How to remove a database from a postgres drop database force?

412    Asked by debbieJha in SQL Server , Asked on Sep 30, 2022

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 -force flag, that will drop all connections and then the DB.

How can I implement it?

I'm using drop dB currently, but other tools are possible.

Answered by Deepa bhawana

To remove a database from a postgres drop database force -


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;


Your Answer

Interviews

Parent Categories