Re: Initiate backup from routine?

On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin <troels(at)arvin(dot)dk> wrote:

> Hello,
>
> I would like to allow a co-worker to perform a backup of a database, such
> that the backup is saved to the database server itself. One use case is
> that (s)he would like an extra backup of a database, just before an
> application update is deployed. The co-worker doesn't have shell access on
> the DB server (so no sudo option), and we would like to allow this to
> happen without having to involve a DBA.
>
> Is it possible to call pg_dump (or equivalent action) through a
> procedure/function?
>
An alternative is continuous (aka PITR) backups using something like
PgBackRest. Weekly full backups, incremental backups on the other six
days, and WAL files that keep you up to date.

CHECKPOINT; and SELECT pg_switch_wal(); are all that's needed before she
deploys the update.