Thread

1. Archive logging not cleaning up pg_wal directory

   Justin Swanhart <greenlion@gmail.com> — 2025-04-10T12:28:36Z

   Hi,
   
   I have the following in my postgresql.conf for archive logging:
   archive_command='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 but
   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 out
   the `pg_wal` directory periodically (keep only 1 day of logs I guess?)
   
   I tried to use mv:
   archive_command='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?
   
   Regards,
   
   --Justin
   

2. Re: Archive logging not cleaning up pg_wal directory

   Laurenz Albe <laurenz.albe@cybertec.at> — 2025-04-10T12:48:00Z

   On Thu, 2025-04-10 at 08:28 -0400, Justin Swanhart wrote:
   > I have the following in my postgresql.conf for archive logging:
   > archive_command='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 but 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 out the `pg_wal` directory periodically (keep only 1 day of logs I guess?)
   > 
   > I tried to use mv:
   > archive_command='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?
   
   Something else is blocking WAL removal:
   https://www.cybertec-postgresql.com/en/why-does-my-pg_wal-keep-growing/
   
   Since you mention replication, my money is on a stale replication slot.
   
   Yours,
   Laurenz Albe
   
   
   
   

3. Re: Archive logging not cleaning up pg_wal directory

   Ron Johnson <ronljohnsonjr@gmail.com> — 2025-04-10T12:48:16Z

   On Thu, Apr 10, 2025 at 8:29 AM Justin Swanhart <greenlion@gmail.com> wrote:
   
   > Hi,
   >
   > I have the following in my postgresql.conf for archive logging:
   > archive_command='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 but
   > 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 out
   > the `pg_wal` directory periodically (keep only 1 day of logs I guess?)
   >
   
   No.  Definitely not.
   
   
   >
   > I tried to use mv:
   > archive_command='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?
   
   -- 
   Death to <Redacted>, and butter sauce.
   Don't boil me, I'm still alive.
   <Redacted> lobster!
   

4. Re: Archive logging not cleaning up pg_wal directory

   Justin Swanhart <greenlion@gmail.com> — 2025-04-10T13:12:52Z

   Thank you very much.  I had a stale replication slot.  I removed it and the
   logs were cleaned up immediately.
   
   --Justin
   
   On Thu, Apr 10, 2025 at 8:48 AM Laurenz Albe <laurenz.albe@cybertec.at>
   wrote:
   
   > On Thu, 2025-04-10 at 08:28 -0400, Justin Swanhart wrote:
   > > I have the following in my postgresql.conf for archive logging:
   > > archive_command='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
   > but 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 out the `pg_wal` directory periodically (keep only 1 day of
   > logs I guess?)
   > >
   > > I tried to use mv:
   > > archive_command='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?
   >
   > Something else is blocking WAL removal:
   > https://www.cybertec-postgresql.com/en/why-does-my-pg_wal-keep-growing/
   >
   > Since you mention replication, my money is on a stale replication slot.
   >
   > Yours,
   > Laurenz Albe
   >