Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 707
ManishD Avatar asked Jul 03 '14 08:07

ManishD


People also ask

How do I dump all databases in PostgreSQL?

pg_dumpall is a utility for writing out (“dumping”) all PostgreSQL databases of a cluster into one script file. The script file contains SQL commands that can be used as input to psql to restore the databases. It does this by calling pg_dump for each database in the cluster.

What is Createdb in PostgreSQL?

createdb creates a new PostgreSQL database. Normally, the database user who executes this command becomes the owner of the new database. However, a different owner can be specified via the -O option, if the executing user has appropriate privileges. createdb is a wrapper around the SQL command CREATE DATABASE .


2 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 
like image 155
ntalbs Avatar answered Sep 21 '22 05:09

ntalbs


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

like image 41
gXXX Avatar answered Sep 23 '22 05:09

gXXX