I wish someone that has resources to invest into making the replication in PostgreSQL seamless, easy to configure and easy to change masters without anything like pgbounce and friends.
Otherwise, PostgreSQL is fantastic.
Otherwise, PostgreSQL is fantastic.
That said, it’s not that hard to set up replication [0]. Properly tuning the various parameters, monitoring, and being able to fix issues is another story.
RDBMS is hard. MySQL is IMO the easiest to maintain up to a certain point, but it can still bite you in surprising ways. Postgres appears to be as easy on the surface, buoyed by a million blog posts about it, but as your dataset grows, so does the maintenance burden. Worse, if you don’t know what you should be doing, it just eventually blows up (txid wraparound from vacuum failures probably being the most common).
[0]: https://www.postgresql.org/docs/current/runtime-config-repli...
Hopefully OrioleDB can upstream all the necessary changes soon. For those who don't know, it's a storage engine for Postgres that uses undo logs instead of vacuuming old records.
But yeah, looking forward to that day too!
[1] https://www.orioledb.com/blog/no-more-vacuum-in-postgresql
And yes, I read the documentation and it is still cumbersome to have an HA setup that is easy to maintain. This is what I mean and hope it is clearer now.
Personally I'd like BDR to be in the main tree, or something else, equivalent to Galera:
It's not perfect [1], but it'll get you a good way for many use cases.
[1] https://aphyr.com/posts/327-jepsen-mariadb-galera-cluster
From what I understood, sometimes the way data is written to disk differently between versions and they're not compatible. I guess due to optimizations or changes in the storage engine?
1. Dump a backup to disk, then restore from dump on new version;
2. Stop the old version, then run `pg_upgrade --link` on the data of the old version which should create a new data directory using hardlinks, then start the new version using the new data directory. This is rather quick; or
3. Use Logical Replication to stand up the new version. This has ... a few caveats.
You can however create your own health/status check service on top of pg_autoctl show state and use HAProxy if required.
I don't think there's something easier to setup and manage than pg_auto_failover, Patroni always appeared very complicated to me.
This video covers pretty much all the practical stuff. I timestamped the parts in a comment.
PgPool II Performance and best practices https://www.youtube.com/watch?v=bMnVS0slgU0
Gosh, haven't heard that in years. I remember a company I used to work for used it on their old system but the new system didn't use it for whatever reason.
I thought patroni only did physical replication (only replicates across the same version of Postgres). But maybe I'm mistaken.