From: | Stephen Frost <sfrost(at)snowman(dot)net> |
---|---|
To: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Initiate backup from routine? |
Date: | 2024-01-18 16:23:56 |
Message-ID: | ZalQnD7Q3/r04Qzd@tamriel.snowman.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Greetings,
* Ron Johnson (ronljohnsonjr(at)gmail(dot)com) wrote:
> On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin <troels(at)arvin(dot)dk> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Vanns | 2024-01-18 16:37:59 | Re: Tips on troubleshooting slow DELETE (suspect cascades) |
Previous Message | Tom Lane | 2024-01-18 15:51:48 | Re: undefined symbol when installing pgcrypto on 16.1 |