Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQVBZ-001hOg-33 for pgsql-general@arkaria.postgresql.org; Thu, 18 Jan 2024 16:24:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rQVBY-003wkA-4B for pgsql-general@arkaria.postgresql.org; Thu, 18 Jan 2024 16:24:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQVBX-003wk2-FS for pgsql-general@lists.postgresql.org; Thu, 18 Jan 2024 16:24:07 +0000 Received: from tamriel.snowman.net ([70.109.60.50]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQVBT-002ULi-Nq for pgsql-general@postgresql.org; Thu, 18 Jan 2024 16:24:06 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id D0C9E5F7B7; Thu, 18 Jan 2024 11:23:56 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1705595036; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=bNVMCQnGI/0oKnqv9ZsLOhVZozqC5kz1X19tGOpKpj0=; b=sXisWWSuJRhHnfVEY7yagMkaDFK/Un0E5SFvH3zuS4WJe+2qKztnpMiTdLPWKjdhPGOs0r NliqwCZr/pchyirQKrW557A2NW7L2kRTP48kNustFBpd6kLYOGxejDK2E1VOouFTybcE9m Fc3rCnDgcPtBq2GuHaSAgV72/fQ7ooI= Date: Thu, 18 Jan 2024 11:23:56 -0500 From: Stephen Frost To: Ron Johnson Cc: pgsql-general Subject: Re: Initiate backup from routine? Message-ID: References: <409843b2-54ec-434f-a173-57c207744dda@arvin.dk> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="QHPIf3YOCZRLgrI0" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --QHPIf3YOCZRLgrI0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Ron Johnson (ronljohnsonjr@gmail.com) wrote: > On Wed, Jan 17, 2024 at 9:41=E2=80=AFAM Troels Arvin wr= ote: > > I would like to allow a co-worker to perform a backup of a database, su= ch > > 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 --QHPIf3YOCZRLgrI0 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmWpUJkACgkQ7WyKOINH ZFVpBxAAtiRyOi1OQQiKUhxCrcw8/75CrKjZfF+8bzYtBs/0oxoDSnj3JXwyDNC1 /wzt5EAfjbNoW/YpuHwqRcg5CFjEdGheMJbi4KHMGKN4IDAkqxTueeJ443RWmMqW V/prTvqe7EDxHVoM9XTtC5dG1gVvkykY2sDImcwoK5yOshbY8SMOl3IrkAnPsSBw L2LwQyO3eJhfU6gvjfYIAp1hwNzkRXrLyhXBikttMq/yTWQv3b2W1a7osVTzziSc GIc39LsvJYFHQBzXRmwY3VBcru3bavmntiO7jIzE69MKZ+Vyd9HdddjtuBVtYif4 sgN71UFqXQL2OY9vdQUHTCI3qcpn3p2MkP4+grisMSOg6VvHEIEgfrNP2HfYOzoP kNxtD491O/A6h4B2zFXKB9hIjm4fk0gFY2NSjIDjAkvshH/LIqMsQ5Hdpfux1jCl OEg1A0soJ2fSGLLL8pLRluIerbXPEQLoixYJaf+QFyhTOvdXCqybYnf24W+kstR7 081MMupp+Xy50rXwKuj/q/PPxUG/nFksurJYAfmpiNaiZ2OPmT4a3827I5+RviyQ UFnWvDuY+kujoTNMAIG7sEGA6Vbi3/iN786qYT03ABShdt1onZGn3CogVIV9R6qq jXDgmt3k0KHm9hetFxOCFr+hDftNKBGmbJ31XZ+jzAE6aHgJoUA= =aLC+ -----END PGP SIGNATURE----- --QHPIf3YOCZRLgrI0--