2

Is it possible to directly transfer a PostgreSQL database between two PostgreSQL servers?

I've currently been setting up a "development" version of my production database by using pg_dump to serialize the entire database to a file, download the file, and then using pg_restore to load the database onto the development server. However, as the database has been growing, this process is now taking hours. Is there anyway to simply "connect" the two servers and transfer the database directly, instead of this lengthy dump/download/restore process?

4 Answers 4

3

Note that I've only worked with MySQL, but PostegreSQL shouldn't be to different.

If pg_dump is anything my mysqldump, then can't you just pipe the output into pg_restore? That's the most direct and fastest way I can think of. Anything else is just more overhead

1

One thing you might consider is having two development databases, so you have one you're using while the other one is going through the pg_dump|pg_restore cycle. Although I've never had a database that took more than a few minutes, and so I've never investigated to see if the --format tar might be faster than the default format.

1

In PostgreSQL there is basically two type of backup plus replication for achieve your goal. Now you are using logical backup, try instead physical backup :

Physical cold backup means coping the files (if necessary also the rdbms binaries) to a place where you can get it back in case of problems.

Advantages: Physical cold backup is generally much faster than logical hot backup.

Disadvantages: Database cluster has to be shut down during backup. You can not use cold backup for upgrading a PostgreSQL database cluster.

Then for doing a cold physical backup you need to :

  • Stop Database Server
  • physically copy the database file to development server (you can also use rsynch)
  • Start the server again

If you can't stop the cluster then is better setting up an asynch replication using new 9.0 version of postgreSQL.

0

If the two servers have the same architecture, then you can just copy the data directory. Normally, you'd have to shut down the server to do that or set up WAL archiving. But for creating a testing database, it might be sufficient to do it anyway and clean up your copy with pg_resetxlog afterwards.

You can use rsync to reduce the amount of data to be copied in subsequent runs.

If the two servers have a different architecture, then pg_dump is the only route.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .