Force drop db while others may be connected
I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a
-forceflag, that will drop all connections and then the DB.How can I implement it?
I'm using
dropdbcurrently, but other tools are possible.In PostgreSQL*, you cannot drop a database while clients are connected to it.
At least, not with the
dropdbutility - which is only a simple wrapper aroundDROP DATABASEserver query.Quite robust workaround follows:
Connect to your server as superuser, using
psqlor other client. Do not use the database you want to drop.psql -h localhost postgres postgresNow using plain database client you can force drop database using three simple steps:
Make sure no one can connect to this database. You can use one of following methods (the second seems safer, but does not prevent connections from superusers).
/* Method 1: update system catalog */ UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb'; /* Method 2: use ALTER DATABASE. Superusers still can connect! ALTER DATABASE mydb CONNECTION LIMIT 0; */Force disconnection of all clients connected to this database, using
pg_terminate_backend.SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb'; /* For old versions of PostgreSQL (up to 9.1), change pid to procpid: SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'mydb'; */Drop the database.
DROP DATABASE mydb;
Step 1 requires superuser privileges for the 1st method, and database owner privileges for the 2nd one. Step 2 requires superuser privileges. Step 3 requires database owner privilege.
* This applies to all versions of PostgreSQL, up to version 12. Version 13 has
DROP DATABASE mydb FORCESo I don't know what I did wrong, but now I can't even connect to the database I targeted! Nor can I drop it as it says "Maintenance database cannot be dropped"
@MattSkeldon, no idea what this message means. In vanilla PostgreSQL you can drop any database except template0 & template1. Maybe you use some non-free / commercial version? Maybe it's client issue not server issue? Did you try psql?
Unfortunately I come from a SQL background, using PGSQL is being used owing to the non commercial / free status.
This doesn't work for me where there are long-running zombie sessions. pg_terminate_backend() does not kill those sessions so I'm still a bit stuck about what to do: I am a Postgres su, but I don't have access to the server it's running on.
Nice. And how do I allow connections again?
Using @filiprem's answer in a my case and simplifying it:
-- Connecting to the current user localhost's postgres instance psql -- Making sure the database exists SELECT * from pg_database where datname = 'my_database_name' -- Disallow new connections UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'my_database_name'; ALTER DATABASE my_database_name CONNECTION LIMIT 1; -- Terminate existing connections SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'my_database_name'; -- Drop database DROP DATABASE my_database_nameon the UPDATE line it should be false not 'false'.
There is a way to do this with the shell utilities
dropdb&pg_ctl(orpg_ctlclusterin Debian and derivates). But @filiprem's method is superior for several reasons:- It only disconnects users from the database in question.
- It does not need to restart the whole cluster.
- It prevents immediate reconnects, possibly spoiling the
dropdbcommand.
I quote
man pg_ctlcluster:With the
--forceoption the "fast" mode is used which rolls back all active transactions, disconnects clients immediately and thus shuts down cleanly. If that does not work, shutdown is attempted again in "immediate" mode, which can leave the cluster in an inconsistent state and thus will lead to a recovery run at the next start. If this still does not help, the postmaster process is killed. Exits with 0 on success, with 2 if the server is not running, and with 1 on other failure conditions. This mode should only be used when the machine is about to be shut down.pg_ctlcluster 9.1 main restart --forceor
pg_ctl restart -D datadir -m fastor
pg_ctl restart -D datadir -m immediateimmediately followed by:
dropdb mydbPossibly in a script for immediate succession.
Not only is this less than ideal as it kicks the full postgres instance but it is not guaranteed to work. It is possible for a client to connect between the time you restart the server and attempt to run dropdb again. @filiprem 's answer above disables all connections to the database prior to disconnecting and will keep other databases up.
If you're on something like RDS where connections without a database selected put you into the DB you asked to be created by default you can do this variant to get around yourself being the last open connection.
DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist; CREATE DATABASE temporary_db_that_shouldnt_exist with OWNER your_user; \connect temporary_db_that_shouldnt_exist SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'the_db_you_want_removed'; DROP DATABASE IF EXISTS the_db_you_want_removed; -- -- Name: the_db_you_want_removed; Type: DATABASE; Schema: -; Owner: your_user -- CREATE DATABASE savings_champion WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; ALTER DATABASE the_db_you_want_removed OWNER TO your_user; \connect the_db_you_want_removed DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist;
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Matt Skeldon 3 years ago
So I don't know what I did wrong, but now I can't even connect to the database I targeted! Nor can I drop it as it says "Maintenance database cannot be dropped"