Greetings, * Ron Johnson (ronljohnsonjr@xxxxxxxxx) wrote: > On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin <troels@xxxxxxxx> wrote: > > 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. Strongly encourage this, of course. > CHECKPOINT; and SELECT pg_switch_wal(); are all that's needed before she > deploys the update. Why force a checkpoint here? It's not necessary and it's expensive. I would suggest making use of pg_create_restore_point() so that you can restore to exactly the point you want to. Documentation for that is here: https://www.postgresql.org/docs/current/functions-admin.html Using pg_switch_wal() will make the WAL get pushed to the repo faster than it would otherwise though that's not strictly necessary either unless you're just outright killing the PG instance; a normal shutdown should push that WAL out anyway. Thanks, Stephen
Attachment:
signature.asc
Description: PGP signature
