Replication

master

create replication user "replicate"

CREATE ROLE replicate WITH REPLICATION LOGIN;
\password replicate

set parameters correctly

listen_addresses = '*'
wal_level = replica
max_wal_senders = 3 # max number of walsender processes
wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables
synchronous_standby_names = 'appname'

maybe you want enable archive

archive_mode = on
archive_command = 'rsync -a %p postgres@slave:/home/postgresql_wal/%f' 
   # placeholders: %p = path of file to archive
   #               %f = file name only

enable slave access to master. Edit pg_hba.conf

hostssl     replication     replicate       xxx.xxx.xxx.xxx/yy      scram-sha-256

slave

stop postgresql

edit postgresql.conf and pg_hba.conf and report the changes you made on the master (like this, your slave will have the same configuration and could act as a master)

postgresql.conf

hot_standby = on

go to your PGDATA directory and delete all the files

copy all the data from the master with the pg_basebackup command

su postgres
pg_basebackup -h 172.17.0.2 -D /var/lib/postgresql/10/main/ -P -U replicate --wal-method=stream

create a file recovery.conf in your PGDATA (/var/lib/postgresql/9.6/main/recovery.conf) directory

standby_mode          = 'on'
primary_conninfo      = 'host=172.17.0.2 port=5432 user=replicate password=MySuperPassword application_name=appname'
trigger_file = '/tmp/MasterNow'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'

# standby_mode=on : specifies that the server must start as a standby server
# primary_conninfo : the parameters to use to connect to the master
# trigger_file : if this file exists, the server will stop the replication and act as a master
# restore_command : this command is only needed if you have used the archive_command on the master

chmod 600 recovery.conf

check sync on master

select * from pg_stat_activity  where usename = 'replicate' ;

check replication done 0

select pg_is_in_recovery();

check replication done 1

on master

SELECT pg_current_xlog_location();

on slave

SELECT pg_last_xlog_receive_location();

see solution #3

check replication done 2

If your database has frequent writes, then the below query is a close approximation to get the slave lag

select now() - pg_last_xact_replay_timestamp() AS replication_delay;

Below is a more accurate query for calculating replication lag for databases with very few writes. If the master doesnt sent down any write to the slave, then pg_last_xact_replay_timestamp() can be constant and hence may not accurately determine the slave lag using the above query.

SELECT CASE WHEN pg_last_xlog_receive_location() =
pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
pg_last_xact_replay_timestamp()) END AS log_delay;