PostgreSQL 11 Streaming Replication/Hot Standby


elephant
Photo by shy sol on Pexels.com

To create PostgreSQL replication first we must install PostgreSQL on  Primary and  Standby server.

On Primary and Standby:

[root@localhost ]# yum -y install epel-release
[root@localhost ]# yum -y install bash-completion
[root@localhost ]# yum -y install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
[root@localhost ]# yum install postgresql11-server

On Primary:

[root@localhost ]# /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK

[root@localhost ]# systemctl enable postgresql-11.service
[root@localhost ]# systemctl start postgresql-11.service

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

[root@localhost ]# vi /var/lib/pgsql/11/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 bash script which will copy WAL files from primary to standby:

[root@localhost ]# vi /usr/pgsql-11/bin/syncwal.sh
#!/bin/bash
scp $1 192.168.2.129:/var/lib/pgsql/11/walarchive/$2
if [ $? != 0 ]
then
echo "Archiver error:"
exit 1
fi
exit 0
[root@localhost ~]# chown postgres: /usr/pgsql-11/bin/syncwal.sh
[root@localhost ~]# chmod 700 /usr/pgsql-11/bin/syncwal.sh

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

[root@localhost bin]# su - postgres 
-bash-4.2$
psql postgres=# CREATE ROLE replicauser WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'test';
CREATE ROLE 
postgres=# ALTER ROLE postgres NOREPLICATION;
ALTER ROLE

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

[root@localhost bin]# vi /var/lib/pgsql/11/data/pg_hba.conf
host     replication         replicauser          192.168.2.129/32        md5

after change restart the PostgreSQL:

[root@localhost ~]# systemctl restart postgresql-11.service
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# systemctl disable firewalld.service

Disable or set to permissive mode SELinux

On standby:

[root@localhost ~]# mkdir -m 700 /var/lib/pgsql/11/walarchive
[root@localhost 11]# chown postgres: /var/lib/pgsql/11/walarchive

On each host generate ssh key  from Postgres user and add this key to other hosts authorized_keys file:

-bash-4.2$ ssh-keygen -t rsa
-bash-4.2$ touch ~/.ssh/authorized_keys
-bash-4.2$ chmod 600 ~/.ssh/authorized_keys

Import base backup of the primary to the standby database

-bash-4.2$ /usr/pgsql-11/bin/pg_basebackup -D /var/lib/pgsql/11/data/ -c fast -X fetch -P -Fp -R -h 192.168.2.113 -p 5432 -U replicauser
Password: ****
40952/40952 kB (100%), 1/1 tablespace

Change standby config file like below:

-bash-4.2$ vi /var/lib/pgsql/11/data/postgresql.conf
hot_standby = on
hot_standby_feedback=on

Then edit recovery.conf file and add lines below:

-bash-4.2$ vi /var/lib/pgsql/11/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=replicauser password=test host=192.168.2.113 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='/usr/pgsql-11/bin/pg_archivecleanup /var/lib/pgsql/11/walarchive %r'
trigger_file = '/var/lib/pgsql/11/data/finish.replication'
recovery_target_timeline = 'latest'

Start database and enable autostart:

[root@localhost 11]# systemctl start postgresql-11.service
[root@localhost 11]# systemctl enable postgresql-11.service

Now create new database and tables to test the standby database and you will see that they immediately applied on standby.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s