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 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.

share|improve this question
add comment

3 Answers

up vote 10 down vote accepted

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.

share|improve this answer
    
Better answer as this mentions use of UTF8 which will help down the line. –  François Beausoleil Aug 24 '12 at 21:22
    
@swasheck : Where is this db.sql stored? –  Ashwin Aug 25 '12 at 2:19
    
@Ashwin wherever you tell it to be stored. in my example it'd be the location where the command was run. –  swasheck Aug 25 '12 at 18:32
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.