Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

migrated from stackoverflow.com Sep 24 '12 at 3:39

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
What about user/group privilages? at my home I have eg user1 but at work its user2. Does it matter? –  user528025 Sep 23 '12 at 23:34
1  
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 Sep 23 '12 at 23:35
1  
Use pg_dumpall for a dump of the database roles. –  Frank Heikens Sep 24 '12 at 5:31

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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