Setting Up Replication in PostgreSQL

Replication in PostgreSQL allows you to maintain one or more copies of a database to ensure high availability and data redundancy. It is essential for disaster recovery and load balancing.

• Master-Slave Architecture: One primary server (master) replicates data to one or more secondary servers (slaves).
• Streaming Replication: Allows real-time data replication using a replication stream.
• Logical Replication: Replicates data based on logical changes instead of physical files.

1. Streaming Replication
2. Logical Replication
3. Physical Replication

Follow these steps to set up streaming replication in PostgreSQL:

1. Configure the Master Server:

Modify the postgresql.conf file:

wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64

2. Set Up Authentication:

Update the pg_hba.conf file to allow the replica to connect:

host    replication     all             /32           md5

3. Restart the Master Server:

sudo systemctl restart postgresql

4. Create a Base Backup:

On the Master, run:

pg_basebackup -h localhost -D /var/lib/postgresql/12/main -U  -P --wal-method=stream

5. Configure the Replica Server:

Modify the postgresql.conf on the replica:

hot_standby = on

6. Create a recovery.conf file on the Replica:

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

7. Start the Replica Server:

sudo systemctl start postgresql

• Regularly monitor replication lag.
• Test failover procedures periodically.
• Use appropriate hardware resources for both master and replicas.
• Backup your configuration files regularly.