postgresql 使用shell检查PostgreSQL中是否存在数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14549270/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if database exists in PostgreSQL using shell
提问by Jimmy
I was wondering if anyone would be able to tell me about whether it is possible to use shell to check if a PostgreSQL database exists?
我想知道是否有人能够告诉我是否可以使用 shell 来检查 PostgreSQL 数据库是否存在？
I am making a shell script and I only want it to create the database if it doesn't already exist but up to now haven't been able to see how to implement it.
我正在制作一个 shell 脚本，如果它不存在，我只希望它创建数据库，但到目前为止还无法看到如何实现它。
回答by kibibu
I use the following modification of Arturo's solution:
我使用 Arturo 解决方案的以下修改：
psql -lqt | cut -d \| -f 1 | grep -qw <db_name>
psql -lqt | cut -d \| -f 1 | grep -qw <db_name>
What it does
它能做什么
psql -loutputs something like the following:
psql -l输出类似以下内容：
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-----------+----------+------------+------------+-----------------------
my_db | my_user | UTF8 | en_US.UTF8 | en_US.UTF8 |
postgres | postgres | LATIN1 | en_US | en_US |
template0 | postgres | LATIN1 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | LATIN1 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Using the naive approach means that searching for a database called "List, "Access" or "rows" will succeed. So we pipe this output through a bunch of built-in command line tools to only search in the first column.
使用简单的方法意味着搜索名为“列表”、“访问”或“行”的数据库会成功。因此，我们通过一堆内置命令行工具将此输出管道化，以便仅在第一列中进行搜索。
The -tflag removes headers and footers:
该-t标志删除页眉和页脚：
my_db | my_user | UTF8 | en_US.UTF8 | en_US.UTF8 |
postgres | postgres | LATIN1 | en_US | en_US |
template0 | postgres | LATIN1 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | LATIN1 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
The next bit, cut -d \| -f 1splits the output by the vertical pipe |character (escaped from the shell with a backslash), and selects field 1. This leaves:
下一位，cut -d \| -f 1通过垂直管道|字符（用反斜杠从 shell 转义）分割输出，并选择字段 1。这留下：
my_db
postgres
template0
template1
grep -wmatches whole words, and so won't match if you are searching for tempin this scenario. The -qoption suppresses any output written to the screen, so if you want to run this interactively at a command prompt you may with to exclude the -qso something gets displayed immediately.
grep -w匹配整个单词，因此如果您temp在这种情况下搜索，则不会匹配。该-q选项禁止写入屏幕的任何输出，因此如果您想在命令提示符下以交互方式运行此选项，您可以排除该选项，-q以便立即显示某些内容。
Note that grep -wmatches alphanumeric, digits and the underscore, which is exactly the set of characters allowed in unquoted database names in postgresql (hyphens are not legal in unquoted identifiers). If you are using other characters, grep -wwon't work for you.
请注意，grep -w匹配字母数字、数字和下划线，这正是 postgresql 中不带引号的数据库名称中允许的字符集（连字符在不带引号的标识符中是不合法的）。如果您使用其他字符，grep -w则不会为您工作。
The exit status of this whole pipeline will be 0(success) if the database exists or 1(failure) if it doesn't. Your shell will set the special variable $?to the exit status of the last command. You can also test the status directly in a conditional:
0如果数据库存在，则整个管道的退出状态将为(success)，否则为1(failure)。您的 shell 会将特殊变量设置$?为最后一个命令的退出状态。您还可以直接在条件中测试状态：
if psql -lqt | cut -d \| -f 1 | grep -qw <db_name>; then
# database exists
# $? is 0
else
# ruh-roh
# $? is 1
fi
回答by Nathan Osman
The following shell code seems to work for me:
以下 shell 代码似乎对我有用：
if [ "$( psql -tAc "SELECT 1 FROM pg_database WHERE datname='DB_NAME'" )" = '1' ]
then
echo "Database already exists"
else
echo "Database does not exist"
fi
回答by Arturo
postgres@desktop:~$ psql -l | grep <exact_dbname> | wc -l
This will return 1 if the database specified exists or 0 otherwise.
如果指定的数据库存在，则返回 1，否则返回 0。
Also, if you try to create a database that already exists, postgresql will return an error message like this:
此外，如果您尝试创建一个已经存在的数据库，postgresql 将返回如下错误消息：
postgres@desktop:~$ createdb template1
createdb: database creation failed: ERROR: database "template1" already exists
回答by bruce
I'm new to postgresql, but the following command is what I used to check if a database exists
我是 postgresql 的新手，但以下命令是我用来检查数据库是否存在的命令
if psql ${DB_NAME} -c '\q' 2>&1; then
echo "database ${DB_NAME} exists"
fi
回答by Otheus
I'm combining the other answers to a succinct and POSIX compatible form:
我正在将其他答案合并为一个简洁且兼容 POSIX 的形式：
psql -lqtA | grep -q "^$DB_NAME|"
A return of true(0) means it exists.
返回true( 0) 表示它存在。
If you suspect your database name might have a non-standard character such as $, you need a slightly longer approach:
如果您怀疑您的数据库名称可能包含非标准字符，例如$，您需要一个稍长的方法：
psql -lqtA | cut -d\| -f1 | grep -qxF "$DB_NAME"
The -tand -Aoptions make sure the output is raw and not "tabular" or whitespace-padded output. Columns are separated by the pipe character |, so either the cutor the grephas to recognize this. The first column contains the database name.
在-t和-A选项确保输出为原料，而不是“表格”或空白填充输出。列由管道字符 分隔|，因此cut或grep必须识别这一点。第一列包含数据库名称。
EDIT: grep with -x to prevent partial name matches.
编辑：grep 与 -x 以防止部分名称匹配。
回答by Nicolas Grilly
You can create a database, if it doesn't already exist, using this method:
您可以使用以下方法创建一个数据库（如果它尚不存在）：
if [[ -z `psql -Atqc '\list mydatabase' postgres` ]]; then createdb mydatabase; fi
回答by wildplasser
#!/bin/sh
DB_NAME=hahahahahahaha
psql -U postgres ${DB_NAME} --command="SELECT version();" >/dev/null 2>&1
RESULT=$?
echo DATABASE=${DB_NAME} RESULT=${RESULT}
#
回答by Steve Bennett
For completeness, another version using regex rather than string cutting:
为了完整起见，另一个使用正则表达式而不是字符串切割的版本：
psql -l | grep '^ exact_dbname\b'
So for instance:
所以例如：
if psql -l | grep '^ mydatabase\b' > /dev/null ; then
echo "Database exists already."
exit
fi
回答by Dan Kohn
The other solutions (which are fantastic) miss the fact that psql can wait a minute or more before timing out if it can't connect to a host. So, I like this solution, which sets the timeout to 3 seconds:
其他解决方案（非常棒）忽略了一个事实，即如果 psql 无法连接到主机，它可以在超时之前等待一分钟或更长时间。所以，我喜欢这个解决方案，它将超时设置为 3 秒：
PGCONNECT_TIMEOUT=3 psql development -h db -U postgres -c ""
This is for connecting to a development database on the official postgresAlpine Docker image.
这是用于连接到官方postgresAlpine Docker 映像上的开发数据库。
Separately, if you're using Rails and want to setup a database if it doesn't already exist (as when launching a Docker container), this works well, as migrations are idempotent:
另外，如果您正在使用 Rails 并且想要设置一个不存在的数据库（如启动 Docker 容器时），这很有效，因为迁移是幂等的：
bundle exec rake db:migrate 2>/dev/null || bundle exec rake db:setup
回答by phils
kibibu's accepted answeris flawed in that grep -wwill match anyname containing the specified pattern as a word component.
kibibu接受的答案有缺陷，因为grep -w它将匹配包含指定模式作为单词组件的任何名称。
i.e. If you look for "foo" then "foo-backup" is a match.
即，如果您查找“foo”，则“foo-backup”是匹配项。
Otheus's answerprovides some good improvements, and the short version will work correctly for most cases, but the longer of the two variants offered exhibits a similar problem with matching substrings.
Otheus 的回答提供了一些很好的改进，并且简短版本在大多数情况下都可以正常工作，但是提供的两个变体中较长的一个在匹配子字符串方面表现出类似的问题。
To resolve this issue, we can use the POSIX -xargument to match only entirelines of the text.
为了解决这个问题，我们可以使用 POSIX-x参数来匹配整行文本。
Building on Otheus's answer, the new version looks like this:
基于 Otheus 的回答，新版本如下所示：
psql -U "$USER" -lqtA | cut -d\| -f1 | grep -qFx "$DBNAME"
That all said, I'm inclined to say that Nicolas Grilly's answer-- where you actually ask postgres about the specific database -- is the best approach of all.
话虽如此，我倾向于说Nicolas Grilly 的回答——你实际上向 postgres 询问特定数据库——是最好的方法。
