Re: pg_dumpall from a script - Mailing list pgsql-general
Hello All,
Thanks for the replies.Sorry I must have been a bit unclear, I realise I *could* do this from the shell level, but can I do it from a PSQL session somehow?
I think the answer is no (I assume unless I write my own C function or similar).
It seems there is no way of passing a :variable to the \! command, and PL/PGSQL is a trusted language.
I might investigate PL/PythonU, would the untrusted mean I might be able to do this sort of thing (run arbitrary commands?)
If not, I'll just make a wrapper and do the backup there.
Cheers,
James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________
On Wed, Oct 23, 2013 at 3:24 AM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
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
The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
pgsql-general by date: