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
- slave

Install postgresql repo on both machines
# yum install -y

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'

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         trust

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 -U replica --verbose

Create recovery.conf in slave server
$ cat > /var/lib/pgsql/9.6/data/recovery.conf <<EOF
primary_conninfo='host= port=5432 user=replica application_name=pgslave'

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/

No comments: