Thread

1. PgBackRest and WAL archive expiry

   KK CHN <kkchn.in@gmail.com> — 2024-09-20T05:46:10Z

   List,
   
   I successfully configured pgbackrest (pgBackRest 2.52.1) on RHEL 9.4 with
   EPAS 16.1 for a couple of production servers and a Remote Repo Server.
   
   Seems everything is working as expected.
   
   I have a serious concern of   archive dir growing day by day..
   
   1. In the  EPAS server    I have   postgres.conf with
   archive_command = 'pgbackrest --stanza=EMI_Repo archive-push %p && cp %p
    /data/archive/%f'
   
   The problem is that the   /data/archive  folder is growing  within a few
   days to 850GB  of 2 TB  partition.
   
    What is the mechanism to check / expire the WAL  archive dir automatically?
    How others control this behavior and on what criteria so that PITR  won't
   be badly affected   if we manually delete the WALs from the archive dir ?
   
   Does  Postgres  or PgBackRest have any command/directive to control the
   /data/archive  growth after a considerable  time/usage of disk space
   without affecting PITR (or on any condition ) ?
   
   Please shed your expertise to enlighten in this regard for a healthy WAL
   retention on the DB server as well as on the RepoServer
   
   Thank you,
   Krishane
   
   
   For any more inputs from DB server ..
   
   161G    ./edb/as16/tablespace/ERSS
   161G    ./edb/as16/tablespace
   167G    ./edb/as16
   167G    ./edb
   854G    ./archive
   229M    ./backup
   1.1T    .
   [root@db1 data]# cd /data/archive/
   [root@db1 archive]# du -h
   854G    .
   [root@db1 archive]#
   
   
   
   [root@db1 archive]# df -h
   Filesystem                    Size  Used Avail Use% Mounted on
   devtmpfs                      4.0M     0  4.0M   0% /dev
   tmpfs                         7.7G   11M  7.7G   1% /dev/shm
   tmpfs                         3.1G   28M  3.1G   1% /run
   /dev/mapper/rhel_bc68-root   20G  6.6G   14G  33% /
   /dev/mapper/rhel_bc68-app   5.0G   68M  4.9G   2% /app
   
   */dev/mapper/rhel_bc68-data  2.0T  1.1T  979G  52% /data*/dev/sda2
               960M  372M  589M  39% /boot
   /dev/sda1                     599M  7.1M  592M   2% /boot/efi
   tmpfs                         1.6G   52K  1.6G   1% /run/user/42
   tmpfs                         1.6G   36K  1.6G   1% /run/user/0
   [root@db1 archive]#
   
   
                                           # (change requires restart)
   archive_mode = on               # enables archiving; off, on, or always
                                   # (change requires restart)
                                   # (empty string indicates archive_command
   should
                                   # be used)
                                   #
   archive_command = 'pgbackrest --stanza=EMI_Repo archive-push %p && cp %p
    /data/archive/%f'
                                   # placeholders: %p = path of file to archive
                                   #               %f = file name only
   
   
   
   
   
   *[root@db1 pg_wal]# du -h20K     ./archive_status5.1G    .[root@db1
   pg_wal]#*
   

2. Re: PgBackRest and WAL archive expiry

   Christophe Pettus <xof@thebuild.com> — 2024-09-20T05:52:32Z

   
   > On Sep 19, 2024, at 22:46, KK CHN <kkchn.in@gmail.com> wrote:
   > 
   > 1. In the  EPAS server    I have   postgres.conf with 
   > archive_command = 'pgbackrest --stanza=EMI_Repo archive-push %p && cp %p  /data/archive/%f'
   > 
   > The problem is that the   /data/archive  folder is growing  within a few days to 850GB  of 2 TB  partition.
   
   The /data/archive directory is entirely under your control.  pgbackrest and PostgreSQL don't manage them in any way.  It will just keep growing indefinitely unless you take action to delete the WAL segments out of it.
   
   There's no real benefit in maintaining that separate /data/archive directory; pgbackrest archives and manages the lifecycle of the WAL segments in its repository.  I wouldn't bother with that separate archive, and just use pgbackrest.