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