This completes the feature set for JSON document storage so you can now use Postgres instead of NoSQL solutions. However Postgres's replication story is still a little weak compared to other solutions.
What are current best practices for configuring replicated PG with hot failover?
To be clear, what I want is:
- The ability to deploy a cluster of say, 5 replicated PostgreSQL instances
- All write transactions go to a single leader, which replicates them to the other instances
- Reads can go to any instance
- If a leader crashes the cluster will elect a new one without human intervention, and no committed transactions will be lost.
I've always been unclear on how to do this with Postgres.
I've worked mostly with MariaDB Galera Cluster which is pretty effortless to setup, but for PostgreSQL there doesn't seem to be any clear direction on how to do the same things.
I am cheating and getting more or less the same availability properties by using multi-AZ RDS i.e. postgresql with AWS managing the synchronous block replication and standby promotion.
If someone is planning to use PostgreSQL as a NoSQL solution, one can use BDR (Bi-Directional Multi-Master Replication -> https://github.com/2ndQuadrant/bdr) and its global sequences (if needed).
There are plenty of other replication solutions available for Postgres, indeed.
To be clear, what I want is: - The ability to deploy a cluster of say, 5 replicated PostgreSQL instances - All write transactions go to a single leader, which replicates them to the other instances - Reads can go to any instance - If a leader crashes the cluster will elect a new one without human intervention, and no committed transactions will be lost.
I've always been unclear on how to do this with Postgres.