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 [root@localhost ]# yum install postgresql11-contrib
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.
