Thread: split pg_dumpall backups per database

Hi all,
is it possible to instrument pg_dumpall to produce separate sql files for each
database it is going to backup? I'd like to keep separate backups of my
databases, but using pg_dump can lead to forgetting a database.....

Thanks,
Luca

Luca Ferrari wrote:
> Hi all,
> is it possible to instrument pg_dumpall to produce separate sql files for each
> database it is going to backup? I'd like to keep separate backups of my
> databases, but using pg_dump can lead to forgetting a database.....

You could build a shell script to repeatedly call pg_dump:

#!/bin/sh
DBLIST=`/path/to/psql -p 5483 -U postgres -d postgres -q -t -c 'SELECT
datname from pg_database'`
for d in $DBLIST
do
     echo "db = $d";
     pg_dump -U postgres -Fc $d > /path/to/backups/$d.dump
done


Two things to remember:
1. If you're going to have spaces or punctuation in the database names
you'll need to quote all the $d
2. You'll want to call pg_dumpall --globals-only to get all the user
details.

--
   Richard Huxton
   Archonet Ltd

On Tue, Mar 11, 2008 at 09:54:47AM +0100, Luca Ferrari wrote:
> Hi all,
> is it possible to instrument pg_dumpall to produce separate sql files for each
> database it is going to backup? I'd like to keep separate backups of my
> databases, but using pg_dump can lead to forgetting a database.....

psql -qAt -c 'select datname from pg_database where datallowconn' | xargs -r -I X pg_dump -C -f X.dump X

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

On 11/03/2008, Luca Ferrari <fluca1978@infinito.it> wrote:
> Hi all,
>  is it possible to instrument pg_dumpall to produce separate sql files for each
>  database it is going to backup? I'd like to keep separate backups of my
>  databases, but using pg_dump can lead to forgetting a database.....

You could use the method described here for a single database:
http://archives.postgresql.org/pgsql-general/2008-02/msg00343.php

Just slap a wrapper around it (untested),  out.sql being the name of
the dump-file:

for i in  $(awk '/^CREATE DATABASE/ {print $3}' out.sql); do dump.sh
${i} out.sql  ${i}.sql; done

Of course you won't be getting the creation of specific roles or
anything that way.


>  Thanks,
>  Luca
Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm