Postgresql – Move postgresql database from one server to another

postgresql

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)

Best Answer

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.

Related Question