Replication in PostgreSQL

Introduction

Explanation: Replication in PostgreSQL involves copying data from one database server (the primary) to another (the replica). This tutorial covers setting up and managing replication in PostgreSQL, focusing on streaming replication and logical replication.

Types of Replication

PostgreSQL supports several replication methods:

• Streaming Replication: Provides near real-time replication of data from the primary to replica servers.
• Logical Replication: Allows replication of specific tables or parts of the database.
• Snapshot Replication: Takes a snapshot of the primary database and transfers it to the replica.

Setting Up Streaming Replication

Step 1: Configure the Primary Server

Edit the postgresql.conf file on the primary server:

wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64
                

Edit the pg_hba.conf file on the primary server to allow replication connections:

host replication replicator 192.168.1.0/24 md5
                

Restart the primary server:

sudo systemctl restart postgresql
                

Step 2: Set Up the Replica Server

Initialize the replica server's data directory:

sudo -u postgres pg_basebackup -h primary_ip -D /var/lib/pgsql/12/data -P -U replicator --wal-method=stream
                

Create a recovery.conf file in the replica's data directory:

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

Start the replica server:

sudo systemctl start postgresql
                

Setting Up Logical Replication

Step 1: Configure the Primary Server

Edit the postgresql.conf file on the primary server:

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4
                

Restart the primary server:

sudo systemctl restart postgresql
                

Create a publication on the primary server:

CREATE PUBLICATION my_pub FOR ALL TABLES;
                

Step 2: Set Up the Subscriber

Create a subscription on the replica server:

CREATE SUBSCRIPTION my_sub CONNECTION 'host=primary_ip dbname=mydb user=replicator password=secret' PUBLICATION my_pub;
                

Check the subscription status:

SELECT * FROM pg_stat_subscription;
                

Monitoring Replication

Monitor replication status using the following queries:

-- On the primary server
SELECT * FROM pg_stat_replication;

-- On the replica server
SELECT * FROM pg_stat_wal_receiver;
                

Troubleshooting Replication Issues

Common issues in replication setup and management include:

• Network connectivity issues.
• Configuration file errors.
• Replication slot overflow.

Check PostgreSQL logs for detailed error messages and refer to the official documentation for troubleshooting steps.