copy entire PostgreSQL cluster to another (identical) server

Question

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.

Answer 1

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.

Answer 2

Your option 1, pg_dump | psql does have the limitations you describe.

Your option 2 doesn't make much sense. You need to start out with a clone in order to set up streaming, and by the time you have the clone then you don't need the streaming, as you already have the clone. Also, you don't have to "reconfigure both to disable replication again". When you promote the standby, it is no longer a standby, no more reconfiguration is needed. If you used a named slot on the master, you just need to drop it. If you used a temp slot, you don't even need to do that. You could drop "wal_level" down "minimal" if you want to create extra work for yourself, you could set "max_wal_senders" down to 0 if you want to create extra work for yourself. So just don't do those things. Leave the master configured in a way that will allow future streaming replication. (On v10, being ready for streaming is already the default configuration anyway, other than the setting in pg_hba.conf to allow a remote streaming connection)

Your concerns in option 3 also don't make any sense. Why would rsync'ing the data over be any more susceptible to corruption than anything else you can do with a terabyte of data, like ordinary backups, or even just running a database? Rsync does not go out of its way to corrupt your data. If you need more things than just the data directory depend on how configured the system in the first place. Do you have tablespaces? Are the config files in the data directory, or someplace else. If you do those things, then yes, you have to handle them. That is what you signed up for when you decided to depart from the simplest configuration.

On option 4, the output of "pg_basebackup" does not get piped to "pg_receivewal". They are not designed to work together in this way. "pg_basebackup" can already create a free standing directory, which is what you want, so you can just use it. I think you want to execute, on the host where you want the new server to be, pg_basebackup -D newdir -F plain -h prod_host.example.com

How are you backing up your database currently? The best way to make a clone is probably to tap into your current backup strategy. How would you restore from your backup if you needed to? Minor alterations on that should get you your clone (as well as testing if your current backup strategy works)