PostgreSQL - filter database list

Question

I have this script (which backups databases daily):

#!/bin/bash
# Location to place backups.
backup_dir="/home/user/openerp/7.0/backup/"
#String to append to the name of the backup files
backup_date=`date +%Y-%m-%d`
#Numbers of days you want to keep copie of your databases
number_of_days=7
databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in $databases; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to $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 I run this script, it starts doing databases backups normally, but when it does like half database backups, it just hangs like it is doing some long backup and never ends it. So some of my databases some times end up not backed up.

I think it is because it tries to backup databases like template0 and template1. I tried to look in documentation how this databases filtering is working, but didn't find any info.

Can anybody tell me how to filter all my databases except databases like template0, template1, postgres. Also would be great if someone could give a link to documentation where it says about such filtering like this:

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

Output as requested:

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

So all databases, except postgres, template0 and template1

Answer 1

I suspect your conjecture about the template databases is incorrect. pg_dump will dump them almost instantly.

It's much more likely that your problem is that pg_dump is waiting on an attempt to get a lock on a table that someone else holds an ACCESS EXCLUSIVE lock on. You'd have to see which pg_dump process is blocked and examine the pg_locks view on that database to get a better idea what's happening. Your logs should tell you which database is stalling the dump and ps will tell you which pg_dump is running. pg_stat_activity will let you identify the pg_dump process's connection.

BTW, You're totally failing to do any error handling in this script. If a backup fails you'll never know unless you happen to be reading the logs and notice some useful stderr output from pg_dump. I'd personally recommend using pgbarman for your regular backups, though periodic dumps are still a good idea.

Since you might want to exclude template0 and template even though they're most likely not the problem, you can use:

psql --tuples-only -P format=unaligned -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'";

instead of your text-processing solution. You'll find the --tuples-only and -P format=unaligned options very useful when scripting work with psql.