Before you proceed this handy procedure, be sure you take backup of the databases.
pg_dumpall > dump.sql
Prerequisite:
- Two Ubuntu(14.04) instances:
Master(187.12.0.1)
Slave(187.12.0.2) - PostgreSQL 9.4.x
Steps to be followed on MASTER:
Step 1: Create a replication user on the master with REPLICATION permissions:
sudo -u postgres createuser -U postgres replicator -P -c 5 --replication
-P prompts you for the new user’s password.
-c sets a limit for the number of connections for the new user. The value 5 is sufficient for replication purposes.
–replication grants the REPLICATION privilege to the user named replication.
Step 2: Configure master for streaming replication
vi /etc/postgresql/9.4/main/postgresql.conf listen_address = '' wal_level = hot_standby max_wal_senders = 3 checkpoint_segments = 8 wal_keep_segments = 8
Here each wal_keep_segments is of 16MB. If you expect your database to have more than 128MB of changes in the time it will take to make a copy of it across the network to your slave, or in the time you expect your slave to be down for maintenance or something, then consider increasing those values.
Step 3: Configure master to allow the connection from the slave
vi /etc/postgresql/9.4/main/pg_hba.conf
#if your server is not ssl ready, use hostnossl
hostssl replication replicator 187.12.0.2 md5 Or hostnossl replication replicator 187.12.0.2 md5
here hostssl means this host can only connect via SSL.
Restart the server now.
Steps to be followed on SLAVE:
Step 1: Configure master for streaming replication
vi /etc/postgresql/9.4/main/postgresql.conf wal_level = hot_standby max_wal_senders = 3 checkpoint_segments = 8 wal_keep_segments = 8 hot_standby = on
No change required for pg_hba.conf
Step 2: Copy the database from master and begin replication
#Stop server
sudo service postgresql stop
#Clean up old cluster directory
ATTENTION: script is going to delete the old database cluster on your slave.
sudo -u postgres rm -rf /var/lib/postgresql/9.4/main
#Start base backup as replicator with pg_basebackup command
sudo -u postgres pg_basebackup -h 187.12.0.1 -D /var/lib/postgresql/9.4/main -U replicator -v -P
Create recovery.conf file and update the content
sudo -u postgres bash -c "cat > /var/lib/postgresql/9.4/main/recovery.conf <<- _EOF1_ standby_mode = 'on' primary_conninfo = 'host=187.12.0.1 port=5432 user=replicator password=PASSWORD sslmode=require' trigger_file = '/tmp/postgresql.trigger' _EOF1_ " NOTE: remove sslmode=require is ssl is not configured for the server.
#Start the PostgreSQL server
sudo service postgresql start
Great, we have completed the replication of PostgreSQL database. To make sure everything working as expected execute and watch:
sudo -u postgres psql -x -c "select * from pg_stat_replication;"
Happy learning.