I have created a (spatial) database at my home which is several gigabytes and I want to move the whole database as it is from one computer my works computer.
Nothing fansy, I will create a new postgresql/postgis db instance at my work computer. The db users may or maynot have the same name in both computers.
Is it possible? What should i be carefull about? How can i do it pain free?
I'm using postgresql v9.1.5 with postgis extension. Ubuntu12.04 (kernel 3.2.0-31)
-
See this similar thread on StackOverflow, Copying PostgreSQL database to another server.Basil Bourque– Basil Bourque2015年06月21日 00:41:19 +00:00Commented Jun 21, 2015 at 0:41
2 Answers 2
You can run a dump of the database:
pg_dump yourdatabase | gzip -9 > outfile.sql.gz
and then import back into PostgreSQL on your work computer. It will take quite some time and the resulting file, even if compressed, might be in the gigabyte range, but should be small enough to be contained on a USB key.
On your work computer, create the new database and load the dump:
zcat outfile.sql.gz | psql yournewdatabase
If you have a fast connection (or can take the time), you can even transfer the database through the Internet via an encrypted SSH connection, or tools such as rsync.
-
1What about user/group privilages? at my home I have eg user1 but at work its user2. Does it matter?user528025– user5280252012年09月23日 23:34:34 +00:00Commented Sep 23, 2012 at 23:34
-
2It shouldn't. Of course you have to set the correct user in your application, though. Since it's your home PC, try renaming user1 to user2 and verifying it all works, before dumping. Then the transfer should be trouble-free.lserni– lserni2012年09月23日 23:35:58 +00:00Commented Sep 23, 2012 at 23:35
-
5Use pg_dumpall for a dump of the database roles.Frank Heikens– Frank Heikens2012年09月24日 05:31:20 +00:00Commented Sep 24, 2012 at 5:31
-
1to be able to dump the database, you need to install the same postgresql version as on the old server, see wiki.postgresql.org/wiki/Aptrubo77– rubo772019年06月16日 05:41:57 +00:00Commented Jun 16, 2019 at 5:41
pg_dumpall is the safest way to do this. I would recommend taking a dumpall before the migration anyway. the big problem with it though is that it can take a while to restore if it is big.
You can also copy the data directory, preferably after stopping your server (you can use pg_start_backup() in place of stopping but see all docs on point of time recovery before you do go that route. That route would allow a near-zero-downtime move however. Also note you can only do this if the OS and CPU architecture is the same on both systems. You can't move from Windows to Linux or from i686 to x86-64 in this way. Check OS kernel versions too in order to make sure systems are compatible.