I need to copy all data from one Postgresql 9.2.4 server to another (both on similar hardware, Ubuntu 12.04). Is it safe to shut down the source Postgres server, rsync the data directory to the new server and start up both servers? Do I need to copy the pg_xlog directory as well? Or is it ok to leave that out since the source server will be shut down cleanly (service postgresql stop)?

I have approx 275G of data so don't want to backup and restore as that will take ages.

  • Do you just want to move your DB or do you need replication? – dezso Sep 6 '13 at 10:26
  • I am moving the db to a new server with SSD drives. However the new server does have a hot standby slave using synchronous replication. My plan is to copy the data to both master and standby servers before starting them up. – David Tinker Sep 6 '13 at 10:35
  • 2
    Another alternative to rsync'ing the cluster to the master and slave machines is to perform a pg_dump -Fc and pipe that over the network directly to a pg_restore, thus eliminating the need to save to disk. This is probably a less desirable way than using rsync though, as the latter at least allows you to restart if there is a network etc failure part way through. – bma Sep 6 '13 at 13:34

No, the pg_xlog directory can't be omitted even if the server has been properly shut down before copying the data directory.

If you try, you should see that the target server will fail to start, complaining that it can't find a checkpoint record.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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