Re: pg_dumpall from a script

On Tue, Oct 22, 2013 at 10:50 AM, James Sewell <james(dot)sewell(at)lisasoft(dot)com>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