PostgreSQL not deleting old WAL archives

August 4, 2023

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?

>Solution :

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.