CODEWITHSUNDEEP
databasepostgresql
ManishD
ManishD

Reputation: 341

how to drop all databases except few ones in postgres

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.

Upvotes: 33

Views: 19743

Answers (3)

gXXX
gXXX

Reputation: 149

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

Upvotes: 14

kraymer
kraymer

Reputation: 3434

As accepted answer kinda demonstrates it, dropping multiple databases was particularly tedious for me, so I wrote an helper script to alleviate this operation : https://github.com/Kraymer/ezdropdb

In short, you enter a pattern that the databases you want to suppress must match then all db names results are listed and there is a final prompt where you can enter which ones of those to drop (cf screenshot on project page) .

Upvotes: 5

ntalbs
ntalbs

Reputation: 29458

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

Upvotes: 65

Related Questions