PostgreSQL - filter database list

Question

I have this script (which backs up the 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 to back up the databases in normal mode, but when it looks like half of the database backups, it just hangs, as if making a long backup and never ending it. Therefore, some of my databases are not copied several times.

I think this is because it is trying to archive databases such as template0 and template1. I tried to look in the documentation how this database filtering works, but did not find any information.

Can someone tell me how to filter all my databases except databases like template0, template1, postgres. It would also be great if someone could give a link to the documentation that talks about such filtering as follows:

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

Conclusion on request:

 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 template database hypothesis is incorrect. pg_dump will reset them almost instantly.

Most likely, your problem is that pg_dump waiting for attempts to obtain a table lock in which someone else holds an ACCESS EXCLUSIVE lock. You will need to see which pg_dump process is locked, and look at the pg_locks in this database to better understand what is going on. Your logs should indicate which database is holding the dump, and ps will tell you which pg_dump running. pg_stat_activity allows you to identify the connection of the pg_dump process.

By the way, you are completely unable to handle error handling in this script. If the backup fails, you will never know if you don’t see reading the logs and pay attention to the useful stderr output from pg_dump. I personally recommend using pgbarman for regular backups, although periodic dumps are still a good idea.

Since you can exclude template0 and template , although this is most likely not a 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 word processing solution. You will find the options --tuples-only and -P format=unaligned very useful when working with scripts with psql .