ergemp
- Feb 7
- 4 min read

Postgresql Replication Series - 3

In this post of the replication, I am going to cover the replication_slots. All the configuration and the infrastructure will be the same as in the Postgresql Replication Series - 2.

The main problem about the streaming replication is; if the standby database is down for some time, the wal files in the primary database may get deleted. In this case there are two solutions:

Use log shipping alongside with the streaming replication with archive_command on the master and restore_command on the standby. In this case even if the streaming replication down for some time and the wal files are deleted, necessary wal files can be restore from the wal archives.
Create a replication slot on the primary database and set the primary_conninfo parameter and primary_slot_name with the replication slot created on the primary database.

Infrastructure

Setting of the blog is going to be two virtual machines with centos stream 9 and postgresql 15 installed;

centos01: 10.211.55.4 (primary database)

centos02: 10.211.55.5 (standby database)

Primary Database Configuration

Primary database configuration is the same with the previous posts of this series. Following code can be followed for primary configuration.

postgres=# alter system set archive_mode=on;
ALTER SYSTEM

postgres=# alter system set archive_command='cp %p /pg_data/15/wal_archive/%f';
ALTER SYSTEM

postgres=# create user replication_user with replication encrypted password 'replication_password';
CREATE ROLE
postgres=# alter user replication_user with password 'replication_user';

postgres=# select name, setting from pg_settings where name like '%hba%';
   name   |            setting             
----------+--------------------------------
 hba_file | /etc/postgresql/15/pg_hba.conf
(1 row)

postgres=# show hba_file;
            hba_file            
--------------------------------
 /etc/postgresql/15/pg_hba.conf
(1 row)

host    replication     all             10.211.55.5/32         md5

This time, I am going to create a replication slot on primary database to be used from the standby.

SELECT pg_create_physical_replication_slot('replication_slot');
/*
pg_create_physical_replication_slot|
-----------------------------------+
(replication_slot,) |
*/

select * from pg_replication_slots;
/*
Name				|Value 			 |
---------------------+----------------+
slot_name 			|replication_slot|
plugin 				| 				 |
slot_type 			|physical 		 |
datoid 				| 				 |
database 			| 				 |
temporary 			|false 			 |
active 				|false 			 |
active_pid 			| 				 |
xmin 				| 				 |
catalog_xmin 		| 				 |	
restart_lsn 		| 				 |
confirmed_flush_lsn	| 				 |
wal_status 			| 				 |
safe_wal_size 		| 				 |
two_phase 			|false 			 |
*/

Standby Database Configuration

Now we need to take a backup of the primary database and copy the backup to secondary, or (as in this example) we run the pg_basebackup from the secondary database to bypass the copy workload.

The difference from the previous post is the -Xs option instead of -Xn.

-X (--wal_method)

n (none): do not manage wal files

s (stream): stream wal files, immediately after the backup.

Backup and restore the the primary database to standby.

pg_basebackup -h 10.211.55.4 -p 5432 -U replication_user -W -Ft -Xs -v -D /pg_data/15/backups/base_backup

rm -rf /pg_data/15/data/*

cd /pg_data/15/backups/base_backup
tar -xvf base.tar -C /pg_data/15/data
tar -xvf pg_wal.tar -C /pg_data/15/data/pg_wal/

Since we have the backup from the primary database restore to the standby database. Now we need to configure the standby:

Set primary_conninfo parameter on standby database.
Set primary_slot_name parameter on standby database.
create standby.signal file in the $PGDATA directory.

# standby database postgresql.conf file 
# replication 
primary_conninfo = 'user=replication_user password=replication_password  host=10.211.55.4 port=5432'
primary_slot_name = 'replication_slot' 

touch $PGDATA/standby.signal

Now we are ready to start the standby database service.

[root@centos02 15]# systemctl start postgresql-15

Monitoring

Same as the previous post, pg_stat_replication on the master and pg_stat_wal_receiver system views contains the deailed information about the replication.

Since we are using replication slot for the standby server, now we also information about the restart_lsn which holds the necessary wal files

On the primary database;

postgres=# select * from pg_catalog.pg_stat_replication;
pid              | 1425
usesysid         | 16532
usename          | replication_user
application_name | walreceiver
client_addr      | 10.211.55.5
client_hostname  | 
client_port      | 56786
backend_start    | 2024-02-06 14:32:26.442483+00
backend_xmin     | 
state            | streaming
sent_lsn         | 0/7000E18
write_lsn        | 0/7000E18
flush_lsn        | 0/7000E18
replay_lsn       | 0/7000E18
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2024-02-06 14:33:06.492828+00

postgres=# select * from pg_replication_slots;
-[ RECORD 1 ]-------+-----------------
slot_name           | replication_slot
plugin              | 
slot_type           | physical
datoid              | 
database            | 
temporary           | f
active              | t
active_pid          | 1425
xmin                | 
catalog_xmin        | 
restart_lsn         | 0/7001040
confirmed_flush_lsn | 
wal_status          | reserved
safe_wal_size       | 
two_phase           | f

postgres=# select * from pg_stat_activity where pid=1425;
datid            | 
datname          | 
pid              | 1425
leader_pid       | 
usesysid         | 16532
usename          | replication_user
application_name | walreceiver
client_addr      | 10.211.55.5
client_hostname  | 
client_port      | 56786
backend_start    | 2024-02-06 14:32:26.442483+00
xact_start       | 
query_start      | 2024-02-06 14:32:26.45934+00
state_change     | 2024-02-06 14:32:26.459348+00
wait_event_type  | Activity
wait_event       | WalSenderMain
state            | active
backend_xid      | 
backend_xmin     | 
query_id         | 
query            | START_REPLICATION SLOT "replication_slot" 0/7000000 TIMELINE 1
backend_type     | walsender

On the standby database;

postgres=# select * from pg_catalog.pg_stat_wal_receiver;
pid                   | 1324
status                | streaming
receive_start_lsn     | 0/7000000
receive_start_tli     | 1
written_lsn           | 0/7001040
flushed_lsn           | 0/7001040
received_tli          | 1
last_msg_send_time    | 2024-02-06 17:28:48.830722+00
last_msg_receipt_time | 2024-02-06 17:28:48.742314+00
latest_end_lsn        | 0/7001040
latest_end_time       | 2024-02-06 14:34:31.685515+00
slot_name             | replication_slot
sender_host           | 10.211.55.4
sender_port           | 5432
conninfo              | user=replication_user password=******** channel_binding=prefer dbname=replication host=10.211.55.4 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

Considerations

Since the default value of max_slot_wal_keep_size parameter is -1 (unlimited) there is no limit to hold the previous wal files in the master database. So keep in mind the downtime of the standby database because holding these wal files can consume significant amount of disk space on the primary database.