On 6 May 2010 21:15, Greg Smith <g...@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` usern...@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
