Thread: Moving postgresql data to another computer
Good Day All, I am a newbie to PostgreSQL and I recently installed it on my desktop (Windows XP) I created a database fine and worked a bunch of queries. I am traveling now and I needed access to my database on my laptop (Windows Vista). So before I left, I installed PosgreSQL on the laptop and then copied the data in the PostgreSQL data directory over to my laptop. I put the data directory from my desktop in a different place on my laptop and now I would like to access the data but I can not seem to figure out how to do this. I naively tried to just replace all the files in the data directory on my laptop with the files from the data directory of the desktop but when I do so, I get the following error: "could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432? " I figure I have at least two options Option 1) Point PostgreSQL on laptop to the directory that desktop data lives in Option 2) Replace the data directory on the laptop with the data directory that came from the desktop. While I am travelling now (about a month) I eventually will want to move my database BACK to the desktop so a solution that makes that transition simple would be ideal. I've tried searching the documentation, archives, and the FAQs but to no avail. I either found it and did not understand how to do it or I could not find it properly. Could someone point me in the correct direction ? Thanks in advance ! -Don
On Tue, May 11, 2010 at 1:16 PM, Donald Catanzaro, PhD <dgcatanzaro@gmail.com> wrote: > I am a newbie to PostgreSQL and I recently installed it on my desktop > (Windows XP) I created a database fine and worked a bunch of queries. I am > traveling now and I needed access to my database on my laptop (Windows > Vista). So before I left, I installed PosgreSQL on the laptop and then > copied the data in the PostgreSQL data directory over to my laptop. > I put the data directory from my desktop in a different place on my laptop > and now I would like to access the data but I can not seem to figure out how > to do this. The easiest (and therefore safest) way to tranfer the databases from one server cluster to another is to dump the entire contents of the database to a text file and then load the text file onto the second server instance. This methods works as long as the second server instance postgresql version >= to the initial instance. Its best if both versions are the same if you want to transfer the data in both directions. http://www.postgresql.org/docs/8.4/interactive/backup-dump.html http://www.postgresql.org/docs/8.4/interactive/app-pg-dumpall.html http://www.postgresql.org/docs/8.4/interactive/app-pgrestore.html > I naively tried to just replace all the files in the data directory on my > laptop with the files from the data directory of the desktop but when I do > so, I get the following error: "could not connect to server: Connection > refused (0x0000274D/10061) Is the server running on host "127.0.0.1" and > accepting TCP/IP connections on port 5432? " Transferring the actual binary files is more risky since much more care is needed to insure a consistent and functional copy. Notice the method of creating a consistent copy: http://www.postgresql.org/docs/8.4/interactive/backup-file.html If you have a consistent copy, you might need to point PostgreSQL to work from the copied data directory. http://www.postgresql.org/docs/8.4/interactive/server-start.html -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Hi All, Well, needless to say I naively copied the data over. First off I did not stop the database server (thus violating http://www.postgresql.org/docs/8.4/interactive/backup-file.html) and I am not near my desktop. Does that mean my copy is in essence useless and I have to start over ? -Don Richard Broersma wrote: > On Tue, May 11, 2010 at 1:16 PM, Donald Catanzaro, PhD > <dgcatanzaro@gmail.com> wrote: > > >> I am a newbie to PostgreSQL and I recently installed it on my desktop >> (Windows XP) I created a database fine and worked a bunch of queries. I am >> traveling now and I needed access to my database on my laptop (Windows >> Vista). So before I left, I installed PosgreSQL on the laptop and then >> copied the data in the PostgreSQL data directory over to my laptop. >> I put the data directory from my desktop in a different place on my laptop >> and now I would like to access the data but I can not seem to figure out how >> to do this. >> > > The easiest (and therefore safest) way to tranfer the databases from > one server cluster to another is to dump the entire contents of the > database to a text file and then load the text file onto the second > server instance. > > This methods works as long as the second server instance postgresql > version >= to the initial instance. Its best if both versions are the > same if you want to transfer the data in both directions. > > http://www.postgresql.org/docs/8.4/interactive/backup-dump.html > http://www.postgresql.org/docs/8.4/interactive/app-pg-dumpall.html > http://www.postgresql.org/docs/8.4/interactive/app-pgrestore.html > > >> I naively tried to just replace all the files in the data directory on my >> laptop with the files from the data directory of the desktop but when I do >> so, I get the following error: "could not connect to server: Connection >> refused (0x0000274D/10061) Is the server running on host "127.0.0.1" and >> accepting TCP/IP connections on port 5432? " >> > > Transferring the actual binary files is more risky since much more > care is needed to insure a consistent and functional copy. > Notice the method of creating a consistent copy: > http://www.postgresql.org/docs/8.4/interactive/backup-file.html > > > If you have a consistent copy, you might need to point PostgreSQL to > work from the copied data directory. > http://www.postgresql.org/docs/8.4/interactive/server-start.html > > > -- -Don Don Catanzaro, PhD Landscape Ecologist dgcatanzaro@gmail.com 16144 Sigmond Lane Lowell, AR 72745 479-751-3616
On Tue, May 11, 2010 at 1:52 PM, Donald Catanzaro, PhD <dgcatanzaro@gmail.com> wrote: > Well, needless to say I naively copied the data over. > First off I did not stop the database server (thus violating > http://www.postgresql.org/docs/8.4/interactive/backup-file.html) and I am > not near my desktop. Does that mean my copy is in essence useless and I > have to start over ? I did this once myself and because of a hard drive crash, there was no other alternative for me. However, I was helped to find a solution. http://archives.postgresql.org/pgsql-general/2006-10/msg00387.php http://www.postgresql.org/docs/8.4/interactive/app-pgresetxlog.html -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Tue, May 11, 2010 at 2:16 PM, Donald Catanzaro, PhD <dgcatanzaro@gmail.com> wrote: > Good Day All, > > I am a newbie to PostgreSQL and I recently installed it on my desktop > (Windows XP) I created a database fine and worked a bunch of queries. I am > traveling now and I needed access to my database on my laptop (Windows > Vista). So before I left, I installed PosgreSQL on the laptop and then > copied the data in the PostgreSQL data directory over to my laptop. > I put the data directory from my desktop in a different place on my laptop > and now I would like to access the data but I can not seem to figure out how > to do this. > > I naively tried to just replace all the files in the data directory on my > laptop with the files from the data directory of the desktop It should work if the source and destination are built the same on the same architecture. Better to move the old one out of the way though. > but when I do > so, I get the following error: "could not connect to server: Connection > refused (0x0000274D/10061) Is the server running on host "127.0.0.1" and > accepting TCP/IP connections on port 5432? " This could easily be a configuration issue. Is there a postgres process running? If so is it set to listen on 127.0.0.1 or localhost? > I figure I have at least two options > Option 1) Point PostgreSQL on laptop to the directory that desktop data > lives in Worth a try. > Option 2) Replace the data directory on the laptop with the data directory > that came from the desktop. Either one should work. But I'm a linux guy. > While I am travelling now (about a month) I eventually will want to move my > database BACK to the desktop so a solution that makes that transition simple > would be ideal. Copy it back over to a clean dir should do it. OR pg_dump it and reload it into a new db on the other machine. Generally speaking dump / restore offers fewer chances to shoot yourself in the foot.