POSTGRES

PostgreSQL 15 master-slave configuration using streaming replication step by step Step 1: Install PostgreSQL on Both Servers Sheikh Wasiu Al Hasib Sheikh Wasiu Al Hasib

· Follow

2 min read · Aug 30, 2024 2

Ensure PostgreSQL 15 is installed on both the primary and standby servers. You can use the package manager for your system (like `yum` for Red Hat-based systems):

sudo yum install postgresql15-server Step 2: Configure the Primary Server 1. Edit the PostgreSQL Configuration File (`postgresql.conf`): — Set the `listen_addresses` to ‘*’ or the specific IP address to accept connections. — Configure the `wal_level` to ‘replica’ to provide the necessary WAL (Write-Ahead Logging) data for the replicas. — Set `max_wal_senders` to the number of standby servers; for one standby, you might set it to 2 or 3 to allow some headroom. — Set `max_replication_slots` if you plan to use replication slots (recommended). — Adjust `archive_mode` to ‘on’ and set `archive_command` to a method that archives your WAL files to a secure location.

Example: listen_addresses = '*' wal_level = replica max_wal_senders = 3 max_replication_slots = 2 archive_mode = on archive_command = 'cp %p /path_to_archive/%f' 2. Modify `pg_hba.conf`: — Add rules to allow the standby server to connect:

host replication all 0.0.0.0/0 scram-sha-256 Step 3: Initialize the Standby Server Create a Base Backup: — Stop the standby server if it’s running. — Remove any existing data in the standby’s data directory. — Use the `pg_basebackup` utility to take a base backup from the primary. This utility automatically creates a valid configuration for replication in the `postgresql.auto.conf`: pg_basebackup -h primary_db_ip -U replicator -p primary_db_port -D /data/pgsql/14/test -v -P --wal-method=stream --write-recovery-conf 2. Create and Modify the Required Signal and Configuration Files: — In the standby’s data directory, create an empty file named `standby.signal` to indicate that this server should start in standby mode. — Modify `postgresql.auto.conf` or use the `ALTER SYSTEM` command in SQL to set replication-related parameters:

primary_conninfo = 'host=primary_db_ip port=primary_db_port user=replicator password=your_password' primary_slot_name = 'your_slot_name' # Optional, if using replication slots restore_command = 'cp /path_to_archive/%f %p' # Optional, for WAL archiving recovery_target_timeline = 'latest' # Optional, for specific recovery targets Step 4: Start the Standby Server

- Start the PostgreSQL service on the standby server:

sudo systemctl start postgresql-15 Step 5: Verify Replication

Check the replication status by connecting to the primary server and querying: SELECT * FROM pg_stat_replication; - Ensure that the standby is listed and that the `state` is ‘streaming’.

This basic setup should get you started with streaming replication in PostgreSQL 15. Remember to secure your database connections using appropriate authentication methods and consider implementing SSL for data transmission security. In the above configuraiton you need to replace primary_db_ip and primary_db_port with your real ip and port.