MIME-Version: 1.0
References: <CAJM9iN3w3UdA3q4_Xnw+Pg+Nnxe4OAUjLQ7EgLtPe36y4A0HDA@mail.gmail.com>
In-Reply-To: <CAJM9iN3w3UdA3q4_Xnw+Pg+Nnxe4OAUjLQ7EgLtPe36y4A0HDA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 10 Apr 2025 08:48:16 -0400
Message-ID: <CANzqJaD0UAqHPtptbOg8KHN+Zc2VZzJpQeV0QZPqcXv_wsnJ1Q@mail.gmail.com>
Subject: Re: Archive logging not cleaning up pg_wal directory
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000096d33b06326c003d"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD0UAqHPtptbOg8KHN%2BZc2VZzJpQeV0QZPqcXv_wsnJ1Q%40mail.gmail.com>
Precedence: bulk

--00000000000096d33b06326c003d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Apr 10, 2025 at 8:29=E2=80=AFAM Justin Swanhart <greenlion@gmail.co=
m> wrote:

> Hi,
>
> I have the following in my postgresql.conf for archive logging:
> archive_command=3D'test ! -f /var/lib/postgresql/prod_archive_logs/%f && =
cp
> %p /var/lib/postgresql/prod_archive_logs/%f'
>
> This command is properly copying the log files to my archive directory bu=
t
> they remain in the `pg_wal` directory after they are copied, consuming
> hundreds of gigs of space.  Am I supposed to create a cron job to clean o=
ut
> the `pg_wal` directory periodically (keep only 1 day of logs I guess?)
>

No.  Definitely not.


>
> I tried to use mv:
> archive_command=3D'test ! -f /var/lib/postgresql/prod_archive_logs/%f && =
mv
> %p /var/lib/postgresql/prod_archive_logs/%f'
>
> When I did this the archives were moved away to the archive directory but
> when I tried to set up replication the server could not find the archived
> log to replicate from so I changed it to "cp" and now have this problem.
>
> What am I missing?
>

PG retains WAL files when it thinks it needs them.  "Orphaned replication
slot" is the typical reason.

Maybe you tried to set up replication at some point in the past, but
then forgot to drop the slot?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--00000000000096d33b06326c003d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Apr 10, 2025 at 8:29=E2=80=AFAM J=
ustin Swanhart &lt;<a href=3D"mailto:greenlion@gmail.com">greenlion@gmail.c=
om</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D=
"ltr"><div dir=3D"ltr">Hi,<div><br></div><div>I have the following in my po=
stgresql.conf for archive logging:<br>archive_command=3D&#39;test ! -f /var=
/lib/postgresql/prod_archive_logs/%f &amp;&amp; cp %p /var/lib/postgresql/p=
rod_archive_logs/%f&#39;</div><div><br></div><div>This command is properly =
copying the log files to my archive directory but they remain in the `pg_wa=
l` directory after they are copied, consuming hundreds of gigs of space.=C2=
=A0 Am I supposed to create a cron job to clean out the `pg_wal` directory =
periodically (keep only 1 day of logs I guess?)<br></div></div></div></div>=
</blockquote><div><br></div><div>No.=C2=A0 Definitely not.</div><div>=C2=A0=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><d=
iv dir=3D"ltr"><div dir=3D"ltr"><div><br></div><div>I tried to use mv:<br>a=
rchive_command=3D&#39;test ! -f /var/lib/postgresql/prod_archive_logs/%f &a=
mp;&amp; mv %p /var/lib/postgresql/prod_archive_logs/%f&#39;</div><div><br>=
</div><div>When I did this the archives were moved away to the archive dire=
ctory but when I tried to set up replication the server could not find the=
=C2=A0archived log to replicate from so I changed it to &quot;cp&quot; and =
now have this problem.</div><div><br></div><div>What am I missing?</div></d=
iv></div></div></blockquote><div><br></div><div></div></div><div>PG retains=
 WAL files when it thinks it needs them.=C2=A0 &quot;Orphaned replication s=
lot&quot; is the typical reason.</div><div><br></div><div>Maybe you tried t=
o set up replication at some point in the past, but then=C2=A0forgot to dro=
p the slot?</div><div><br></div><span class=3D"gmail_signature_prefix">-- <=
/span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death=
 to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m stil=
l alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></di=
v></div>

--00000000000096d33b06326c003d--