postgres-replication.txt

*********************
Streaming Replication
*********************

++++++
Master
++++++

postgresql.conf
---------------

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = 'localhost,172.31.14.134' # Also: mind the firewall

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
wal_level = hot_standby
# Wait for standbys to finish writing out WALs to disk, before returning from
# the commit
synchronous_commit = remote_write

# - Archiving -
# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
# archive_mode = on
# archive_command = 'cp %p /data/cache/postgres/archive/%f'
# [0 0 * * * /bin/bash -l -c 'find /data/cache/postgres/archive -maxdepth 1 -mtime +1 | xargs rm >> /dev/null 2>&1']

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
max_wal_senders = 5 # max number of walsender processes
# Specifies the minimum number of past log file segments kept in the pg_wal
# directory, in case a standby server needs to fetch them for streaming
# replication
wal_keep_segments = 32

# - Master Server -
synchronous_standby_names = pgslave1


pg_hba.conf
-----------

# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'passwd';

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# The standby server must connect with a user that has replication privileges
host    replication     replica          172.31.4.141/32        md5


+++++
Slave
+++++

# Set up replication-related parameters, connections and authentication in the
# standby server like the primary, so that the standby might work as a primary
# after failover

postgresql.conf
---------------

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = 'localhost,172.31.4.141' # Again: mind the firewall

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
wal_level = hot_standby
synchronous_commit = remote_write

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
max_wal_senders = 5
wal_keep_segments = 32

# - Master Server -
synchronous_standby_names = pgslave1


pg_hba.conf
-----------

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    ceds_production ceds            172.31.14.134/32        md5


+++++++++++++++++++++++++++++++++++++++++++++++++++
Syncronize data from the Master to the Slave server
+++++++++++++++++++++++++++++++++++++++++++++++++++

# systemctl stop postgresql
$ pg_basebackup --host=172.31.14.134 --pgdata=/var/lib/postgresql/10/main --username=replica --verbose --progress --wal-method=stream

# systemctl start postgresql

+++++
Slave
+++++

# Enable read-only queries on the standby server
# - Standby Servers -
hot_standby = on
# This parameter determines how long the standby server should wait before
# canceling standby queries that conflict with about-to-be-applied WAL entries
max_standby_streaming_delay = 90s


/var/lib/postgresql/10/main/recovery.conf
------------------------------------------

standby_mode = on
primary_conninfo = 'host=172.31.14.134 port=5432 user=replica password=passwd application_name=pgslave1'
trigger_file = '/tmp/postgresql.trigger.5432'

# systemctl start postgresql


++++++++++++++++++++++++++++
Testing on the master server
++++++++++++++++++++++++++++

psql -h localhost ceds_production ceds -c 'select * from pg_stat_replication'


-[ RECORD 1 ]----+------------------------------
pid              | 11202
usesysid         | 2156626
usename          | replica
application_name | pgslave1
client_addr      | 172.31.4.141
client_hostname  | [NULL]
client_port      | 54342
backend_start    | 2018-03-20 10:48:06.682015+00
backend_xmin     | [NULL]
state            | streaming
sent_lsn         | 1601/B7045478
write_lsn        | 1601/B7045478
flush_lsn        | 1601/B7045478
replay_lsn       | 1601/B7045478
write_lag        | [NULL]
flush_lag        | [NULL]
replay_lag       | [NULL]
sync_priority    | 1
sync_state       | sync


*******************
Logical Replication
*******************

++++++
Master
++++++

CREATE PUBLICATION test_pub FOR TABLE users, addresses;
CREATE PUBLICATION all_tables FOR ALL TABLES;


+++++
Slave
+++++

# Note that the definition of the table is not copied at this time, so we need to
# create the tables ourselves, as the replication worker will error if it can’t
# find the table locally.

CREATE SUBSCRIPTION test_subs CONNECTION 'host=1.2.3.4 dbname=ceds' PUBLICATION test_pub;

# When new tables are added to the publication, the subscription will not learn
# about them automatically

ALTER SUBSCRIPTION test_subs REFRESH PUBLICATION;


++++++++++
Monitoring
++++++++++

On the slave:
-------------

SELECT * FROM pg_stat_subscription;

-[ RECORD 1 ]---------+------------------------------
subid                 | 16403
subname               | test_subs
pid                   | 13109
relid                 | [NULL]
received_lsn          | 0/15F1A10
last_msg_send_time    | 2017-04-28 10:38:17.862848+02
last_msg_receipt_time | 2017-04-28 10:38:17.864402+02
latest_end_lsn        | 0/15F1A10
latest_end_time       | 2017-04-28 10:38:17.862848+02


--------------
On the master:
--------------

SELECT application_name, backend_start, state, sent_location, write_location,
       flush_location, sync_state
FROM pg_stat_replication;

-[ RECORD 1 ]----+------------------------------
application_name | test_subs
backend_start    | 2017-04-28 10:38:16.852043+02
state            | streaming
sent_location    | 0/15F1A10
write_location   | 0/15F1A10
flush_location   | 0/15F1A10
sync_state       | async