Home > mailing lists

Thread: pg_dumpall from a script

pg_dumpall from a script

From

James Sewell

Date:

22 October 2013, 05:37:32

Hello,

I need to trigger a database dump from a SQL script (or function, but I think that is even less likely).

I know I can do:

\! pg_dumpall > /mydir/myfile

Which is fine, but I need to use a variable to set the name.

\set myfile 'filename'

\! pg_dumpall > /mydir/:myfile

Doesn't seem to work.

Any ideas?

James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________

Level 2, 50 Queen St, Melbourne VIC 3000

P (+61) 3 8370 8000 W www.lisasoft.com F (+61) 3 8370 8099

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.

Re: pg_dumpall from a script

From

Raghu Ram

Date:

22 October 2013, 08:05:55

On Tue, Oct 22, 2013 at 8:07 AM, James Sewell <james.sewell@lisasoft.com> wrote:

Hello,

I need to trigger a database dump from a SQL script (or function, but I think that is even less likely).

I know I can do:

\! pg_dumpall > /mydir/myfile

Which is fine, but I need to use a variable to set the name.

\set myfile 'filename'
\! pg_dumpall > /mydir/:myfile

Doesn't seem to work.

Any ideas?

Below URL provides more information of Automated database Backup's on Linux:

http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux

Thanks & Regards

Raghu Ram

Re: pg_dumpall from a script

From

James Sewell

Date:

22 October 2013, 08:20:08

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!

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________

Level 2, 50 Queen St, Melbourne VIC 3000

P (+61) 3 8370 8000 W www.lisasoft.com F (+61) 3 8370 8099

On Tue, Oct 22, 2013 at 4:05 PM, Raghu Ram <raghuchennuru@gmail.com> wrote:

On Tue, Oct 22, 2013 at 8:07 AM, James Sewell <james.sewell@lisasoft.com> wrote:
Hello,

I need to trigger a database dump from a SQL script (or function, but I think that is even less likely).

I know I can do:

\! pg_dumpall > /mydir/myfile

Which is fine, but I need to use a variable to set the name.

\set myfile 'filename'
\! pg_dumpall > /mydir/:myfile

Doesn't seem to work.

Any ideas?

Below URL provides more information of Automated database Backup's on Linux:

http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux

Thanks & Regards
Raghu Ram

Re: pg_dumpall from a script

From

BladeOfLight16

Date:

22 October 2013, 09:08:29

On Tue, Oct 22, 2013 at 1:20 AM, James Sewell <james.sewell@lisasoft.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).

I don't understand. To use psql, you would have to launch it with a command line, as far as I know. If psql is a viable option for you, then you should be able to execute other commands as well. That would make a bash script a viable option. How do you intend to launch psql?

Re: pg_dumpall from a script

From

Luca Ferrari

Date:

22 October 2013, 09:41:41

On Tue, Oct 22, 2013 at 7:20 AM, James Sewell <james.sewell@lisasoft.com> wrote:

> 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
froma :variable. 

Could it be something like this:

# \setenv myFile 'filename'
# \! pg_dump > $myFile

Unfortunately there seems to be no unsetenv command to remove the
variable within the script.


Luca

Re: pg_dumpall from a script

From

Adrian Klaver

Date:

22 October 2013, 16:52:13

On 10/21/2013 11:41 PM, Luca Ferrari wrote:
> On Tue, Oct 22, 2013 at 7:20 AM, James Sewell <james.sewell@lisasoft.com> wrote:
>
>> 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
froma :variable. 
>
> Could it be something like this:
>
> # \setenv myFile 'filename'
> # \! pg_dump > $myFile
>
> Unfortunately there seems to be no unsetenv command to remove the
> variable within the script.

Call it again without the variable value:

http://www.postgresql.org/docs/9.3/interactive/app-psql.html

\setenv [ name [ value ] ]
Sets the environment variable name to value, or if the value is not
supplied, unsets the environment variable

\setenv myFile

>
>
> Luca
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Re: pg_dumpall from a script

From

Steve Crawford

Date:

22 October 2013, 19:24:42

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

Re: pg_dumpall from a script

From

James Sewell

Date:

23 October 2013, 01:41:24

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
______________________________________

Level 2, 50 Queen St, Melbourne VIC 3000

P (+61) 3 8370 8000 W www.lisasoft.com F (+61) 3 8370 8099

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

Re: pg_dumpall from a script

From

Adrian Klaver

Date:

