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
/usr/local/pgsql/data with whatever your $PGDATA directory is. For example, for PostgreSQL installed via Homebrew, it's /usr/local/var/postgres. Also, if you export $PGDATA, then you can run initdb with no arguments. Finally, do note that deleting your $PGDATA directory is a separate step. initdb does not do it for you. Oh, and the deletion won't work right if your postgres server is still running.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:
echo \pset pager off \copy (select 'drop database "'||datname||'";' from pg_database where datistemplate=false) to STDOUT; generates a string for psql to execute
\pset pager off ensures you get all records instead of that (54 rows) crap\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.| psql -U <user> -d postgres pipes said string into psql, which executes it. Replace <user> with the user you want to use| <appropriate grep> is for stripping out the "Pager usage is off" line
findstr /v "Pager" or findstr /b "drop"grep 'drop'| psql -U <user> -d postgres pipes the resulting set of drop database commands into psql again, which executes them, thus dropping all the databasespostgres database. You can strip it either in the SELECT or the grep if you don't want that to happen.
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
-e 'UTF-8' for pg_createcluster otherwise you may have to be changed later if you want UTF-8 databases and the locale is not set to UTF8.
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
Had the same problem and decided to go with a pure OSX shell approach. Inspired by https://stackoverflow.com/a/58735470
psql --csv --username postgres -c "SELECT datname FROM pg_database where datname like "test-%";" | xargs -I% echo psql --username=postgres -c "drop database \"%\""
Remove echo when you want to run the commands, I left it in for safety.
Explanation:
psql --csv --username postgres -c "SELECT datname FROM pg_database where datname like "test-%";" lists all your databases in a csv format, but since we're querying for only a single column you will simply get a list of databases separated by new lines. --csv removes all the output formatting. You can customize the query to select only the databases you want to drop with a where condition.
xargs -I% echo psql --username=postgres -c "drop database \"%\"" runs all lines as a command. -I% defines a replacement string and what follows is the command it will run. Remove echo when you want to run actual psql commands, I left it in for safety.
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
