Postgresql replication on CentOS

I will use 2 machines to do this. For the sake of practicing, even 2 containers will do. The IP addresses are:

- master 10.10.10.1

- slave 10.10.10.2

Install postgresql repo on both machines

# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install postgresql on both machines, in this example, I am using postgres 9.6

# yum install -y postgresql96-server

Initialize both postgres

# su - postgres

$ /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/data/

On master, put in below config

# su - postgres

$ cat >> /var/lib/pgsql/9.6/data/postgresql.conf <<EOF

wal_level = hot_standby

max_wal_senders = 1 # number of slave servers

wal_keep_segments = 100

synchronous_standby_names = 'pgslave'

EOF

On master, create a user for replication, called replica

$ psql -c "create user replica replication;"

Allow slave to access master as replica

$ cat >> /var/lib/pgsql/9.6/data/pg_hba.conf <<EOF

host    replication     replica          10.10.10.2/32         trust

EOF

Restart postgres on master server

# systemctl restart postgresql-9.6

On slave server, stop postgresql

# systemctl stop postgresql-9.6

Clear slave server postgresql data directory

# mv /var/lib/pgsql/9.6/data/ /var/lib/pgsql/9.6/data-old
# sudo -u postgres mkdir /var/lib/pgsql/9.6/data

Copy data from master

# su - postgres

$ pg_basebackup -D /var/lib/pgsql/9.6/data -h 10.10.10.1 -U replica --verbose

Create recovery.conf in slave server

$ cat > /var/lib/pgsql/9.6/data/recovery.conf <<EOF

standby_mode=on

trigger_file='/tmp/promotedb'

primary_conninfo='host=10.10.10.1 port=5432 user=replica application_name=pgslave'

EOF

Turn hot_standby to on, on slave server

$ sed -i 's/#hot_standby\ =\ off/ hot_standby\ =\ on/'/var/lib/pgsql/9.6/data/postgresql.conf

Start postgres on slave

# systemct start postgresql-9.6

To check replication status, run below in master server

# su - postgres

$ psql -c "select client_addr, state, sent_location, write_location,flush_location, replay_location from pg_stat_replication;"

Test your replication by adding data/database into master server, and check whether the data/database is replicated to slave.

If a master is down, you need to promote the current slave to master, to allow it to be writable

# su - postgres

$ /usr/pgsql-9.6/bin/pg_ctl promote -D /var/lib/pgsql/9.6/data/