postgresql 如何删除 Postgres 上的所有数据库？

How to delete all databases on Postgres?

提问by chotchki

I take daily backs of our postgres development box using: pg_dumpall -h 127.0.0.1 -U user -w | gzip blah.gz

我每天使用以下命令获取 postgres 开发箱的支持： 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?

由于 9.0 现在是候选版本，我想每天将此每日备份恢复到 postgres9.0rc1 框以进行测试，但是我不确定如何重复编写脚本。是否有一些目录我可以用核武器来做到这一点？

采纳答案by John Neuhaus

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 Hymaning with your config and also having rummage through the file system, this is a much better answer:

诚然，这个问题此时已有 9 年历史，但它仍然是删除所有数据库的第二个谷歌结果。如果您只想从 N 个 DB 转到 0，而无需使用您的配置，也不想翻阅文件系统，这是一个更好的答案：

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

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

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

根据答案，以下脚本将生成 N 个drop database命令，每个非模板 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

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 offensures 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 postgrespipes 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 postgrespipes the resulting set of drop databasecommands into psql again, which executes them, thus dropping all the databases
6. WARNING: Without any additional filtering, this willalso drop the postgresdatabase. You can strip it either in the SELECTor the grep if you don't want that to happen.

1. 这是一系列的管道
2. echo \pset pager off \copy (select 'drop database "'||datname||'";' from pg_database where datistemplate=false) to STDOUT;生成一个字符串供 psql 执行
   1. \pset pager off确保您获得所有记录而不是（54 行）废话
   2. \copy (select 'drop database "'||datname||'";' from pg_database where datistemplate=false) to STDOUT;执行上述查询，将结果发送到 STDOUT。我们必须这样做，因为我们以\pset.
3. | psql -U <user> -d postgres通过管道将字符串输入到 psql 中，然后执行它。替换<user>为您要使用的用户
4. | <appropriate grep>用于去除“寻呼机使用已关闭”行
   1. Windows 用户可以使用findstr /v "Pager"或findstr /b "drop"
   2. *nix 用户可以使用 grep 'drop'
5. | psql -U <user> -d postgres将生成的drop database命令集再次通过管道传输到 psql 中，由 psql 执行它们，从而删除所有数据库
6. 警告：如果没有任何额外的过滤，这也会删除postgres数据库。SELECT如果您不希望发生这种情况，您可以在 the或 grep 中删除它。

回答by halfdan

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:

您可以执行“删除集群”和“创建集群”，这将自动擦除所有数据库。擦除 $PGDATA 目录中的所有数据并使用以下命令重新初始化集群：

initdb -D /usr/local/pgsql/data

回答by Fernando Almeida

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 versionand main= cluster name

其中9.2=cluster version和main=cluster name

回答by tobias47n9e

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

1）在某处创建一个目录（例如/home/USERNAME/BACKUPDIR）。

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

2) 作为超级用户转到 postgresql 数据目录 (Fedora eg /var/lib/pgsql/)

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

3) 将 移动data到您的备份文件夹：mv data /home/USERNAME/BACKUPDIR

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

4）然后使用例如重新初始化一个新数据库 sudo postgresql-setup --initdb --unit postgresql