CODEWITHSUNDEEP
databasepostgresqlpattern-matching
La Page PT
La Page PT

Reputation: 621

How to delete postgresql databases matching a pattern?

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.

Upvotes: 11

Views: 5500

Answers (5)

stephanos
stephanos

Reputation: 186

I use a bash one-liner for this:

psql -c '\l' | awk '{print $1}' | grep -E '.*june.*' | xargs -n 1 dropdb

Upvotes: 0

George Griffin
George Griffin

Reputation: 754

Lots of good, robust solutions; but here is a bash one-liner to keep in the ol' grimoire:

psql -Atqc "SELECT 'DROP DATABASE ' || quote_ident(datname) || ';' FROM pg_database WHERE datname like '%june%';" | psql

This uses a SELECT statement that builds some strings around the datnames returned by a query against the pg_database table.

SELECT 'DROP DATABASE ' || quote_ident(datname) || ';' 
FROM pg_database WHERE datname like '%june%';

This is executed directly from the shell using arguments to psql to:

-A Turn off fill justification when printing out table elements.

-t Turn off printing of column names and result row count. This is useful with the -c option in shell scripts.

-q Specifies that psql should do its work quietly. By default, it prints welcome and exit messages and prompts for each query. If this option is used, none of this happens. This is useful with the -c option.

-c query Specifies that psql is to execute one query string, query, and then exit. This is useful for shell scripts, typically in conjunction with the -q option in shell scripts

All of this is then piped into another invocation of psql with | psql when then executes the DROP DATABASE queries

Upvotes: 2

La Page PT
La Page PT

Reputation: 621

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

Upvotes: 1

Nux
Nux

Reputation: 10062

I think a more elegant way is to use a for loop.

Note that options -t and -A makes psql return only data (no headers nor extra whitespace). This allows you to directly create a bash array from psql.

# drop all molnet* databases
sql="select datname from pg_database where datname like '%june%'"
dbDelNames=`psql -U postgres -t -A -c "$sql"`
for dbName in ${dbDelNames[@]}
do
    echo -e "\n\n[INFO] Drop $dbName.\n"
    psql -U postgres -c "DROP DATABASE IF EXISTS ${dbName};"
done

Upvotes: 0

Abelisto
Abelisto

Reputation: 15624

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)

Upvotes: 11

Related Questions