使用Shell检查PostgreSQL中是否存在数据库 [英] Check if database exists in PostgreSQL using shell

问题描述

我想知道是否有人可以告诉我是否可以使用shell检查PostgreSQL数据库是否存在？

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脚本，并且我只希望它创建尚不存在的数据库，但直到现在还没有看到如何实现它。

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.

推荐答案

我对Arturo的解决方案进行了以下修改：

I use the following modification of Arturo's solution:

psql -lqt |切-d \ | -f 1 | grep -qw< db_name>

psql -l <​​/ code>输出以下内容：

psql -l outputs something like the following:

                                        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.

-t 标志删除页眉和页脚：

The -t flag removes headers and footers:

 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

---

下一位 cut -d \ | -f 1 分割输出b y垂直管道 | 字符（用反斜杠从外壳中转出），然后选择字段1。这将留下：

---

The next bit, cut -d \| -f 1 splits the output by the vertical pipe | character (escaped from the shell with a backslash), and selects field 1. This leaves:

 my_db             
 postgres          
 template0         

 template1         

---

grep -w 匹配整个单词，因此如果您匹配则不匹配在这种情况下搜索 temp 。 -q 选项会禁止写入屏幕的任何输出，因此，如果要在命令提示符下以交互方式运行此输出，则可以排除- q ，因此会立即显示某些内容。

---

grep -w matches whole words, and so won't match if you are searching for temp in this scenario. The -q option 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 -q so something gets displayed immediately.

请注意， grep -w 与字母数字匹配，数字和下划线，恰好是postgresql中未加引号的数据库名称中允许的字符集（连字符在未加引号的标识符中是合法的）。如果您使用其他字符，则 grep -w 将不适合您。

Note that grep -w matches 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 -w won't work for you.

如果数据库存在，整个管道的退出状态将为 0 （成功）或 1 （失败），如果没有。您的外壳程序会将特殊变量 $？设置为最后一个命令的退出状态。您也可以直接在条件中测试状态：

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:

if psql -lqt | cut -d \| -f 1 | grep -qw <db_name>; then
    # database exists
    # $? is 0
else
    # ruh-roh
    # $? is 1
fi

这篇关于使用Shell检查PostgreSQL中是否存在数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！