Setup hot standby PostgreSQL

This is a guild for setup PostgreSQL replication hot standby

Install PostgreSQL on two servers as primary and replication servers. Can refer to this article for installing PostgreSQL.

Configuration on the primary server. The combination of Hot Standby and Standby Replication would make the latest data inserted into the primary visible in the standby almost immediately. Now let's change postgresql.conf on Primary to create hot standby with streaming replication:

#edit postgresql.conf
nvim /local/export/scratch/dbuser/pg_data/postgresql.conf
wal_level = replica
max_wal_senders=10
wal_keep_segments=256
archive_mode=on
archive_command=’/usr/pgsql-11/bin/syncwal.sh %p %f'
listen_addresses = '*'

Create a bash script that will copy WAL files from primary to standby:

cat /local/export/scratch/dbuser/pg_bin/syncwal.sh
#!/bin/bash
scp $1 192.168.1.111:/local/export/scratch/dbuser2/pg_standby/walarchive/$2
if [ $? != 0 ]
then
echo "Archiver error:"
exit 1
fi
exit 0
# 192.168.1.111 is standby IP
# will using this user to operate posrgresql, no need change ownership 

Now we will create a special user for replication and revoke REPLICATION grant from “Postgres” superuser to secure our replication.

psql -d postgres
CREATE ROLE repuser WITH REPLICATION LOGIN ENCRYPTED PASSWORD '123456';
ALTER ROLE postgres NOREPLICATION;

Then edit pg_hba.conf file on the primary server and add a line below to give access to WAL receiver from the standby :

nvim /local/export/scratch/dbuser/pg_data/pg_hba.conf
host     replication         repuser          192.168.1.111/32        md5

after change restart the PostgreSQL:

pg_ctl -D /local/export/scratch/dbuser/pg_data/pg_hba restart

On the standby server:

Stop database:

pg_ctl -D /local/export/scratch/dbuser2/env/data stop

Create the folder to receive WAL data file from the primary server:

mkdir /local/export/scratch/dbuser2/pg_standby/walarchive

Import base backup of the primary to the standby database

pg_basebackup -D /local/export/scratch/dbuser2/pg_data/ -c fast -X fetch -P -Fp -R -h 192.168.2.110 -p 5432 -U repuser

Change standby config file like below:

nvim /local/export/scratch/dbuser2/env/data/
hot_standby = on
hot_standby_feedback=on

Replace with the base backup folder

cd /local/export/scratch/dbuser2/env/data/
cp postgresql.conf /local/export/scratch/dbuser2/pg_data/
cp pg_hba.conf /local/export/scratch/dbuser2/env/data/
cp postmaster.opts /local/export/scratch/dbuser2/env/data/

Replace base backup data with the initial data:

rm -rf /local/export/scratch/dbuser2/env/data/
cd /local/export/scratch/dbuser2/pg_data/
mv * /local/export/scratch/dbuser2/env/data/

Then edit recovery.conf file and add lines below:

nvim /local/export/scratch/dbuser2/env/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=repuser password=123456 host=192.168.2.110 port=5432 scram_channel_binding=''tls-unique'' sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
restore_command = 'cp /var/lib/pgsql/11/walarchive/%f %p'
archive_cleanup_command='/local/export/scratch/dbuser2/env/bin/pg_archivecleanup /local/export/scratch/dbuser2/pg_standby/walarchive %r'
trigger_file = '/local/export/scratch/dbuser2/env/data/finish.replication'
recovery_target_timeline = 'latest'

Start the database

pg_ctl -D /local/export/scratch/dbuser2/env/data start

Ref: https://valehagayev.wordpress.com/2018/08/15/postgresql-11-streaming-replication-hot-standby/