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 <htf...@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.sew...@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.kla...@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.kla...@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. >> >> >
