Monday, May 6, 2019

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          192.168.1.33/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
# rm -rf /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 master 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 on
$ sed -i 's/#hot_standby\ =\ off/ hot_standby\ =\ on/'/var/lib/pgsql/9.6/data/postgresql.conf

Start postgres on slave
# systemct restart 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/
  

No comments: