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.