Published on

Dropping a Postgres Database

Authors

Dropping a Postgres database is fairly easy.

Backing up the existing data

But first if you wish to backup the DB data to a file first:

# login to the main schema
psql -U adminUser
# get the db name/s you wish to dump
\dt
# dump the db to a file (will be placed in the directory that you logged into the db from (i.e. where you ran the psql command)
pg_dump foodb > foo_outfile
# repeat the last step for any other dbs you wish to dump, remember to change the file name otherwise other dumps will be overwritten

Dumping the schema and DB (even if there are active sessions)

First login ideally to the top level schema. It will be something like this:

psql -U adminUser

Then see what schemas there are:

\l

Now check what databases you have and the schema for it (it should be the first column of the output):

\dt

Drop the schema/s for the DB/s you want to drop:

drop schema theSchemaForDBToDrop CASCADE;

Kill all sessions except yours for the db you want to drop, lets pretend that db is called foo:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'foo'
  AND pid <> pg_backend_pid();

Now drop the db:

drop database foo;

Repeat the last 2 steps for any other DBs you wish to drop (assuming they are all under the same schema, otherwise drop the schema first).

If you wish to stop and disable the DB service on Ubuntu or any system that has systemd do so as follows:

# stop the service
systemctl stop postgresql
# disable the service so that it does not start again if the box is rebooted
systemctl disable postgresql
# confirm it has been stopped
systemctl status postgresql

References