News for hackers. Stuff that matters.
Re:PostgreSQL Schema Design by megous on Sunday March 29, 2020 @03:52PM (#22722113)
> I don't recommend more than one cluster per host. I just mention it as possible.

I actually use this as a cheap data backup mechanism. I have a primary database cluster on SSD, and two replicas on two separate harddrives. All running as three postgresql clusters on the same machine.

The harddrives run intentionally on different filesystems, so that filesystem bug will not eat all my DB data. (in case someone wants to suggest raid ;))

Re:PostgreSQL Schema Design by manigandham on Sunday March 29, 2020 @09:25PM (#22724392)
This seems complicated and would still fail if something happens to the host hardware itself right? What about projects like wal-e/wal-g that continuously archive to cloud storage?

https://github.com/wal-e/wal-e

https://github.com/wal-g/wal-g

Re:PostgreSQL Schema Design by megous on Monday March 30, 2020 @11:09AM (#22728610)
Looks like an interesting option too.

But it's not really complicated. Making a replica is just a few shell commands and two new systemd service files. It's probably simpler than setting up wal-e, especially if you count in the setup and maintenance costs of those cloud accounts, and the need to keep up with regular payments for the services, and recovery not being as simple as switching to an already configured and uptodate replica is.

Re:PostgreSQL Schema Design by mbrameld on Sunday March 29, 2020 @11:30PM (#22724976)
It would only fail if the host hardware failed in a way that damaged all three discs, which seems unlikely. It's for data backup, not uptime redundancy.
Re:PostgreSQL Schema Design by mjard on Sunday March 29, 2020 @11:52PM (#22725074)
Re:PostgreSQL Schema Design by bzzzt on Monday March 30, 2020 @07:04AM (#22726934)
A power surge caused by a lightning strike would kill your master as well as your backup. Besides that, do you also keep all your WAL logs so you can roll back an accidentally dropped database?
Re:PostgreSQL Schema Design by megous on Monday March 30, 2020 @10:58AM (#22728517)
A power surge here where I am may kill the power supply. It's low probability that it would kill everything.

Anyway, I don't fear the lightnings/storms/power surges where I am as much as I do the inevitable failures of the storage devices, or kernel bugs.

This is just a part of layered protection I have. Offline backups are nice, and I have them too, but they are out of sync all the time by definition. So unless absolutely needed, having a real-time synchronized replica is much more prefered.