- Yair Mark
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:
Now check what databases you have and the schema for it (it should be the first column of the output):
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
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
- [Backing up Postgresql DB data](https://www.postgresql.org/docs/9.1/backup-dump.html
- [Wiping a Postgresql database] (https://stackoverflow.com/questions/7970004/wiping-a-postgresql-database)
- How to drop a PostgreSQL database if there are active connections to it?
- Stopping postgresql from starting on ubuntu startup