Re: pg_dumpall from a script

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
do
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(at)gmail(dot)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(dot)sewell(at)lisasoft(dot)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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)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.
>>
>>
>