I have a number of independent postgres servers in my (recently inherited) environment and I am planning on backing them up via WAL shipping.

I'd like to make one backup server that contains a copy of all of the databases from each of the production servers. Once I have that, can I ship WAL logs from all of the production servers to the single backup server?

If I can get that working, I was then thinking of taking ZFS snapshots each hour.




Windmills do not work that way!

You cannot ship logs from many primary servers to one standby server instance for replication, the Write-Ahead Log is Per-Cluster, and you can't merge them together.
If you somehow convinced a single Postgres standby server to accept the log files from multiple primary servers the results would be catastrophic -- your replicated environment would be an unusable mash-up of the writes from all the remote Primary servers.

The closest equivalent to what you're asking would be to set up an individual Postgres standby instance on the backup host for each remote DB cluster you want to back up, and replicate each primary server to its own standby instance per the instructions in the Postgres manual.

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.