Re: WAL Archiving and base backup - Mailing list pgsql-general

Greetings,

* David G. Johnston (david.g.johnston@gmail.com) wrote:
> On Tue, Jan 18, 2022 at 10:53 AM Stephen Frost <sfrost@snowman.net> wrote:
> > We already have pg_receivewal, which is part of pg_basebackup, and is
> > able to use a slot and such.  I'm not sure that making pg_basebackup
> > somehow also work as an archive command makes much sense
>
> I suppose my proposal should have been:
>
> Create and document a new "PostgreSQL Server Application" [1] and name it:
> pg_archive_wal
> Advise people to set their archive_command to "pg_archive_wal
> --path=/location/of/archive %p/%f
>
> 1. https://www.postgresql.org/docs/current/reference-server.html

Or just link to a proper solution that already exists and includes
support for object stores, multiple repositories, checksums all files
and stores them in a manifest to validate them ...

> Having pg_basebackup still prompt for permission to add that command to the
> system via ALTER SYSTEM (probably will some other logic) seems doable.

It doesn't seem to follow, to me anyway, that if you're using
pg_basebackup that you'd want to use this proposed pg_archive_wal.  Much
more likely would be that you'd want to use pg_receivewal, which doesn't
need any archive command to be set.  Seems like a feature in need of a
use case is what I'm getting at here.

> Having created a base backup one still must decide on a wal archiving
> strategy.  There appear to be two options, though as far as I can tell if
> one simply reads the documentation regarding backups they will not discover
> the pg_receivewal option.  I, not knowing of that option myself, have been
> operating under the assumption that if one uses pg_basebackup that one
> would be required to setup an archive_command as well.

Adding a reference to pg_receivewal under the continuous archiving
section would probably make sense, similar to how pg_basebackup is
referenced from the base backup section.  Might even make sense to have
a 'low level API' section for WAL archiving which mentions
archive_command ... or maybe not and rip out the existing 'low level
API' section as it really isn't nearly detailed enough for someone to be
able to write a proper tool and having it there implies that it does
provide that.

> The superior option is having a persistently running pg_receivewal command
> on a server.  As noted above, the documentation does not do this option
> justice.

Not sure that I'd say that it's the superior option, but it depends on
the options that are being considered and if you're limiting those to
"just what exists in core."

> The alternative option is to set archive_command; which at present is also
> poorly documented.  My proposal above simply tries to improve on this.  And
> while that is a good and easy starting point if there is agreement on
> pg_receivewal being a superior archiving option (leaving archive_command
> unset) reworking the documentation to guide the inexperience PostgreSQL DBA
> toward a "minimal but effective" backup procedure is needed.

Adding a new PG server application isn't exactly what I'd call just a
simple improvement to the documentation ...

The existing documentation for base backups does, in fact, link to
pg_basebackup to guide the new DBA to a solution for base backups that's
minimal but effective.  Doing PITR is getting beyond just the minimal,
but even so, linking to pg_receivewal in a similar manner probably does
make sense since we link to pg_basebackup, so I think I agree with you
about that specific change.  Hopefully, we can remove the long
deprecated exclusive backup option and further simplify the backup
documentation.

Thanks,

Stephen