Re:Reddit’s blackout protest is set to continue indefinitely
by paulmd on Thursday June 15, 2023 @10:57PM
(#36350531)
postgres itself doesn't really do more than a leader/follower failover. There is always one source of truth: the leader, and whatever timestamp in the WAL they have written until. This is replicated to the follower. If the leader stops talking, the follower takes over. That's more or less it.
(if you have a third-party commercial PG extension with clustering, like EnterpriseDB or CitusDB, ask them, they'll tell you what they want you doing.)
there is no inherent clustering support in postgres. You can't scatter tables across servers/etc. It's not supported. Always one master server - if you need more storage you can use SAS/fibrechannel/etc of course but there is one postgres instance that's in charge.
All of the postgres replication idioms rely on that model: the WAL is reliable up to the point it's been received/verified. If you've faithfully played out the WAL throughout your existence, everyone's storage instance is at the exact same state and the only thing that matters is who's got the most recent timestamp from the host.
This also includes bringup strategies: if you have an atomically checksummed snapshot of the datasets (all storage, and pg_wal, at the same instant) then you can also spawn new nodes. ZFS can do that. And with an incremental copy-on-write you can replicate the base state, then replicate only the changes (which is much faster) and then fail over quickly. That's popular for other applications that don't have sophisticated ACID stuff.
Anyway short version is, I'd think about a simple blue-green system (or round-robin system) where you can set an environment flag, and blue frontend nodes can simply choose to direct traffic to green backend's pgbouncer or similar when they see that. Then you flip web traffic over. Then you shut down the blue pg instance, and stuff fails over gracefully. The "healthy", "progressing", and "migrated" states, if that makes sense. And there is always one healthy node, even if you need to bring it up from backups. Yes, you have to look whether the other side is still healthy but that is now a k8s/pod problem and not an app one.
If you can live with an instance in the US and one in Europe or similar, a simple blue/green failover is probably easiest.
(if you have a third-party commercial PG extension with clustering, like EnterpriseDB or CitusDB, ask them, they'll tell you what they want you doing.)
there is no inherent clustering support in postgres. You can't scatter tables across servers/etc. It's not supported. Always one master server - if you need more storage you can use SAS/fibrechannel/etc of course but there is one postgres instance that's in charge.
All of the postgres replication idioms rely on that model: the WAL is reliable up to the point it's been received/verified. If you've faithfully played out the WAL throughout your existence, everyone's storage instance is at the exact same state and the only thing that matters is who's got the most recent timestamp from the host.
This also includes bringup strategies: if you have an atomically checksummed snapshot of the datasets (all storage, and pg_wal, at the same instant) then you can also spawn new nodes. ZFS can do that. And with an incremental copy-on-write you can replicate the base state, then replicate only the changes (which is much faster) and then fail over quickly. That's popular for other applications that don't have sophisticated ACID stuff.
Anyway short version is, I'd think about a simple blue-green system (or round-robin system) where you can set an environment flag, and blue frontend nodes can simply choose to direct traffic to green backend's pgbouncer or similar when they see that. Then you flip web traffic over. Then you shut down the blue pg instance, and stuff fails over gracefully. The "healthy", "progressing", and "migrated" states, if that makes sense. And there is always one healthy node, even if you need to bring it up from backups. Yes, you have to look whether the other side is still healthy but that is now a k8s/pod problem and not an app one.
If you can live with an instance in the US and one in Europe or similar, a simple blue/green failover is probably easiest.