Viswamitra's blog

PostgreSQL has different in built high availability features for application like streaming replication and logical replication. By using streaming replication we can configure two or more standby servers which receive the transactions from master and apply it on standby server, so that the standby will be used as master during the time of master server crash.

PostgreSQL 12 has interesting changes to replication configuration compare to previous versions.

1. recovery.conf file is removed on standby server and made all the parameters of recovery.conf as parameters in postgresql.conf
2. A new file introduced “standby.signal” as a replacement to standby_mode parameter, which indicates the server should start in hot standby mode. If this file is missed PostgreSQL will be started as primary.
3. trigger_file parameter is renamed to promote_triger_file.

Before we dive into replication configuration we will see some of the important configuration parameters used for configuration.

Parameters on master

max_wal_senders – This parameter specifies the maximum number of concurrent connections allowed on the sender from standby(s) or tools like pg_basebackup clients for streaming. The default value is 10. Set this parameter based on number standby(s) and pg_basebackup like tool going to stream the data from a sender.

max_replication_slots – This parameter specifies the maximum number of replication slots that a sender can support, and the number of standby(s)/tools can be allowed to use replication slots to stream data. The default value is 10. Set this parameter based on the number of standby(s)/tools is going to use for streaming.

wal_sender_timeout – With this parameter, a sender/master decides on terminating replication connections that are inactive for longer than the amount of time mentioned for the parameter. This parameter is useful to detect a standby crash or network outage. The default value is 60 seconds. A value of zero disables the timeout mechanism.

wal_level = replica – With this parameter settings, the master server decides on how much information is written to the WAL. The default value of this parameter is replica. With this setting, the sender writes enough data to enable streaming replication.

wal_log_hints = on – This parameter makes a master server write each disk page to WAL with the first modification with hint bits. With this setting, you can use pg_rewind to make a master be standby of a new master after the failover.

Parameters on standby

primary_conninfo – ‘connection string of master/sender’

primary_slot_name = ‘name of the replication slot on sender/master

promote_trigger_file = /trigger/file/location – when this file is created on standby server it make it self as master.

hot_standby = on – This parameter configures a standby(s) to allow connections and execute READ queries during recovery. The default value of this parameter is on.

recovery_target_timeline = latest – during the time of failover it makes the standby to follow the new master server.

Test environment

192.168.159.147 – Master server

192.168.159.151 – Standby server

Replication configuration

On Master server

1. Configure listen_address to appropriate value, create a user with replication permission

 postgres=# alter system set listen_addresses to '*';
 ALTER SYSTEM
 postgres=# create user viswamitra password 'postgres' replication;
 CREATE ROLE
 postgres=#

2. Make a replication entry for the user in pg_hba.conf with standby server ip address and reboot the instance

-bash-4.2$ grep viswamitra pg_hba.conf
 host    replication     viswamitra      192.168.159.151/32      md5
/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data restart

On Slave Server

3. Take a base backup of the master server using pg_basebackup tool

-bash-4.2$ pg_basebackup --pgdata=standby --write-recovery-conf --create-slot --slot=standby1 --verbose --host=192.168.159.147 --port=5432 --username=viswamitra
 pg_basebackup: initiating base backup, waiting for checkpoint to complete
 pg_basebackup: checkpoint completed
 pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
 pg_basebackup: starting background WAL receiver
 pg_basebackup: created replication slot "standby1"
 pg_basebackup: write-ahead log end point: 0/2000138
 pg_basebackup: waiting for background process to finish streaming …
 pg_basebackup: syncing data to disk …
 pg_basebackup: base backup completed
 -bash-4.2$

The command options I used here it self pretty informative, if you want to know more about this options click here.

4. Below is the content of the data directory (I named it as standby) created by previous step.

standby.signal file is created as described previously, below is the content of postgresql.auto.conf file.

-bash-4.2$ cat postgresql.auto.conf
 Do not edit this file manually!
 It will be overwritten by the ALTER SYSTEM command.
 listen_addresses = '*'
 primary_conninfo = 'user=viswamitra passfile=''/var/lib/pgsql/.pgpass'' host=192.168.159.147 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
 primary_slot_name = 'standby1'
 -bash-4.2$

-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D standby start
 waiting for server to start….2020-12-09 10:54:56.497 PST [3955] LOG:  starting PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
 2020-12-09 10:54:56.499 PST [3955] LOG:  listening on IPv4 address "0.0.0.0", port 5432
 2020-12-09 10:54:56.499 PST [3955] LOG:  listening on IPv6 address "::", port 5432
 2020-12-09 10:54:56.501 PST [3955] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
 2020-12-09 10:54:56.512 PST [3955] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
 2020-12-09 10:54:56.524 PST [3955] LOG:  redirecting log output to logging collector process
 2020-12-09 10:54:56.524 PST [3955] HINT:  Future log output will appear in directory "log".
  done
 server started

5. Verify the replication configuration

On standby

postgres=# select * from pg_stat_wal_receiver ;
 -[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 pid                   | 3961
 status                | streaming
 receive_start_lsn     | 0/3000000
 receive_start_tli     | 1
 received_lsn          | 0/3000148
 received_tli          | 1
 last_msg_send_time    | 2020-12-09 10:55:26.599891-08
 last_msg_receipt_time | 2020-12-09 10:55:26.608928-08
 latest_end_lsn        | 0/3000148
 latest_end_time       | 2020-12-09 10:54:56.536338-08
 slot_name             | standby1
 sender_host           | 192.168.159.147
 sender_port           | 5432
 conninfo              | user=viswamitra passfile=/var/lib/pgsql/.pgpass dbname=replication host=192.168.159.147 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

On master

-bash-4.2$ psql
 psql (12.4)
 Type "help" for help.
 postgres=# \x
 Expanded display is on.
 postgres=# select * from pg_stat_replication ;
 -[ RECORD 1 ]----+------------------------------
 pid              | 5234
 usesysid         | 16384
 usename          | viswamitra
 application_name | walreceiver
 client_addr      | 192.168.159.151
 client_hostname  |
 client_port      | 50846
 backend_start    | 2020-12-09 10:54:56.533403-08
 backend_xmin     |
 state            | streaming
 sent_lsn         | 0/3000148
 write_lsn        | 0/3000148
 flush_lsn        | 0/3000148
 replay_lsn       | 0/3000148
 write_lag        |
 flush_lag        |
 replay_lag       |
 sync_priority    | 0
 sync_state       | async
 reply_time       | 2020-12-09 10:56:56.65843-08