Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1srWSw-002M8S-Lv for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 05:46:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1srWSu-00G1AB-HR for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 05:46:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1srWSu-00G18d-1j for pgsql-general@lists.postgresql.org; Fri, 20 Sep 2024 05:46:01 +0000 Received: from mail-yw1-x112c.google.com ([2607:f8b0:4864:20::112c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1srWSs-0007f2-Oi for pgsql-general@postgresql.org; Fri, 20 Sep 2024 05:46:00 +0000 Received: by mail-yw1-x112c.google.com with SMTP id 00721157ae682-6dffe3fe4fcso158277b3.1 for ; Thu, 19 Sep 2024 22:45:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726811158; x=1727415958; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=hBx5dkdRs1ul15B5HAKrXSLiPj/kI554o5osh8mCILI=; b=NLkPneV4NjBdHfpY61+03C01Ltw4hMdXx2GSdiquFzQQkpP04yRMpoGy8alVg4pL/Y vFNgabcltQgOk5y/jT32sWZMX3AUSiZFag69WiEv7Q+1RVY4+e9KNDZQerNus0xQWuaV 4erU31rkvRrJoKshMd1fczAWztP+PzgAFalCVEgiidVFJKlQat5U3zq0PcLivxgRECBJ X3grkA33XCRycE5SnOXcFIqG6xshCoz1dtp5NfdDosgEgOOMLJKNwhKg/ifb4J2y0mm+ B88V7CgV3Q8tf9d3zxT6ftNzXeYLFYgWIou7qiO9KE94G1gGE4HpIu+wC1PGyYwKk/z8 TAiA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726811158; x=1727415958; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=hBx5dkdRs1ul15B5HAKrXSLiPj/kI554o5osh8mCILI=; b=RAYudu+RwvAOmw+oga6Qoqu1a6yfUGNmunZ51P2/elRjDmfB8l9iXfe0LMzKZqLGnp L9q9RmBsuMSpNQFqYBM9W/oTWLY9+JzKSzaNdkE2+Y+d2ZAdmdyC3Qj59k036bfwPYyY W0WoLuzhhdHhDHfQsGMtUZnIXTrPf8Q5NMjyiiFeZ3J1EBuIZjut+uDhnQ+XDFO1Evxy KvRhAqegSHr2yfDzPOZ7HeggCE7di/Fg1xz8qnYBCcvxVOUCTyxZ5bMmNo9ySklEucfP d9oxoFGOILk4irqJEw+8slo5q20gtVHrKaNL8etPmXq56mC6Y1RKQ16xfq+NmkEBapwK wlDg== X-Gm-Message-State: AOJu0Yx0DGxnYOHYKBbDLnDJvpbbbuSZ27TpH+PyIHQt6LIVaYaKa8+Y MlGOaIQhhHVJKNGQ4RvQqxE0h22cYhKeEjFseFoyeCApTDKiUv+Y7M0b+yG1cRhLPhs2P0HGEra W2QxD5VSjrJ2Qa2opqdJCtmL9MUWTGCWU X-Google-Smtp-Source: AGHT+IHjJgmzSJryFabYxhfXOzp6Utzax4aYVsHDsOmuzuwBjQ27dOnPnrhxDnkLDKZEocfqHb+Ysey9+PYg+hjTZeU= X-Received: by 2002:a05:690c:f0a:b0:6dd:d34b:373f with SMTP id 00721157ae682-6dfeed222ebmr21346567b3.8.1726811157764; Thu, 19 Sep 2024 22:45:57 -0700 (PDT) MIME-Version: 1.0 From: KK CHN Date: Fri, 20 Sep 2024 11:16:10 +0530 Message-ID: Subject: PgBackRest and WAL archive expiry To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000a721e00622868ddc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a721e00622868ddc Content-Type: text/plain; charset="UTF-8" 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]#* --000000000000a721e00622868ddc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
List,

I successfully configured pgbackr= est (pgBackRest 2.52.1) on RHEL 9.4 with EPAS 16.1 for a couple of producti= on servers and a Remote Repo Server.

Seems everyth= ing is working as expected.=C2=A0

I have a serious= =C2=A0concern of=C2=A0 =C2=A0archive dir growing day by day..
1. In the=C2=A0 EPAS server=C2=A0 =C2=A0 I have=C2=A0 =C2=A0pos= tgres.conf with=C2=A0
archive_command =3D 'pgbackrest --stanz= a=3DEMI_Repo archive-push %p && cp %p =C2=A0/data/archive/%f'

The problem is that the=C2=A0 =C2=A0/data/archi= ve=C2=A0 folder is growing=C2=A0 within a few days to 850GB=C2=A0 of 2 TB= =C2=A0 partition.

=C2=A0What is the mechanism to c= heck / expire the WAL=C2=A0 archive dir automatically?
=C2=A0How = others control this behavior and on what criteria so that PITR=C2=A0 won= 9;t be badly affected=C2=A0 =C2=A0if we manually delete the WALs from the a= rchive dir ?=C2=A0

Does=C2=A0 Postgres=C2=A0 or Pg= BackRest=C2=A0have any command/directive to control the=C2=A0 =C2=A0 /data/= archive=C2=A0 growth=C2=A0after a considerable=C2=A0 time/usage of disk spa= ce without affecting PITR (or on any condition ) ?=C2=A0

Please shed your expertise to enlighten=C2=A0in this=C2=A0regard for= =C2=A0a healthy=C2=A0WAL retention on=C2=A0the DB server as well as on the = RepoServer=C2=A0

Thank you,
Krishane


For any more inputs from DB server ..<= /div>

161G =C2=A0 =C2=A0./edb/as16/tablespace/ERSS
16= 1G =C2=A0 =C2=A0./edb/as16/tablespace
167G =C2=A0 =C2=A0./edb/as16
16= 7G =C2=A0 =C2=A0./edb
854G =C2=A0 =C2=A0./archive
229M =C2=A0 =C2=A0.= /backup
1.1T =C2=A0 =C2=A0.
[root@db1 data]# cd /data/archive/
[ro= ot@db1 archive]# du -h
854G =C2=A0 =C2=A0.
[root@db1 archive]#


[root@db1 archive]# df -h
Filesystem =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Size =C2=A0Used Avail Use% Mou= nted on
devtmpfs =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A04.0M =C2=A0 =C2=A0 0 =C2=A04.0M =C2=A0 0% /dev
tmpf= s =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 7.7G =C2=A0 11M =C2=A07.7G =C2=A0 1% /dev/shm
tmpfs =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 3.1G =C2=A0 28M =C2=A03.1G =C2=A0 1% /run
/dev/mapper/rhel_bc68-root= =C2=A0 20G =C2=A06.6G =C2=A0 14G =C2=A033% /
/dev/mapper/rhel_bc68-app = =C2=A0 5.0G =C2=A0 68M =C2=A04.9G =C2=A0 2% /app
/dev/mapper/rhel_bc6= 8-data =C2=A02.0T =C2=A01.1T =C2=A0979G =C2=A052% /data
/dev/sda2 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 960M = =C2=A0372M =C2=A0589M =C2=A039% /boot
/dev/sda1 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 599M =C2=A07.1M =C2=A0592M = =C2=A0 2% /boot/efi
tmpfs =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1.6G =C2=A0 52K =C2=A01.6G =C2=A0 1%= /run/user/42
tmpfs =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1.6G =C2=A0 36K =C2=A01.6G =C2=A0 1% /run/u= ser/0
[root@db1 archive]#


=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # (change requires restart)
archive_mode= =3D on =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # enables archivin= g; off, on, or always
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # (change re= quires restart)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # (empty string ind= icates archive_command should
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # be = used)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 #
archive_command =3D '= ;pgbackrest --stanza=3DEMI_Repo archive-push %p && cp %p =C2=A0/dat= a/archive/%f'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # placeholders:= %p =3D path of file to archive
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 #= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 %f =3D file name only
=

[root@db1 pg_wal]# du -h
20K =C2=A0 =C2=A0 ./archive_status5.1G =C2=A0 =C2=A0.
[root@db1 pg_wal]#



<= /div>



--000000000000a721e00622868ddc--