How to Drop Multiple Databases in PostgreSQL

Introduction

While performing database integration tests, things went south, and I ended up with more than 15 (fifteen) temporary PostgreSQL test databases.

I use OmniDB, an open-source application for managing databases. Version (3.0.2b) of OmniDB does not have an option for selecting and dropping multiple databases. To drop a database in OmniDB, you right-click on a database, select Drop Database from the context menu, run the query to drop the database, and finally close the query window. The thought of repeating the four steps on all those test databases led me to think of other efficient alternatives.

The following three alternatives for dropping multiple databases in PostgreSQL came to mind:

1. Anonymous Code Block
2. Interactive Terminal
3. Using a shell script

My test databases are prefixed with test; I will extract the names of the databases from pg_database database using SELECT datname FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false SQL query. The WHERE clause in the SQL query will restrict the database names to my test databases and non-template databases.

In the following sections, I explore the above three options in dropping multiple databases in PostgreSQL.

Anonymous Code Block

The DO statement in PostgreSQL executes an anonymous code block. In the past, I have used an anonymous code block to drop tables from a database.

DO $$
  DECLARE
      r RECORD;
BEGIN
    FOR r IN
        SELECT datname
        FROM pg_database
        WHERE datname LIKE 'test%' AND datistemplate=false
    LOOP
        EXECUTE 'DROP DATABASE ' || quote_ident(r.datname) || ';' ;
    END LOOP;
END $$;

This time, however, the anonymous code block throws an exception:

DROP DATABASE cannot be executed from a function
CONTEXT: SQL statement "DROP DATABASE test_01024da6a3;"
PL/pgSQL function inline_code_block line 12 at EXECUTE

As it turns out, you cannot execute DROP DATABASE statements inside a transaction block and anonymous code blocks being functions are transactional and this is the reason for the exception.

Interactive Terminal

PostgreSQL interactive terminal (psql) allows you to enter, edit, execute commands, and view results of SQL queries. In addition to SQL queries, psql provides meta-commands which are commands processed by psql without being sent to a PostgreSQL server; all meta-commands have a backslash () prefix. In the following paragraphs, I explain how to dynamically create a DROP DATABASE SQL query and run the query using the \gexec meta-command.

Before you execute PostgreSQL SQL commands, you have to connect to a PostgreSQL server. To connect to a PostgreSQL server, run the psql -d postgres command from a terminal window. If you don't specify the user name, PostgreSQL will default to the current operating system user. The default user has permission to create and drop databases. You can connect as a different user by appending the -U command-line option to the psql command (psql -U username -d postgres).

Upon successful connection, the psql prompt you get will either be postgres=> or postgres=# if you are a database superuser. At the prompt, type your SQL query to construct DROP DATABASE statements for the databases you want to drop. Do not terminate the query with a semicolon (;) --SELECT 'DROP DATABASE ' || quote_ident(datname) || ';' FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false. If you terminate the query with a semicolon (;), the query will run when you press the RETURN key.

The \gexec meta-command sends the current query buffer to the server and treats each column of each row of the query's output as an SQL statement to be executed. In our case, our query will output a DROP DATABASE SQL query for each test database. \gexec meta-command will then run each statement from the output.

postgres=#
postgres=# SELECT 'DROP DATABASE ' || quote_ident(datname) || ';'
postgres=# FROM pg_database
postgres=# WHERE datname LIKE 'test%' AND datistemplate=false
postgres=#
postgres=# \gexec

Using Shell Script

Scripts help in automating repetitive tasks. You create a text file, type commands in the file, make the file executable, and any time you want to repeat the commands, you execute the script file instead of retyping the commands.

Creating and executing a shell script involves the following steps:

1. Use a text editor to create your script file
2. Insert commands in the file.
3. Save and close the file.
4. Make the script executable.
5. Execute the script.

Create a drop-test-dbs.sh text file and update with the following:

#!/bin/bash

test_databases_file=~/projects/test_dbs.txt
psql -d postgres -c "COPY (SELECT datname FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false) TO '$test_databases_file'"

while read dbname
do
  echo "dropping DB $dbname..."
  dropdb "$dbname"
done < $test_databases_file

echo "removing $test_databases_file file"
rm $test_databases_file

#!/bin/bash is called a shebang or a bang line. It specifies the interpreter that will execute the commands.

The script uses psql to connect to a PostgreSQL server, run SQL query to fetch names of the test databases and copy the database names to a test_dbs.txt file. We then open the created file (containing database names), and for each database name, we drop the database using the dropdb utility program. Finally, we remove the file containing the database names.

After creating our script file, we then open the console or terminal window and assign the execute permission to the file using the chmod command (chmod +x drop-test-dbs.sh).

Finally, to drop the databases, we execute the commands in the file by running the file from a console or terminal window (./drop-test-dbs.sh). If the script file is not within the current folder, remember to include the file path (file-location/drop-test-dbs.sh).

Wrapping Up

There are various methods for dropping multiple databases in PostgreSQL.

ou can use desktop or web-based database management tools to drop databases. Some database management tools allow dropping one database at a time; others can drop multiple databases.

You cannot drop PostgreSQL databases within a transaction; PostgreSQL functions (including anonymous functions) are transactional.

psql is a powerful command-line tool that ships with PostgreSQL. You can use psql to connect to a server and interact with databases. Using psql and querying the pg_database database for the required databases together with \gexec meta-command is another option for dropping multiple databases in PostgreSQL. However, failing to filter for required databases will drop all databases from your server.

If the task of dropping multiple databases is repetitive, you can consolidate the psql commands into a shell script file and execute the script file.

If you would like to create multiple databases for testing the methods described in this post, you can create and run a script file with the following commands:

#!/bin/bash

for n in {1..15}
do
  # Use Bash command substitution to store the
  # output (GUID) from uuidgen command
  DB_ID=$(uuidgen)

  # Use Bash Substring expansion to extract
  # 8 characters of the GUID starting from index 0
  db="test_db_${DB_ID:0:8}"

  createdb "$db"
  echo "DB $db created"
done