ds-meg
Last Updated: December 12, 2017
·
857
· Ganesh071190

postgres master slave setup

Installation

rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
yum -y install postgresql10 postgresql10-server postgresql10-contrib postgresql10-libs -y
/usr/pgsql-10/bin/postgresql-10-setup initdb
systemctl start postgresql-10.service
systemctl enable postgresql-10.service
su - postgres -c "psql"

Create SSH-Key (postgres)

su - postgres
ssh-keygen -t rsa
touch ~/.ssh/authorizedkeys
chmod 600 ~/.ssh/authorized
keys

Config

Master

vi /var/lib/pgsql/10/data/postgresql.conf
listenaddresses = 'localhost, 10.137.136.226'
max
walsenders = 10

wal
level = 'hotstandby'

max
walsize = 1GB

wal
keepsegments = 10

hot
standby = on

archivemode = on
archive
command = 'cp %p /var/lib/pgsql/wal-archives/%f'

Info

Allow up to 10 standbys and backup processes to connect at a time

Ensure WAL files contain enough information to enable read-only queries and includes information.

File size. default is 16MB

Keep at least 10 files

Enable read-only queries on a standby while recovery and ignored on master.

The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. When archivemode is enabled, completed WAL segments are sent to archive storage by setting archivecommand.

Backup command to store WAL files before postgres disacards old files.

Config

vi /var/lib/pgsql/10/data/pg_hba.conf
host replication repluser 10.137.136.226/32 trust
host replication repluser 10.137.136.228/32 trust
host replication repluser 10.137.32.53/32 trust

Creating directory for archive command backup

su - postgres
mkdir /var/lib/pgsql/wal-archives
mkdir /var/lib/pgsql/backup

Create user

su - postgres -c "psql"
CREATE USER repluser REPLICATION LOGIN ENCRYPTED PASSWORD 'Maropost@#1234';

Take backup

su - postgres
SELECT pgstartbackup('label', true);
tar -czvf backup.tar.gz 10/data
SELECT pgstopbackup();

Slave

su - postgres
rm -rf /var/lib/pgsql/10/data/*
scp postgres@10.137.136.228:/var/lib/pgsql/backup.tar.gz .
tar xvf backup.tar.gz

vi /var/lib/pgsql/10/data/postgresql.conf # Change IP

vi /var/lib/pgsql/10/data/recovery.conf # Change IP
standbymode = on
primary
conninfo = 'host=10.137.136.228 user=repluser'
restorecommand = 'cp /var/lib/pgsql/wal-archives/%f %p'
trigger
file = '/tmp/pg-trigger-failover-now'
recoverytargettimeline = 'latest'

Info

This tells the slave to keep pulling WALs from master.

This is how to connect to the master.

restore - pending

Trigger file allow standby to promote to master.

This tells PostgreSQL how to retrieve archived WAL file segments.

systemctl start postgresql-10.service

++++++++++++++++++++++++++++

Commands to check replication

select now() - pglastxactreplaytimestamp() AS replicationdelay;
psql -c "select pg
isinrecovery();"

++++++++++++++++++++++++++++