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?
Other Common PostgreSQL Questions (and Answers)
- How do you manage Postgres replication lag?
- How can I limit the number of rows updated in a PostgreSQL query?
- How does sharding work in PostgreSQL?
- How do you limit the number of rows deleted in PostgreSQL?
- How do you use the PARTITION OVER clause in PostgreSQL?
- What are PostgreSQL replication slots and how do they work?
- How can you partition an existing table in PostgreSQL?
- How do you partition a table by multiple columns in PostgreSQL?
- How do you check the replication status in PostgreSQL?
- What are the scaling limits of PostgreSQL?
- How do you scale Azure PostgreSQL?
- How do you use the limit clause in PostgreSQL to get the top N rows of a query result?
Free System Design on AWS E-Book
Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.
Switch & save up to 80%
Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement. Instantly experience up to a 25X boost in performance and 80% reduction in cost