Copy PostgreSQL data from one PC to another

Question

I am migrating my server application from the existing system to another system. Unfortunately, the existing system is also the database server. It has data stored. Is it possible to copy this data to the other system? I have a copy of all the table schemas.
I have no idea on how to proceed. I am using PostgreSQL.

Answer 1

You could dump the database using pg_dump and then restore it on the new server using psql. Here's a couple of commands from the above link:

Create the backup:

pg_dump mydb > db.sql

Copy db.sql to the new server (specific command depends on OS)

Go to the new server

createdb mydb -E UTF8 (you don't have to specify UTF8 encoding, but I always do)

Then:

psql -d mydb -f db.sql

As I was answering, JohnP just answered with a fine answer but assumes that pg_hba.conf has been edited to allow remote connections and postgres.conf has been edited to listen on the network.

• François Beausoleil: Better answer as this mentions use of UTF8 which will help down the line.
• Ashwin: @swasheck : Where is this db.sql stored?
• swasheck: @Ashwin wherever you tell it to be stored. in my example it'd be the location where the command was run.

Answer 2

The perhaps easiest way is to do a full dump from the old server and pipe the result straight into the new server. Like this:

pg_dump -h old_server_ip -p 5432 -U username dbname | psql -h localhost -p 5432 -U username dbname

Do this as a superuser. By default the postgres user is a superuser, but you might've created others.

UPDATE: In case you move data between different server versions, use pg_dump from the latest version, most likely pg_dump on the new server.

• sorin: This approach does not create the database if it does note exists.

Answer 3

Last time I had to do a task like this I used the Free ETL tool called 'GeoKettle'

http://www.spatialytics.org/projects/geokettle/

It looks a little scary at first, but all you need to do for a server to server copy is drag a Table input onto your work surface, a Table out onto your work surface, join them together then double click on each and configure them as required.

Once that's done you then save the transform, and it's there to run when ever you need it.

To run it, load your transform in and click the run button. Assuming everything is ok and configured correctly you should get the data transferred relatively quick.

If you don't need to handle spatial data then you can use the non spatial version called 'Pentaho' but since GeoKettle can do everything that Pentaho can and more then GeoKettle is the best of the 2.

It will take you a day of playing to get used to the app, but once your done you'll find everything quite easy, and re-usable.

I use the app now for copying from all manner of different servers to other different servers as well as bulk loading all kinds of data.