Question: How do you set up PostgreSQL replication on Ubuntu?

Answer

Setting up PostgreSQL replication on an Ubuntu server involves a few key steps. This guide will cover setting up a simple streaming replication which is a common scenario for achieving high availability and read scaling.

Prerequisites

• Two Ubuntu servers (a primary and a standby).
• PostgreSQL installed on both servers.
• Network connectivity between the two servers.

Assuming PostgreSQL is installed and running on both servers, follow these steps:

Step 1: Configure the Primary Server

Edit the postgresql.conf file on the primary server.

sudo nano /etc/postgresql/12/main/postgresql.conf

Make sure to replace 12 with your version of PostgreSQL. Add or modify the following lines:

listen_addresses = '*' # To listen on all interfaces
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64

Step 2: Edit pg_hba.conf on the Primary Server

You need to allow the standby server to connect to the primary server. Edit the pg_hba.conf file:

sudo nano /etc/postgresql/12/main/pg_hba.conf

Add the following line at the end of the file. Replace standby_ip with the IP address of your standby server.

host    replication     all             standby_ip/32            trust

Reload PostgreSQL configuration:

sudo systemctl reload postgresql

Step 3: Create a Replication User

On the primary server, switch to the postgres user and start the PostgreSQL interactive terminal.

sudo -u postgres psql

Create the replication user:

CREATE ROLE replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'your_password';

Replace 'your_password' with a secure password.

Step 4: Prepare the Standby Server

Stop PostgreSQL on the standby server:

sudo systemctl stop postgresql

Remove the existing data directory and clone the database from the primary server:

sudo -u postgres pg_basebackup -h primary_ip -D /var/lib/postgresql/12/main -U replicator -P -v --wal-method=stream

Replace primary_ip with the IP address of your primary server.

Step 5: Configure the Standby Server

Create a recovery file to start the server in standby mode.

sudo nano /var/lib/postgresql/12/main/standby.signal

(For PostgreSQL versions prior to 12, use recovery.conf)

And configure it to connect to the primary server by creating a postgresql.auto.conf file with the following content:

primary_conninfo = 'host=primary_ip port=5432 user=replicator password=your_password'

Replace primary_ip and your_password with appropriate values.

Finally, start PostgreSQL on the standby server:

sudo systemctl start postgresql

The standby server will now start in replication mode, continuously fetching logs from the primary server and applying them.

This setup covers basic streaming replication. For production environments, consider additional configurations like synchronous replication, failover mechanisms, and backup strategies.