Setup PostgreSQL 9.3 replication on CentOS 6.8

This is a short introduction of setting up PostgreSQL replication on CentOS 6.
The version of PostgreSQL is 9.3, the version of CentOS is 6.8 in both master and slave server.

Prequisite

In this article, I will use master and slave servers as following:

  • Master server: Hostname: centos1, IP: 192.168.56.102
  • Slave server: Hostname: centos2, IP address: 192.168.56.101
  • The PostgreSQL user used for replication is replicator, with password is thepassword.

To install PostgreSQL 9.3 on CentOS 6.8, see https://umbalaconmeogia.wordpress.com/2016/08/07/install-postgresql-9-5-on-centos-6/

Setup master server

Create replication user replicator

# sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'upper_house2084';"

Update postgresql.conf

Edit the file /var/lib/pgsql/9.3/data/postgresql.conf with following setting.

listen_addresses = 'localhost,192.168.56.102' # Set for replication
wal_level = hot_standby # Set for replication
checkpoint_segments = 8 # Set for replication
max_wal_senders = 3 # Set for replication
wal_keep_segments = 8 # Set for replication

Update pg_hba.conf

Add the following line into the file /var/lib/pgsql/9.3/data/pg_hba.conf.

host replication replicator 192.168.56.101/32 md5

Restart postgresql

# service postgresql-9.3 restart

Set firewall to enable access from slave server

Add following lines into the file /etc/sysconfig/iptables, before REJECT and COMMIT line.

-A INPUT -p tcp -s 192.168.56.101 --sport 1024:65535 -d 192.168.56.102 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -s 192.168.56.102 --sport 5432 -d 192.168.56.101 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart filewall

# service iptables restart

Setup slave server, copy data from the master

Update postgresql.conf

Edit the file /var/lib/pgsql/9.3/data/postgresql.conf with following setting.

wal_level = hot_standby # Set for replication
checkpoint_segments = 8 # Set for replication
max_wal_senders = 3 # Set for replication
wal_keep_segments = 8 # Set for replication
hot_standby = on # Set for replication

Restart postgresql

# service postgresql-9.3 restart

Copy data from master server

Backup postgresql.conf, copy data from the master server, restore postgresql.conf.
Should run commands below at location outside /var/lib/pgsql/9.3/data

# echo Backing up postgresql.conf
# cp /var/lib/pgsql/9.3/data/postgresql.conf .
 
# echo Stopping PostgreSQL
# service postgresql-9.3 stop
 
# echo Cleaning up old cluster directory
# sudo -u postgres rm -rf /var/lib/pgsql/9.3/data
 
# echo Starting base backup as replicator
# sudo -u postgres pg_basebackup -h 192.168.56.102 -D /var/lib/pgsql/9.3/data -U replicator -v -P
 
# echo Writing recovery.conf file
# sudo -u postgres bash -c "cat > /var/lib/pgsql/9.3/data/recovery.conf <<- _EOF1_
  standby_mode = 'on'
  primary_conninfo = 'host=192.168.56.102 port=5432 user=replicator password=thepassword'
  trigger_file = '/tmp/postgresql.trigger'
_EOF1_
"
 
# echo Restoring postgresql.conf
# mv postgresql.conf /var/lib/pgsql/9.3/data/postgresql.conf
# chown postgres.postgres /var/lib/pgsql/9.3/data/postgresql.conf
 
# echo Startging PostgreSQL
# service postgresql-9.3 start

Reference

Link http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/
In this introduction, I omit the SSL usage comparing to the reference link.
I also add the step of backing up/restoring the file postgresql.conf on slave server.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: