Re: split pg_dumpall backups per database

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