Search Postgresql Archives

Re: pg_dumpall from a script

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx>
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@xxxxxxxxxxxx> 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

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


On Wed, Oct 23, 2013 at 11:48 AM, Adrian Klaver <adrian.klaver@xxxxxxxxx> 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@xxxxxxxxx



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.




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux