CODEWITHSUNDEEP
postgresql
chotchki
chotchki

Reputation: 4343

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?

Upvotes: 30

Views: 90757

Answers (6)

Mike Minicki
Mike Minicki

Reputation: 8456

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:

  1. 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.

  2. 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.

Upvotes: 0

Hussam Suleiman
Hussam Suleiman

Reputation: 1

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

Upvotes: -1

John Neuhaus
John Neuhaus

Reputation: 1937

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.

Upvotes: 19

tobias47n9e
tobias47n9e

Reputation: 2231

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

Upvotes: 1

Fernando Almeida
Fernando Almeida

Reputation: 3174

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

Upvotes: 21

halfdan
halfdan

Reputation: 34214

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

Upvotes: 22

Related Questions