2

Using Postgres 14 on Ubuntu:

I have logical replication setup from master => slave => slave2; It is replicating, but there is a problem with safe_wal_size.

Using du -sh /var/lib/postgresql/14

  1. After loading one 4GB SQL file, master is using 9.2GB of space
  2. After replication becomes up to date slave is using 9.2GB of space
  3. After replication becomes up to date slave2 is using 9.2GB of space

master reports the following:

 SELECT slot_name, active, wal_status, safe_wal_size
  FROM pg_catalog.pg_replication_slots;
 slot_name | active | wal_status | safe_wal_size
-----------+--------+------------+---------------
 replica_1 | t      | reserved   |    8598896640

slave reports the following:

SELECT slot_name, active, wal_status, safe_wal_size
  FROM pg_catalog.pg_replication_slots;
 slot_name | active | wal_status | safe_wal_size
-----------+--------+------------+---------------
 replica_1 | t      | reserved   |  124570828800

Even though both machines are configured alike, (default params except for the following)

wal_level = logical         # minimal, replica, or logical
max_slot_wal_keep_size = 8192   # in megabytes; -1 disables
wal_log_hints = on          # also do full page writes of non-critical updates

Why does the middle slave think it should provide more than 12GB of WAL space?

I re-created the DB and replication setup on slave now I get:

 SELECT slot_name, active, wal_status, safe_wal_size
  FROM pg_catalog.pg_replication_slots;
 slot_name | active | wal_status | safe_wal_size
-----------+--------+------------+---------------
 replica_1 | t      | reserved   |  447666454528

This safe_wal_size is now in excess of the disk space on the machine, so effectively it can run the machine out of disk space if slave2 goes down and does not get back up quickly enough.....

4
  • It's not that it should, it's that it can.
    – mustaccio
    Jan 19, 2023 at 0:58
  • It's been told to use 8GB for WAL replication, the first machine does, but it chooses to use 12GB which does not make sense given the config
    – boatcoder
    Jan 19, 2023 at 3:06
  • 1
    Perhaps looking at the source code will be helpful.
    – mustaccio
    Jan 19, 2023 at 3:19
  • I did, and the number is effectively calculated as slot's restart_lsn + max(max_slot_wal_keep_size, wal_keep_size) - current LSN. So I'd look at restart_lsn of the slot and pg_current_wal_lsn() on both servers. Jan 20, 2023 at 8:23

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.