Backstory: I am unable to use RDS, as I need to install cartridges in my PostgreSQL instances.
I have been trying to pin down an architecture for PostgreSQL running on EC2 instances for a few days. Most information I could find online use separate software tools like HAProxy or PGPool, or more fully fledged DB clustering products like ClusterControl.
Question: Can't a highly available PostgreSQL DB be set up on AWS without any software load balancers, cluster management tools, etc?
So far I have come up with the following architecture which I think would work. There is some custom coding required in the Lambda layer to update the PostgreSQL replication slots whenever EC2s are terminated / started - there is no escaping this since PostgreSQL does not come with built in failover mechanism.
Notes:
- I'm using PostgreSQL version 10.
- DB connections come in via the network load balancer.
- Replication Slot management via SQL commands: https://www.postgresql.org/docs/10/logical-replication-subscription.html
