PostgreSQL Replication – Copy Entire Cluster to Another Server

backuppostgresqlreplicationwrite-ahead-logging

I'm looking to clone a PostgreSQL 10 cluster from server1 to server2, which is running the same Postgres version on identical hardware. The purpose is load-balancing and HA. Things to keep in mind:

  • The databases are very large (terabytes) and the network is excellent. I want to avoid using intermediate files.
  • Cloning the live databases would be cool, but I can also shut down the cluster if needed.

Options I've considered:

  1. pg_dump | psql of course, but this requires re-creating the indexes and seems really slow and incredibly inefficient for a complete copy between identical systems.
  2. Set up streaming replication with server2 as slave, wait for it to sync with server1, then reconfigure both to disable replication again (I don't need it). Seems like a bunch of pointless config work with room for mistakes.
  3. Shut down the cluster, rsync all the Postgres folders and files. With so much data there's a risk of data corruption, and I need to be sure I get everything (presumably just the data directories are not enough).
  4. Can I pipe pg_basebackup directly to pg_receivewal somehow to make this work? Can't find instructions for my use case.

What's the best way to do this? Seems like a pretty common situation.

Best Answer

The way to go is a plain format pg_basebackup.

Just make sure that --wal-method=stream, and the backup will contain all required WAL files.

Then start the server on the backup directory, and it will perform recovery and come up.

There is no need to mess with pg_receivewal.

Related Question