bash - PostgreSQL - filter database list -

i have script (which backups databases daily):

#!/bin/bash # location place backups. backup_dir="/home/user/openerp/7.0/backup/" #string append name of backup files backup_date=`date +%y-%m-%d` #numbers of days want keep copie of databases number_of_days=7 databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'` in $databases;   if [ "$i" != "template0" ] && [ "$i" != "template1" ];     echo dumping $i $backup_dir$i\_$backup_date     pg_dump -fc $i > $backup_dir$i\_$backup_date   fi done find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \; 

when run script, starts doing databases backups normally, when half database backups, hangs doing long backup , never ends it. of databases times end not backed up.

i think because tries backup databases template0 , template1. tried in documentation how databases filtering working, didn't find info.

can tell me how filter databases except databases template0, template1, postgres. great if give link documentation says such filtering this:

`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'` 

output requested:

demo demo_empty1 dn1 dn2 dn3 da21 da22 nbb323 nd nd2 pf12 postgres rub_demo1 template0 template1 test test3 testas_3 

so databases, except postgres, template0 , template1

i suspect conjecture template databases incorrect. pg_dump dump them instantly.

it's more problem pg_dump waiting on attempt lock on table else holds access exclusive lock on. you'd have see pg_dump process blocked , examine pg_locks view on database better idea what's happening. logs should tell database stalling dump , ps tell pg_dump running. pg_stat_activity let identify pg_dump process's connection.

btw, you're totally failing error handling in script. if backup fails you'll never know unless happen reading logs , notice useful stderr output pg_dump. i'd recommend using pgbarman regular backups, though periodic dumps still idea.

since might want exclude template0 , template though they're not problem, can use:

psql --tuples-only -p format=unaligned -c "select datname pg_database not datistemplate , datname <> 'postgres'"; 

instead of text-processing solution. you'll find --tuples-only , -p format=unaligned options useful when scripting work psql.