On 6 May 2010 21:15, Greg Smith
<greg@2ndquadrant.com> wrote:
Guillaume Lelarge wrote:
And rather than checking the database name, I usually prefer to filter
with datallowconn.
It'll save template1 and postgres, but the script won't give me an error
on template0.
Template databases are labeled as such, so this excludes both template0 and template1 without having to hardcode their names:
query="select datname from pg_database where not datistemplate"
However, if you can't connect to them, you can't dump them either! So you really need both those things:
query="select datname from pg_database where not datistemplate and datallowconn"
for line in `psql -At -c "$query" postgres`
do
pg_dump -f /home/backups/`date +\%Y\%m\%d`/"$line".sql "$line"
done
It's also a good idea to use "-At" instead of just "-t" when writing scripts that parse the output from psql, as I modified the above. If you look carefully you'll see that without the "-A", there is a space before the name of each line output. Doesn't matter in this case, but using the default "aligned" mode can cause problems in more complicated scripts that output more than one field.
I've actually started using this myself, and have set myself up following in a cron job (as couldn't decide on a nice location for the script):
mkdir /tmp/`date +\%Y\%m\%d` && pg_dumpall -c | gzip > /tmp/`date +\%Y\%m\%d`/FULL.sql.gz && query="select datname from pg_database where not datistemplate and datallowconn;";for line in `psql -U postgres -At -c "$query" postgres`;do pg_dump -U postgres "$line" | gzip > /tmp/`date +\%Y\%m\%d`/"$line".sql.gz;done;scp -r /tmp/`date +\%Y\%m\%d` username@my.location:/backups/location/ ; rm -rf /tmp/`date +\%Y\%m\%d`
This creates a directory named in yyyyddmm format in /tmp, then backs up the whole database cluster into a file called FULL.sql.gz, then each individual database as databasename.sql.gz, then copies it off to a remote location, then deletes them from /tmp.
Thom