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 <parameters>';

But note that the user must have SUPERUSER or BYPASSRLS privileges or must be table owner.

Ragards,
Kamil Adem

From: Troels Arvin <troels(at)arvin(dot)dk>
Sent: Wednesday, January 17, 2024 5:41 PM
To: pgsql-general(at)lists(dot)postgresql(dot)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