PostgreSQL not deleting old WAL archives

Question

I have Postgresql 13 database and Barman 2.7 deployed for backing it up. The Barman is deployed on separated server. Barman is using streaming backup and WAL streaming via pg_recievewal.

However the pg_wal directory become enormously large/not cleaned up

The replication slot reports that barman received the latest WAL, Barman shows no indication of issues on his side. I used the following query to find if the last WAL received by Barman:

SELECT slot_name,
        lpad((pg_control_checkpoint()).timeline_id::text, 8, '0') ||
        lpad(split_part(restart_lsn::text, '/', 1), 8, '0') ||
        lpad(substr(split_part(restart_lsn::text, '/', 2), 1, 2), 8, '0')
        AS wal_file
FROM pg_replication_slots;

The value reported by it, is the same as the value reported by SELECT pg_walfile_name(pg_current_wal_lsn());

After enabling archival with barman-wal-archive the pg_wal directory started cleaning up. If I understood correctly having archival is not mandatory when pg_recievewal is used.

Is my understanding correct? If so, then why are WAL archives piling up?

are you sure wal streaming is effective ? what is your barman check output ? — Gab, Aug 04 '23 at 14:22
Before taking the backup everything was with green "OK". Unfortunately I cannot do barman check at the moment, because the max wal readers is 2 (one occupied by the stuck backup and one from the pg_recievewal). I'm waiting for the barman-wal-archive process to finish with hope that the backup will finish as well. Currently the backup is stuck because: INFO: HINT: Check that your archive_command is executing properly. You can safely cancel this backup, but the database backup will not be usable without all the WAL segments. — user22188956, Aug 04 '23 at 14:24
Also I can see the latest WAL files in the wals directory (barman_dir/server_name/wals) if that can serve for any confirmation of your question @Gab — user22188956, Aug 04 '23 at 14:27
I don't know barman archive but as far as I undertstand it uses the standard barman user and not the replication one. Imho your replication was not effective and the replication slot was so preventing prostgres to get rid of wal — Gab, Aug 04 '23 at 14:30
I'm confident the replication slot was and is receiving all wals. As I said I already checked if the replication slot is lagging behind, and it wasn't. But your understanding is correct, I have only 1 barman user. Which is super user. Shall I have two? @Gab — user22188956, Aug 04 '23 at 14:32
ah sure there should be 2 barman user afaik, one admin for the dump (barman) and one for replication (streaming_barman) with replication privilege. you can test replication from barman host using sudo -u barman psql -U streaming_barman -h $yourDbIp -c "IDENTIFY_SYSTEM" replication=1 — Gab, Aug 04 '23 at 14:40
I will test with two users, without any archival and report the result back for search-engine comers. Any idea why having single user may cause this problem? — user22188956, Aug 04 '23 at 14:42
well I suppose you could use the same user for both backup and wal streaming as long as an admin user can have replication privilege. — Gab, Aug 04 '23 at 14:49
well, in fact my setup use ssh based backup so You man only need the replication user in your case — Gab, Aug 04 '23 at 14:52

Gab · Accepted Answer · 2023-08-04T14:54:24.093

wal streaming require a barman user with replication privilege :

below is the related configuration file part

; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; PostgreSQL streaming connection string
; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; To be used by pg_basebackup for backup and pg_receivewal for WAL streaming
; NOTE: streaming_barman is a regular user with REPLICATION privilege
streaming_conninfo = host=$DataBaseHost user=streaming_barman

; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; WAL streaming settings (via pg_receivewal)
; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
streaming_archiver = on
slot_name = barman
create_slot = auto
;streaming_archiver_name = barman_receive_wal
;streaming_archiver_batch_size = 50

you can create the related user in postgres using :

sudo -u postgres createuser -P --replication streaming_barman

and test the replication from barman host using

psql -U streaming_barman -h $yourDbIp -c "IDENTIFY_SYSTEM" replication=1

after having reported the related user password in a local .pgpass file.

PostgreSQL not deleting old WAL archives

1 Answers1