How to delete postgresql databases matching a pattern?

Question

With pgAdmin III, I can list all the databases on my postgresql server.

But with pgAdmin, I can delete only 1 database at the time. It takes a lot of time to delete, for example, 30 databases, one by one...

So, what would be your approach to delete, for example, all the databases with a name containing the word "june"?

Probably I will have to build a Bash script. No problem with this. But which command would you use in this script?

I have search the web for many hours without success for this problem...

Thanks to help.

Answer 1

psql -c "copy (select datname from pg_database where datname like '%june%') to stdout" | while read line; do
    echo "$line"
    #dropdb -i "$line"
done

Or using for loop which is more reliable (while block executed in the parent context so it is necessary to do some additional movements for its body):

for dbname in $(psql -c "copy (select datname from pg_database where datname like '%june%') to stdout") ; do
    echo "$dbname"
    #dropdb -i "$dbname"
done

Also option -X for psql (do not use ~/.psqlrc file) could be good to avoid unnecessary outputs.

For psql and dropdb utilities you probably need to provide the connection options (see documentation)

Answer 2

Big thanks to Abelisto.

Here are our last script that works well, after many months of search.

#!/bin/bash
clear
export PGPASSWORD="xxxxx"

PATTERN=$1

echo "Pattern parameter: $PATTERN"

/usr/bin/psql -U odoo -d postgres -c "copy (select datname from pg_database where datname like '%$PATTERN%') to stdout" | while read line; do
    echo "$line"
    dropdb -U xxxx "$line"
done
echo
echo "Databases which names matches pattern $PATTERN were deleted!"
echo
exit