Skip to content

Instantly share code, notes, and snippets.

@alexnoz

alexnoz/pg_hba.conf

Last active March 18, 2020 21:41
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save alexnoz/240bf3d19472c69eb33ceace8f4b306d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save alexnoz/240bf3d19472c69eb33ceace8f4b306d to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL master-slave replication example
Raw
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
Raw
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'
Raw
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'
Raw
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;'
  1. sudo service postgresql-12 restart

On slave

  1. Remove the contents of $PGDATA

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

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

  4. Set hot_standby = on in postgresql.conf

  5. Start postgres server

/usr/pgsql-12/bin/pg_ctl -D "$PGDATA" start
  1. (Probably unnecessary) sudo service postgresql-12 restart
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment