Postgresql: how to enable pg_stat_replication_slots?

All we need is an easy explanation of the problem, so here it is.

I have a streaming replication on pg 14.2 that seems to be working, on a slot named slot1.

select * from pg_stat_replication_slots has no output. Do I need to enable anything else?

I have those on:

  • track_activities
  • track_counts

Streaming INFO:

select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------
pid              | 21483
usesysid         | 376382
usename          | repl
application_name | replication
client_addr      | 172.28.201.16
client_hostname  | 
client_port      | 42822
backend_start    | 2022-03-17 11:25:05.599057-03
backend_xmin     | 
state            | streaming
sent_lsn         | 56/4729C750
write_lsn        | 56/4729C750
flush_lsn        | 56/4729C750
replay_lsn       | 56/46007120
write_lag        | 00:00:00.005546
flush_lag        | 00:00:00.011271
replay_lag       | 00:59:34.522039
sync_priority    | 0
sync_state       | async
reply_time       | 2022-03-17 12:24:49.02266-03

select * from pg_replication_slots;

-[ RECORD 1 ]-------+------------
slot_name           | slot1
plugin              | 
slot_type           | physical
datoid              | 
database            | 
temporary           | f
active              | t
active_pid          | 21483
xmin                | 
catalog_xmin        | 
restart_lsn         | 56/47313758
confirmed_flush_lsn | 
wal_status          | reserved
safe_wal_size       | 10750970024
two_phase           | f

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

An important word in the documentation:

The pg_stat_replication_slots view will contain one row per logical replication slot, showing statistics about its usage.

As intended, there is nothing for physical replication slots here. Perhaps this view could be called differently, but now it’s too late to rename.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply