What method of ensuring PostgreSQL fault tolerance would you choose?

The choice of PostgreSQL fault tolerance method depends on specific requirements for availability, budget, and data loss tolerance (RPO/RTO). Main approaches:

1. Replication:

   • Advantages: Relatively easy to set up, good read performance with replicas.
   • Disadvantages: Requires manual or automated failover, master node is a single point of failure during writes.
   • Types: Streaming Replication (synchronous/asynchronous), Logical Replication.

2. Clustering:

   • Advantages: Automatic failover, high availability.
   • Disadvantages: Complex setup and management, requires additional software (Patroni, Repmgr, Corosync/Pacemaker).
   • Examples: Patroni + DCS (etcd, ZooKeeper, Consul), Repmgr.

3. Distributed Databases:

   • Advantages: High availability, horizontal scalability.
   • Disadvantages: Architectural complexity, requires changes in application development approach (sharding, transactions).
   • Examples: CitusData (PostgreSQL extension).

Recommended approach:

For most standard cases, I would choose Streaming Replication combined with a clustering manager like Patroni.

• Streaming Replication (asynchronous or synchronous depending on RPO): Ensures up-to-date copies of data on replicas.
• Patroni: Automates failover and switchover process, detects master node failures, and promotes a replica to master. Uses distributed storage (e.g., etcd) for cluster coordination and state storage.

Architecture example:

• 1 PostgreSQL master node
• N PostgreSQL replicas (at least 1 for fault tolerance)
• etcd cluster (at least 3 nodes for quorum)
• Patroni processes on all PostgreSQL nodes
• Load Balancer (HAProxy, Nginx) to distribute read traffic to replicas and direct writes to the current master.

Advantages of this approach:

• Automatic failover minimizing RTO.
• Maintaining data consistency (depends on replication type).
• Easier scaling by adding replicas.
• Ability to read data from replicas, reducing load on the master.

Also consider:

• Backups: Do not replace fault tolerance but are critical for recovery after logical errors or disasters. Use pg_basebackup or wal-g.
• Monitoring: Important for timely issue detection (Prometheus + Grafana, Zabbix).
• Failover testing: Regularly test the switch-over procedure to ensure its functionality.

The specific configuration (number of replicas, synchronous/asynchronous replication) is determined by business requirements for availability and acceptable data loss.