Wednesday, July 19, 2017

 

Postgresql Streaming Replication recipe


This is a quick recipe for PostgreSql 9.x Streaming Replication configuration
between a Master and Slave servers based on https://www.howtoforge.com/tutorial/postgresql-replication-on-ubuntu-15-04/

Master: IP 192.168.1.249

Everything should be done as postgres user.
psql CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'replicauser@';
# ctrl + d to exit
cd /etc/postgresql/9.4/main
vim postgresql.conf
listen_addresses = 'localhost,192.168.1.248'
wal_level = hot_standby
checkpoint_segments = 8
archive_mode = on
archive_command = 'cp -i %p /var/lib/postgresql/9.4/main/archive/%f'
max_wal_senders = 3
wal_keep_segments = 8
# save and close
mkdir -p /var/lib/9.4/main/archive/
vim pg_hba.conf
host    replication     replica      192.168.1.248/24            md5
#192.168.1.248 is slave-server ip address
# save and close
sudo service postgresql start
Master server done.

Slave: IP: 192.168.1.248

Again, everything as postgres user.
su - postgres
cd /etc/postgresql/9.4/main/
vim postgresql.conf
listen_addresses = 'localhost,192.168.1.249'
wal_level = hot_standby
checkpoint_segments = 8
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
# save and close
sudo service postgresql stop
pg_basebackup -h 192.168.1.249 -D /var/lib/postgresql/9.4/main -U replica -v -P
cd /var/lib/postgresql/9.4/main/
vim recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.249 port=5432 user=replica password=replicauser@'
restore_command = 'cp /var/lib/postgresql/9.4/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
# save and close
sudo service postgresql start
Done!.

This page is powered by Blogger. Isn't yours?