Re: Initiate backup from routine?
Greetings, * Ron Johnson (ronljohnso...@gmail.com) wrote: > On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin 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 signature.asc Description: PGP signature
RE: Initiate backup from routine?
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 '; But note that the user must have SUPERUSER or BYPASSRLS privileges or must be table owner. Ragards, Kamil Adem From: Troels Arvin 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
Re: Initiate backup from routine?
On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin 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.
Re: Initiate backup from routine?
On Wednesday, January 17, 2024, Troels Arvin 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.
Re: Initiate backup from routine?
Hello, David wrote: / Are you able to install an untrusted language handler into the database?/ Yes, if need be. -- Regards, Troels Arvin
Re: Initiate backup from routine?
On Wednesday, January 17, 2024, Troels Arvin 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.
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
