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.
4
-
"drop database databasename" for your deletable 18 databases..SkyWalker– SkyWalker2014-07-03 08:10:55 +00:00Commented Jul 3, 2014 at 8:10
-
Thanks for quick reply! I want a single script where I would like to mention only the 2 databases names which I want to keep.ManishD– ManishD2014-07-03 08:12:44 +00:00Commented Jul 3, 2014 at 8:12
-
@user3800715 ... and when you tried to write this script yourself, using the scripting language of your choice, where did you get stuck?Craig Ringer– Craig Ringer2014-07-03 08:16:57 +00:00Commented Jul 3, 2014 at 8:16
-
@CraigRinger I am not sure how to write the script for this query. Can someone provide a script for the same compatible to postgres?ManishD– ManishD2014-07-03 08:21:46 +00:00Commented Jul 3, 2014 at 8:21
Add a comment
|
3 Answers
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
1 Comment
estani
optionally:
psql -d postgres < <( psql -Atc "select 'drop database \"'||datname||'\";' from pg_database where datistemplate=false /* AND add_extra_conditions_here */;")From pgAdmin you can now select properties on a database, select DBs to drop and click delete/drop. Quick and easy! Drop selected databases:
Comments
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) .
