Question: How do you configure PostgreSQL replication with read-only replicas?

Answer

PostgreSQL offers robust replication capabilities that allow data to be mirrored from a primary (master) server to one or more secondary (slave) servers. This setup enhances both data protection and query performance by offloading read operations to the replicas. Here, we'll discuss how to set up basic streaming replication with read-only replicas.

Step 1: Configure the Primary Server

Edit the postgresql.conf file on your primary server:

# Enable WAL (Write-Ahead Logging) archiving wal_level = replica # Set the maximum number of WAL senders max_wal_senders = 3 # Enable hot standby on replicas for read-only queries hot_standby = on

Additionally, modify the pg_hba.conf file to allow the replica to connect:

# TYPE DATABASE USER ADDRESS METHOD host replication all 192.168.1.0/24 md5

Replace 192.168.1.0/24 with the correct network or IP address of your replica.

Step 2: Initialize the Replica

First, stop your replica server if it's running. Then, remove any existing data in the PostgreSQL data directory (typically /var/lib/postgresql/XX/main/, where XX is the version).

Use the pg_basebackup tool to make a base backup from the primary:

pg_basebackup -h primary_host -D /var/lib/postgresql/XX/main/ -U replicator -v -P --wal-method=stream

After creating the base backup, create a recovery.conf file inside the data directory of the replica:

standby_mode = 'on' primary_conninfo = 'host=primary_host port=5432 user=replicator password=secret sslmode=prefer' trigger_file = '/tmp/postgres_replica_trigger'

Step 3: Start the Replica Server

Now, start your PostgreSQL service on the replica server. The replica will connect to the primary and start streaming changes. By default, the replica is in read-only mode and will accept only SELECT queries.

Additional Configuration

For load balancing or failover purposes, consider setting up multiple replicas, connection pooling, or using tools like PgBouncer. Additionally, for automatic failover, solutions like Patroni or Repmgr can be integrated into your PostgreSQL architecture.

Remember, for production environments, always test your replication setup thoroughly before going live and regularly back up your databases.

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.