Re: pg_dumpall from a script

On 10/21/2013 10:20 PM, James Sewell wrote:

That looks great, but it doesn't really help with my problem unless I'm missing something (very possible!)

I need a way to backup either from SQL in PSQL (possibly \!) or from a PG/PLSQL function to a file with a name set from a :variable.

This would be triggered by a certain action in the database (applying a patch).

Hope that's a bit clearer!

...

Perhaps a better description of the problem you want to solve would help. Are backups running on the same machine as the server? How soon after the triggering event must the backup be run? If there is a subsequent triggering event that occurs before the backup starts on the first event will there be ill effects? Is there a method in place to prevent subsequent events prior to the backup completing on the first event? Does the test have to be done in psql for some reason or are other clients acceptable?

If you are running the backup from within psql then something started psql. I assume you aren't trying to have a constantly connected psql script running a loop or using listen/notify. If you can test for your triggering event via a query then you can use a simple bash script to create a temporary file based on your query then test that file to determine if a backup is required. Run via cron every minute and you're done.

If you really need relatively instant response and especially if you can use a different client-side program then I suppose you might be able to cook up something with listen/notify or advisory locks.

Alternately, you might be able to use a foreign-data-wrapper that would allow you to create/modify a file (external file-based table) when your event occurs. By watching that external file you can determine that a backup is required. A daemonized bash-script utilizing inotifytools can reilably sit and wait for a change to a file then immediately launch you backup script. Be careful that you have a mechanism to prevent too many overlapping backups.

Cheers,
Steve