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.

Was this content helpful?

White Paper

Free System Design on AWS E-Book

Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.

Free System Design on AWS E-Book
Start building today

Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement.