How to drop all databases starting with prefix in Postgresql

Question

In Postgresql I have several databases starting with hdb prefix.

What I am looking for is to to forcefully drop all such dbs, terminating existing connections if any.

I tried below way:

DO
$do$
DECLARE
   _db text;
BEGIN
FOR _db  IN
    SELECT datname FROM pg_stat_activity where datname ilike 'hdb%'
LOOP
    EXECUTE 'REVOKE CONNECT ON DATABASE ' || _db||' FROM public;';
    EXECUTE 'DROP DATABASE ' || _db;

END LOOP;
END
$do$;

But that says,

ERROR: DROP DATABASE cannot be executed from a function or multi-command string

Since you can't do it with a database function, write client code. — Laurenz Albe
– Laurenz Albe, Commented Aug 19, 2019 at 11:37

madflow · Accepted Answer · 2019-08-19 19:03:50Z

7

This is how I drop test databases with a client script (Linux):

#!/bin/bash

PREFIX='hdb'
export PGPASSWORD=postgres
export PGUSER=postgres
export PGHOST=localhost
export PGPORT=5432

TEST_DB_LIST=$(psql -l | awk '{ print $1 }' | grep '^[a-z]' | grep -v template | grep -v postgres)
for TEST_DB in $TEST_DB_LIST ; do
    if [ $(echo $TEST_DB | sed "s%^$PREFIX%%") != $TEST_DB ]
    then
        echo "Dropping $TEST_DB"
        dropdb --if-exists $TEST_DB
    fi
done

edited Aug 19, 2019 at 19:03

answered Aug 19, 2019 at 18:54

madflow

8,7013 gold badges45 silver badges61 bronze badges

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

5 Comments

Pratik Over a year ago

Thanks but I want to make it OS independent.

madflow Over a year ago

@Pratik With which client (scripting) language?

Pratik Over a year ago

As mentioned, using PostgreSQL script.

madflow Over a year ago

And nothing has changed: It is not possible in a database function.

Aida.Mirabadi Over a year ago

For more information, check out its reference copyprogramming.com/howto/…

Collectives™ on Stack Overflow

How to drop all databases starting with prefix in Postgresql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related