psql drop database is the SQL DROP DATABASE name; run from inside a psql session. The trap: you cannot drop a database you are currently connected to, and you cannot drop a database with active connections from other sessions without WITH (FORCE) (Postgres 13+). The team that maintains a Postgres instance runs this often enough to know the connect-to-postgres-first incantation by heart.
Table of contents
- The basic command
- Listing databases first
- The active-session trap and WITH (FORCE)
- Dropping from a script (CI / dev reset)
- How this fits the rest of the stack
- FAQ
The basic command
Connect to a different database (you cannot drop the one you are connected to):
\c postgres
DROP DATABASE myapp_dev;
\c postgres switches to the maintenance database. Then DROP DATABASE removes the target. The ; terminator is required - psql sends the command to the server when it sees the semicolon.
The team that runs this from a script wraps it in a transaction: BEGIN; DROP DATABASE myapp_dev; COMMIT;. If anything goes wrong, the ROLLBACK is automatic.
Listing databases first
Before dropping, confirm the name. The list-databases command:
\l
Or in SQL: SELECT datname FROM pg_database ORDER BY datname;. The team that scripts this reads the list into a variable, double-checks the name, then runs the drop.
The wrong database name is the most common way this command fails. The team that has a myapp_dev, myapp_staging, and myapp_prod learns to check twice.
The active-session trap and WITH (FORCE)
If another session is connected to the database, the drop fails with database "myapp_dev" is being accessed by other users. The fix:
-
Disconnect the sessions (PG 13+):
DROP DATABASE myapp_dev WITH (FORCE); -
Or terminate the sessions manually:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'myapp_dev' AND pid <> pg_backend_pid();then drop.
The WITH (FORCE) option (Postgres 13+) terminates the connections and then drops. The team that scripts a clean teardown uses this. The team that runs an older Postgres (12 and below) has to do the terminate-then-drop dance by hand.
Dropping from a script (CI / dev reset)
The pattern that resets a dev database cleanly:
psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'myapp_dev';"
psql -U postgres -c "DROP DATABASE myapp_dev;"
psql -U postgres -c "CREATE DATABASE myapp_dev;"
Three commands: kill connections, drop, recreate. The team that runs this in a CI test setup has it in a Makefile or a shell script. The error handling: if the drop fails, the next command fails too, and the script exits non-zero.
For a one-liner version that works on Postgres 13+: psql -U postgres -c "DROP DATABASE myapp_dev WITH (FORCE);" followed by CREATE DATABASE.
FAQ
Can I drop the database I am connected to?
No. psql returns ERROR: cannot drop the currently open database. Connect to a different database (usually postgres or template1) first, then run the drop.
What does WITH (FORCE) do?
Postgres 13+ feature. It terminates all backends connected to the target database, waits for them to close, then drops the database. Without it, the drop fails with database is being accessed by other users if any session is connected.
Is DROP DATABASE reversible?
No. It removes the catalog entries and deletes the directory containing the data. The only recovery path is restoring from a backup (pg_dump, pg_basebackup, or a managed snapshot). The team that runs this in production has a backup verified first.
Why does my drop fail with “being accessed by other users”?
Some other session - a connection pool, a long-running query, a left-open psql - is still connected. Either terminate those connections manually or use WITH (FORCE) on Postgres 13+.
Can I drop multiple databases in one command?
No. DROP DATABASE takes one name. The team that scripts a clean teardown runs the command once per database, or uses a DO block to loop.
How this fits the rest of the stack
For a sense of what the full project costs before it commits, the RunxBuild hosting calculator shows the line items together. The API, the database, the storage, the worker, the bandwidth - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers.
Useful related references: