12

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)

Improve this question
1

2 Answers 2

Reset to default
14

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.

Improve this answer
4
  • 1
    What about user/group privilages? at my home I have eg user1 but at work its user2. Does it matter?
    – user528025
    Commented Sep 23, 2012 at 23:34
  • 2
    It 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
    Commented Sep 23, 2012 at 23:35
  • 5
    Use pg_dumpall for a dump of the database roles.
    – Frank Heikens
    Commented Sep 24, 2012 at 5:31
  • 1
    to be able to dump the database, you need to install the same postgresql version as on the old server, see wiki.postgresql.org/wiki/Apt
    – rubo77
    Commented Jun 16, 2019 at 5:41
5

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.