23 October 2013, 03:48:43

On 10/22/2013 03:41 PM, James Sewell wrote:
> 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?

Lucas' \setenv method won't work for you?


> Cheers,
>
>
> James Sewell,
> PostgreSQL Team Lead / Solutions Architect


--
Adrian Klaver
adrian.klaver@gmail.com

Re: pg_dumpall from a script

From

James Sewell

Date:

23 October 2013, 04:19:56

Oh I missed that, I skimmed and thought it was the same as \set

Turns out it's not and it's exactly what I want!

Thanks!

James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________

Level 2, 50 Queen St, Melbourne VIC 3000

P (+61) 3 8370 8000 W www.lisasoft.com F (+61) 3 8370 8099

On Wed, Oct 23, 2013 at 11:48 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 10/22/2013 03:41 PM, James Sewell wrote:
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?

Lucas' \setenv method won't work for you?

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect

--
Adrian Klaver
adrian.klaver@gmail.com

Re: pg_dumpall from a script

From

Michael Nolan

Date:

23 October 2013, 17:09:00

You could write a plperlul function that runs a shell script to back up your database, you can even pass it parameters and put a call to that in a trigger.

BUT, this could result in multiple backups running at the same time and become a performance drag.
--

Mike Nolan

On Tue, Oct 22, 2013 at 9:19 PM, James Sewell <james.sewell@lisasoft.com> wrote:

Oh I missed that, I skimmed and thought it was the same as \set

Turns out it's not and it's exactly what I want!

Thanks!

James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________

Level 2, 50 Queen St, Melbourne VIC 3000

P (+61) 3 8370 8000 W www.lisasoft.com F (+61) 3 8370 8099

On Wed, Oct 23, 2013 at 11:48 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 10/22/2013 03:41 PM, James Sewell wrote:
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?

Lucas' \setenv method won't work for you?

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect

--
Adrian Klaver
adrian.klaver@gmail.com

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.

Re: pg_dumpall from a script

From

Oscar Calderon

Date:

23 October 2013, 21:02:26

Hi James, i wanna share with you a script that i use, i scheduled it with crontab. It generates a backup for each database. After that, there is another script scheduled that copies the backups to another server.

#! /bin/bash

TIEMPO=$(date '+%d%m%Y_%H%M%S')

UBICACION="/var/lib/pgsql/9.3/backups_auth/bkp_"

BASES=$(psql -h localhost -t -U postgres -c "SELECT

datname from pg_database WHERE datistemplate = false

AND datname <> 'postgres' ")

BASES='\n' read -a array <<< $BASES

for CURRDB in $BASES

pg_dump -h localhost -p 5432 -U postgres -Fc -Z0 -C -d $CURRDB > "$UBICACION$CURRDB-$TIEMPO.backup"

# cp "$UBICACION$CURRDB-$TIEMPO.backup" /some/where/else

done

As you can see i query the list of the databases, but you can choose another method to get the list of the databases, but if you are backing up a single database then just use this line

pg_dump -h localhost -p 5432 -U postgres -Fc -Z0 -C -d your_database > "/path/to/backup/file.backup"

Then schedule it:

nano /etc/crontab

30 23 * * * postgres /path/to/my/script.sh

***************************
Oscar Calderon
Analista de Sistemas
Soluciones Aplicativas S.A. de C.V.
www.solucionesaplicativas.com
Cel. (503) 7741 7850 Tel. (503) 2522-2834

2013/10/23 Michael Nolan <htfoot@gmail.com>

You could write a plperlul function that runs a shell script to back up your database, you can even pass it parameters and put a call to that in a trigger.

BUT, this could result in multiple backups running at the same time and become a performance drag.
--
Mike Nolan

On Tue, Oct 22, 2013 at 9:19 PM, James Sewell <james.sewell@lisasoft.com> wrote:
Oh I missed that, I skimmed and thought it was the same as \set

Turns out it's not and it's exactly what I want!

Thanks!

James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________

Level 2, 50 Queen St, Melbourne VIC 3000

P (+61) 3 8370 8000 W www.lisasoft.com F (+61) 3 8370 8099

On Wed, Oct 23, 2013 at 11:48 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 10/22/2013 03:41 PM, James Sewell wrote:
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?

Lucas' \setenv method won't work for you?

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect

--
Adrian Klaver
adrian.klaver@gmail.com

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.

Re: pg_dumpall from a script

From

Raghu Ram

Date:

24 October 2013, 08:38:55

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