pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephen Frost <sfrost@snowman.net>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Initiate backup from routine?
Date: Thu, 18 Jan 2024 11:23:56 -0500
Message-ID: <ZalQnD7Q3/r04Qzd@tamriel.snowman.net> (raw)
In-Reply-To: <CANzqJaC5+k2H3JGVvtLTSs+i3oF2jyRTQk154MsFQRKyydHdHA@mail.gmail.com>
References: <409843b2-54ec-434f-a173-57c207744dda@arvin.dk>
	<CANzqJaC5+k2H3JGVvtLTSs+i3oF2jyRTQk154MsFQRKyydHdHA@mail.gmail.com>

Greetings,

* Ron Johnson (ronljohnsonjr@gmail.com) wrote:
> On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin <troels@arvin.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

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (7+ messages)

Message-ID: <ZalQnD7Q3/r04Qzd@tamriel.snowman.net>
Permalink:  ../ZalQnD7Q3%2Fr04Qzd@tamriel.snowman.net/
Also on:    postgresql.org/message-id/ZalQnD7Q3/r04Qzd@tamriel.snowman.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: sfrost@snowman.net, ronljohnsonjr@gmail.com
  Subject: Re: Initiate backup from routine?
  In-Reply-To: <ZalQnD7Q3/r04Qzd@tamriel.snowman.net>
  References: <409843b2-54ec-434f-a173-57c207744dda@arvin.dk> <CANzqJaC5+k2H3JGVvtLTSs+i3oF2jyRTQk154MsFQRKyydHdHA@mail.gmail.com> <ZalQnD7Q3/r04Qzd@tamriel.snowman.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox