Re: WAL Archive Cleanup?

On Fri, Mar 22, 2019 at 6:28 AM Foo Bar <qubitrenegade(at)gmail(dot)com> wrote:

>
> Fast forward two weeks, this cluster has been running but not seeing any
> traffic. And my master server has filled its archive directory.
>

Are you sure it is the archive directory (/hab/svc/postgresql/data/archive)
which is filling up, and not the live directory (pg_wal or pg_xlog)? This
is often a point of confusion.

> I found an older thread
> <https://www.postgresql.org/message-id/417C5AF7C228B94490192951394BEFE7B4805F@AIPHLEXDAG01B.airgas.com>
> that seemed to indicate that the wal_keep_segments is what was causing
> psql to keep so many WAL files... However, mine is set to 8, but there
> were thousands of log files...
>

wal_keep_segments applies to the live directory (pg_xlog or pg_wal), not
the archive directory.

> This SO thread
> <https://stackoverflow.com/questions/21113210/archive-cleanup-command-when-is-called>
> seems to indicate that archive_cleanup_command can be run every "restart
> point", but googling "psql restart point" brings me to this page
> <https://www.postgresql.org/docs/9.1/continuous-archiving.html> which a
> ^F indicates does not mention a "restart point"... So this *feels* like
> the configuration setting I want, but I can't find the documentation that
> confirms it for me...
>

"psql" is the name of a specific command line tool used to connect to a
PostgreSQL database server, it is not the name of the database itself. The
database is usually abbreviated "pgsql". And "restartpoint" is usually
spelled as one work in technical discussions of it. Or at least, searching
for it that way avoids finding things which mention each word separately in
different senses.

A restartpoint is just a checkpoint which is run on the replica (as opposed
to a checkpoint proper, which runs on the master). And
archive_cleanup_command is executed on the replica, not on the master, so
to use it the replica has to have write access on the archive directory to
implement it.

archive_cleanup_command is pretty much obsolete. The modern way to do this
is with streaming replication, using either replication slots or
wal_keep_segments. If the only reason you want an archive is for
replication, then use streaming replication and do away with the archive
completely. There are reasons other than replication that one might want
to keep a WAL archive, but those reasons don't seem to apply to you. And
if they did you almost certainly wouldn't want to run
archive_cleanup_command on it.

Cheers,

Jeff

>