Question: How do you set up a PostgreSQL cluster?

Answer

Setting up a PostgreSQL cluster involves configuring multiple PostgreSQL servers to work together, typically for purposes of increased availability, load balancing, or fault tolerance. Here's an overview of the steps involved in setting up a basic PostgreSQL cluster using streaming replication.

Prerequisites

  • At least two PostgreSQL servers (one primary and one or more replicas).
  • Network connectivity between the servers.
  • Sufficient permissions to modify PostgreSQL configuration and control the server processes.

Step 1: Configure the Primary Server

First, configure the primary PostgreSQL server to allow it to send data to the replica(s). Modify the postgresql.conf file:

# Enable WAL (Write-Ahead Logging) archiving wal_level = replica # Set the maximum number of concurrent connections from the replicas max_wal_senders = 3 # Set the amount of information retained in the WAL logs on disk wal_keep_segments = 64

Also, edit the pg_hba.conf file to allow replication connections from the replicas' IP addresses:

# TYPE DATABASE USER ADDRESS METHOD host replication all 192.168.1.50/32 md5

Restart the PostgreSQL service after making these changes.

Step 2: Prepare the Replica Server

On the replica server, you need to start with a base backup taken from the primary. First, stop the PostgreSQL service on the replica if it is running. Then run:

pg_basebackup -h primary_ip -D /var/lib/postgresql/12/main -U replicator -vP -W

This command will ask for the password of the replicator user, who must have replication privileges on the primary server. It copies the data directory from the primary to the replica.

After taking the backup, configure the replica by modifying its postgresql.conf:

hot_standby = on

And create a recovery.conf in the data directory with the following content:

standby_mode = 'on' primary_conninfo = 'host=primary_ip port=5432 user=replicator password=yourpassword' trigger_file = '/tmp/pgsql.trigger.5432'

Step 3: Start the Replica Server

Start the PostgreSQL service on the replica server. It will begin in recovery mode and continuously fetch updates from the primary.

Monitoring and Maintenance

Regularly monitor the lag between the primary and the replicas using tools such as pg_stat_replication. Also, ensure that backups are taken regularly and test failover procedures periodically.

This setup provides a robust starting point for a PostgreSQL high availability environment but can be extended and customized based on specific needs and advanced configurations like using connection pools, load balancers, or additional tools like repmgr.

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.