Re: pg_dumpall from a script

[Raghu Ram <raghuchennuru@xxxxxxxxx>]

On Tue, Oct 22, 2013 at 10:50 AM, James Sewell <james.sewell@xxxxxxxxxxxx> 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! 

You can use a stored procedure with this plsh http://plsh.projects.postgresql.org/ , like this: 

CREATE FUNCTION dump_db(text, text) RETURNS text AS ' 

#!/bin/sh 

pg_dump $1 > $2 

' LANGUAGE plsh; 

Note that you must CREATE LANGUAGE first, $1 is db_name, $2 is file name and check for write permissions of $2.

Thanks & Regards

Raghu Ram