how to drop all databases except few ones in postgres

Question

I want to drop all of the databases except few ones. Lets say there are 20 databases and I want to delete 18 out of them but keep 2 as it is the latest ones and are in use.

Please suggest.

Answer 1

First, execute the following query in the psql terminal.

select 'drop database "'||datname||'";' from pg_database where datistemplate=false; 

This will generate drop database command for all the databases. Copy the result in a text editor and exclude(delete) what you want to keep and save it as dd.sql file. And execute it like this:

psql -d postgres -f dd.sql 

Answer 2

From pgAdmin you can now select properties on a database, select DBs to drop and click delete/drop. Quick and easy! Drop selected databases: