0

I'd like to know if there is a way to call a flat file (*.sh) from a procedure/function in a postgres database. Basically when a certain table gets a row inserted it should call the .sh script to run pg_dump to take a backup of this table and import it to another database.

Thanks.

shawnk79
  • 1
  • 2
  • Only in one of the untrusted languages: [plpython3u](https://www.postgresql.org/docs/current/plpython.html) or the untrusted variant of [plperlu](https://www.postgresql.org/docs/current/plperl-trusted.html). Another option would be to set up [Logical replication](https://www.postgresql.org/docs/current/logical-replication.html) on that table between the databases. – Adrian Klaver Aug 03 '22 at 18:12
  • is there an example of a trigger that can send email every time there's an insert or update done on the table? – shawnk79 Aug 03 '22 at 18:48
  • I would strongly advice against this, but Yes, it is possible: https://stackoverflow.com/questions/12243335/pl-perl-send-mail-in-postgresql – Frank Heikens Aug 03 '22 at 18:49
  • Why would you want to do this? It seems to would be better to have the trigger send the data to another table in the same database and then periodically in a batch move that data over. – Adrian Klaver Aug 03 '22 at 18:53
  • The application that inserts this specific row, should know that it should also take a backup. I would not push this logic to the database because you need applications outside of the database for this job. – Frank Heikens Aug 03 '22 at 19:13
  • @AdrianKlaver are you suggesting to use dblink inside the trigger/procedure to copy the data to another database? – shawnk79 Aug 03 '22 at 20:02
  • 1
    You could do that, though I would use [postgres_fdw](https://www.postgresql.org/docs/current/postgres-fdw.html). I was thinking of secondary table on the source database that would hold the changes and from which you could harvest data in batches. – Adrian Klaver Aug 03 '22 at 20:06
  • thank you @AdrianKlaver for your quick response. i will try fdw – shawnk79 Aug 03 '22 at 20:32

0 Answers0