pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedInitiate backup from routine?
7+ messages / 6 participants
[nested] [flat]
* Initiate backup from routine?
@ 2024-01-17 14:40 Troels Arvin <troels@arvin.dk>
2024-01-17 14:49 ` Re: Initiate backup from routine? David G. Johnston <david.g.johnston@gmail.com>
2024-01-17 16:24 ` Re: Initiate backup from routine? Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-17 18:17 ` RE: Initiate backup from routine? Kamil ADEM <kamila@aqvilasoftware.com>
0 siblings, 3 replies; 7+ messages in thread
From: Troels Arvin @ 2024-01-17 14:40 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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?
--
Regards,
Troels Arvin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Initiate backup from routine?
2024-01-17 14:40 Initiate backup from routine? Troels Arvin <troels@arvin.dk>
@ 2024-01-17 14:49 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-17 15:09 ` Re: Initiate backup from routine? Troels Arvin <troels@arvin.dk>
2 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-01-17 14:49 UTC (permalink / raw)
To: Troels Arvin <troels@arvin.dk>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wednesday, January 17, 2024, Troels Arvin <troels@arvin.dk> wrote:
>
> Is it possible to call pg_dump (or equivalent action) through a
> procedure/function?
>
Are you able to install an untrusted language handler into the database?
They are untrusted because they can basically get shell on the server.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Initiate backup from routine?
2024-01-17 14:40 Initiate backup from routine? Troels Arvin <troels@arvin.dk>
2024-01-17 14:49 ` Re: Initiate backup from routine? David G. Johnston <david.g.johnston@gmail.com>
@ 2024-01-17 15:09 ` Troels Arvin <troels@arvin.dk>
0 siblings, 0 replies; 7+ messages in thread
From: Troels Arvin @ 2024-01-17 15:09 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hello,
David wrote:
> / Are you able to install an untrusted language handler into the
> database?/
Yes, if need be.
--
Regards,
Troels Arvin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Initiate backup from routine?
2024-01-17 14:40 Initiate backup from routine? Troels Arvin <troels@arvin.dk>
@ 2024-01-17 16:24 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-18 16:23 ` Re: Initiate backup from routine? Stephen Frost <sfrost@snowman.net>
2 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2024-01-17 16:24 UTC (permalink / raw)
To: pgsql-general
On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin <troels@arvin.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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Initiate backup from routine?
2024-01-17 14:40 Initiate backup from routine? Troels Arvin <troels@arvin.dk>
2024-01-17 16:24 ` Re: Initiate backup from routine? Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-01-18 16:23 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 7+ messages in thread
From: Stephen Frost @ 2024-01-18 16:23 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Initiate backup from routine?
2024-01-17 14:40 Initiate backup from routine? Troels Arvin <troels@arvin.dk>
@ 2024-01-17 18:17 ` Kamil ADEM <kamila@aqvilasoftware.com>
2 siblings, 0 replies; 7+ messages in thread
From: Kamil ADEM @ 2024-01-17 18:17 UTC (permalink / raw)
To: Troels Arvin <troels@arvin.dk>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hello Troels,
You can use the following statements in a procedure/function:
CREATE TEMPORARY TABLE tt_cmdout(cmdoutput text);
COPY tt_cmdout(cmdoutput) FROM PROGRAM 'pg_dump.exe <parameters>';
But note that the user must have SUPERUSER or BYPASSRLS privileges or must be table owner.
Ragards,
Kamil Adem
From: Troels Arvin <troels@arvin.dk>
Sent: Wednesday, January 17, 2024 5:41 PM
To: pgsql-general@lists.postgresql.org
Subject: Initiate backup from routine?
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?
--
Regards,
Troels Arvin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Initiate backup from routine?
@ 2024-01-17 16:15 Pyrote <pyromania@guungle.com>
0 siblings, 0 replies; 7+ messages in thread
From: Pyrote @ 2024-01-17 16:15 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Wednesday, January 17, 2024, Troels Arvin <troels@arvin.dk> wrote:
> Is it possible to call pg_dump (or equivalent action) through a
> procedure/function?
You could create a new schema and table that holds a flag column or a
simple queue.
Then setup a script/app on a 5 minute cron that checks the status of the
flag or queue to see if it needs to start a backup.
The script/app could update the table to show the user that the backup
has started and when its finished.
The user account on the DB server that runs the code would have their
shell set to /bin/false so they cannot login. The DB account used by the
code would have the minimum privileges to perform the backup.
Or if it needs to be more real-time, the app could use LISTEN/NOTIFY to
trigger the backup process. Then use cron on some interval to make sure
the app is always running.
Then you just need the procedure to update the flag/queue or send a
NOTIFY message.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-01-18 16:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-17 14:40 Initiate backup from routine? Troels Arvin <troels@arvin.dk>
2024-01-17 14:49 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-17 15:09 ` Troels Arvin <troels@arvin.dk>
2024-01-17 16:24 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-18 16:23 ` Stephen Frost <sfrost@snowman.net>
2024-01-17 18:17 ` Kamil ADEM <kamila@aqvilasoftware.com>
2024-01-17 16:15 Re: Initiate backup from routine? Pyrote <pyromania@guungle.com>
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