How to delete all databases on Postgres?

I 开发者_开发问答take daily backs of our postgres development box using: pg_dumpall -h 127.0.0.1 -U user -w | gzip blah.gz

Since 9.0 is now a release candidate I would like to restore this daily backup on a daily basis to a postgres9.0rc1 box for testing, however I'm not sure how to script it repeatedly. Is there some directory I can nuke to do this?

You can do "drop cluster" and "create cluster" which will automtically erase all databases. Erase all data in you $PGDATA directory and reinit the cluster using:

initdb -D /usr/local/pgsql/data

You can use:

$ pg_dropcluster 9.2 main
$ pg_createcluster 9.2 main
$ pg_ctlcluster 9.2 main start
$ pg_restore -f your_dump_file

where 9.2 = cluster version and main = cluster name

Granted the question is 9 years old at this point, but it's still the second google result for deleting all databases. If you just want to go from N DBs to 0 without jacking with your config and also having rummage through the file system, this is a much better answer:

https://stackoverflow.com/a/24548640/3499424

From the answer, the following script will generate N drop database commands, one for each non-template DB:

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

From there, you can edit and run manually, or pipe further along into a script. Here's a somewhat verbose one-liner:

echo \pset pager off \copy (select 'drop database "'||datname||'";' from pg_database where datistemplate=false) to STDOUT; | psql -U <user> -d postgres | <appropriate grep> | psql -U <user> -d postgres

Explanation:

1. This is a series of pipes
2. echo \pset pager off \copy (select 'drop database "'||datname||'";' from pg_database where datistemplate=false) to STDOUT; generates a string for psql to execute
   1. \pset pager off ensures you get all records instead of that (54 rows) crap
   2. \copy (select 'drop database "'||datname||'";' from pg_database where datistemplate=false) to STDOUT; executes the aforementioned query, sending the result to STDOUT. We have to do this since we lead with \pset.
3. | psql -U <user> -d postgres pipes said string into psql, which executes it. Replace <user> with the user you want to use
4. | <appropriate grep> is for stripping out the "Pager usage is off" line
   1. Windows users can use findstr /v "Pager" or findstr /b "drop"
   2. *nix users can use grep 'drop'
5. | psql -U <user> -d postgres pipes the resulting set of drop database commands into psql again, which executes them, thus dropping all the databases
6. WARNING: Without any additional filtering, this will also drop the postgres database. You can strip it either in the SELECT or the grep if you don't want that to happen.

Moving the database data somewhere else can achieve the same as deleting, and you can easily move it back in case you change your mind (But be sure to be careful and back up your data in any case).

1) Create a directory somewhere (e.g. /home/USERNAME/BACKUPDIR).

2) As a superuser go to the postgresql data directory (Fedora e.g. /var/lib/pgsql/)

3) Move the data to your backup folder: mv data /home/USERNAME/BACKUPDIR

4) Then reinit a new database using e.g. sudo postgresql-setup --initdb --unit postgresql

run this command as root:

cd /var/lib/postgresql/ && sudo -u postgres psql -c "SELECT 'dropdb '||datname||'' FROM pg_database WHERE datistemplate = false AND datallowconn = true And datname NOT IN ('postgres')" |grep ' dropdb ' |  sudo -u postgres /bin/bash ; cd