Question: How do you configure PostgreSQL replication with a load balancer?

Answer

Setting up PostgreSQL replication involves configuring one or more standby servers that are copies of the primary server. The use of a load balancer helps distribute the read queries among multiple replicas to achieve high availability and better read performance. Here's an overview of how to accomplish this:

Step 1: Configure Postgres Replication

1. Primary Server Configuration: On your primary PostgreSQL server, modify postgresql.conf:

   listen_addresses = '*'
   wal_level = replica
   max_wal_senders = 5
   wal_keep_segments = 32

2. Primary Server pg_hba.conf: Add the following line to allow the replica to connect:

   host replication all <replica_ip>/32 md5

3. Create a Replication User: On your primary server, create a user for replication:

   CREATE ROLE replicator LOGIN REPLICATION ENCRYPTED PASSWORD 'password';

4. Backup the Primary Database: Use pg_basebackup to clone the primary database on your replica server:

   pg_basebackup -h <primary_ip> -D /var/lib/postgresql/12/main -U replicator -P -v --wal-method=stream

5. Configure the Standby Server: On your replica, adjust postgresql.conf as needed, and create standby.signal file to indicate it's a replica.

6. Start the Replica: Start PostgreSQL on the replica server. It should connect to the primary and start replicating.

Step 2: Set Up a Load Balancer

For distributing read queries, you can use PgBouncer or HAProxy as a load balancer in front of your PostgreSQL servers.

Using HAProxy:

1. Install HAProxy: Install HAProxy on your load balancer machine.

2. Configure HAProxy: Edit /etc/haproxy/haproxy.cfg to direct traffic to your primary and replica databases. Sample configuration directs write-operations (SELECT pg_is_in_recovery()) to the primary and read-operations elsewhere:

   frontend psql_front
    bind *:5432
    default_backend psql_back
   
   backend psql_back
    redirect prefix http://<primary_ip>:5432 if { hdr(host) -i write.example.com }
    server primary <primary_ip>:5432 check
    server replica <replica_ip>:5432 check backup

3. Restart HAProxy: After adjusting the configuration, restart HAProxy for the changes to take effect.

Summary

This setup allows you to leverage PostgreSQL replication with a load balancer to distribute reads across multiple replicas while directing writes to the primary server. This enhances database performance and availability. Make sure to monitor your setup and make adjustments according to the workload and requirements.