PostgreSQL不删除旧的WAL归档。

huangapple go评论62阅读模式
英文:

PostgreSQL not deleting old WAL archives

问题

我有一个Postgresql 13数据库和Barman 2.7用于备份。Barman部署在一个单独的服务器上。Barman正在使用流式备份和通过pg_recievewal进行WAL流式传输。

然而,pg_wal目录变得异常庞大/未清理。

复制插槽报告barman已接收到最新的WAL,Barman的一侧没有显示任何问题。
我使用以下查询来查找Barman接收到的最后一个WAL:

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;

它报告的值与SELECT pg_walfile_name(pg_current_wal_lsn());报告的值相同。

启用了barman-wal-archive后,pg_wal目录开始清理。如果我理解正确,当使用pg_recievewal时,不是必须要有归档。

我的理解是否正确?如果是这样,为什么WAL归档会不断积累?

英文:

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?

答案1

得分: 1

"wal streaming require a barman user with replication privilege :"

以下是相关的配置文件部分:

; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; PostgreSQL流式连接字符串
; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; 用于pg_basebackup备份和pg_receivewal进行WAL流式传输
; 注意:streaming_barman是一个具有REPLICATION权限的常规用户
streaming_conninfo = host=$DataBaseHost user=streaming_barman

; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; WAL流式设置(通过pg_receivewal)
; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
streaming_archiver = on
slot_name = barman
create_slot = auto
;streaming_archiver_name = barman_receive_wal
;streaming_archiver_batch_size = 50

您可以使用以下命令在PostgreSQL中创建相关用户:

sudo -u postgres createuser -P --replication streaming_barman

并在barman主机上使用以下命令测试复制:

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

在本地的.pgpass文件中报告相关用户密码之后。

英文:

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.

huangapple
  • 本文由 发表于 2023年8月4日 22:00:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76836645.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定