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.

Abelisto · Accepted Answer · 2018-06-30 11:27:48Z

11

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)

edited Jun 30, 2018 at 11:27

answered Jul 28, 2016 at 0:01

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cees Timmerman Over a year ago

Works on psql 12 without confirmation:

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

George Griffin · Accepted Answer · 2022-11-01 20:54:39Z

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

Matthieu · Accepted Answer · 2019-10-27 21:48:06Z

1

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

edited Oct 27, 2019 at 21:48

Matthieu

3,1456 gold badges63 silver badges90 bronze badges

answered Jul 30, 2016 at 3:05

La Page PT

6211 gold badge9 silver badges21 bronze badges

Comments

Nux · Accepted Answer · 2019-06-08 09:24:33Z

0

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

answered Jun 8, 2019 at 9:24

Nux

10.2k6 gold badges63 silver badges80 bronze badges

Comments

stephanos · Accepted Answer · 2024-12-28 22:31:26Z

0

I use a bash one-liner for this:

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

answered Dec 28, 2024 at 22:31

stephanos

1863 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to delete postgresql databases matching a pattern?

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related