PostgreSQL master-slave replication example

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

# This is necessary for replication, the user must exist with replication permission
# TODO: Replace `trust` with something more secure
host    replication     replica         34.243.240.207/32       trust

postgresql.conf

# Settings that're essential for replication
listen_addresses = '*'		
max_connections = 100			
wal_level = replica			
wal_keep_segments = 100
min_wal_size = 80MB
max_wal_senders = 1		
synchronous_standby_names = 'master1v'	

recovery.conf

standby_mode=on
trigger_file='/tmp/promotedb'
# application_name as specified in master's postgresql.conf 'synchronous_standby_names' setting
# user as specified in master's pg_hba.conf
primary_conninfo='host=172.31.18.31 port=5432 user=replica application_name=master1v'

replication.md

We'll assume the following environment:

🐘	💾
OS	CentOS 7
PostgreSQL version	12
Master IP	172.31.18.31
Slave IP	34.243.240.207
$PGDATA	/var/lib/pgsql/12/data

Steps

All steps should be performed as postgres user, unless otherwise stated.

On master

1. Configure postgresql.conf and pg_hba.conf (see sample configs)

2. Create replication user

psql -c 'CREATE USER replica replication;'

3. sudo service postgresql-12 restart

On slave

4. Remove the contents of $PGDATA

5. Perform backup pg_basebackup -D "$PGDATA" -h 172.31.18.31 -U replica

6. Add recovery.conf to $PGDATA (see sample file)

7. Set hot_standby = on in postgresql.conf

8. Start postgres server

/usr/pgsql-12/bin/pg_ctl -D "$PGDATA" start

9. (Probably unnecessary) sudo service postgresql-12 restart