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.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us