如果PostgreSQL不存在，是否模拟创建数据库？

我想通过JDBC创建一个不存在的数据库。与MySQL不同，PostgreSQL不支持create if not exists语法。实现这一目标的最佳方法是什么？

应用程序不知道数据库是否存在。它应该检查，如果数据库存在，它应该被使用。因此，连接到所需的数据库是有意义的，如果由于数据库不存在而导致连接失败，则应创建新的数据库（通过连接到默认的postgres数据库）。我检查了Postgres返回的错误代码，但我找不到任何相同的相关代码。

实现这一点的另一种方法是连接到postgres数据库，检查所需的数据库是否存在，并采取相应的措施。第二个是有点乏味的工作。

有没有办法在Postgres中实现这个功能？

221894 次浏览
小开
最佳答案

限制

您可以询问系统编目pg_database-可从同一数据库集群中的任何数据库访问。棘手的部分是CREATE DATABASE只能作为单个语句执行。手册：

CREATE DATABASE不能在事务块内执行。

因此，它不能直接在函数或DO语句中运行，而应该隐式地在事务块中运行。SQL过程，由Postgres 11引入，这个也帮不上忙。。

psql内部的解决方法

您可以在psql中通过有条件地执行DDL语句来解决此问题：

SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec

手册：

\gexec

将当前查询缓冲区发送到服务器，然后将查询输出（如果有）的每一行的每一列视为要执行的SQL语句。

Shell中的解决方法

使用\gexec，您只需调用psql曾经：

echo "SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec" | psql

您的连接可能需要更多的psql选项。角色、端口、密码..参见：

不能使用psql -c "SELECT ...\gexec"调用相同的命令，因为\gexec是psql元命令，并且-c选项需要手册规定：的单个命令指挥

command必须是完全可由服务器解析的命令字符串（即，它不包含特定于PSQL的功能），或者是单个反斜杠命令。因此，您不能在-c选项中混合使用SQL和psql元命令。

Postgres事务中的解决方法

您可以使用dblink连接返回到当前数据库，该数据库在事务块外部运行。因此，效果也不能回滚。

为此安装附加模块dblink（每个数据库一次）：

然后：

DO
$do$
BEGIN
IF EXISTS (SELECT FROM pg_database WHERE datname = 'mydb') THEN
RAISE NOTICE 'Database already exists';  -- optional
ELSE
PERFORM dblink_exec('dbname=' || current_database()  -- current db
, 'CREATE DATABASE mydb');
END IF;
END
$do$;

同样，您可能需要更多用于连接的psql选项。请看Ortwin的补充回答：

dblink的详细解释：

您可以将其设置为可重复使用的函数。

小开

我不得不使用稍微扩展的版本@Erwin Brandstetter使用：

DO
$do$
DECLARE
_db TEXT := 'some_db';
_user TEXT := 'postgres_user';
_password TEXT := 'password';
BEGIN
CREATE EXTENSION IF NOT EXISTS dblink; -- enable extension
IF EXISTS (SELECT 1 FROM pg_database WHERE datname = _db) THEN
RAISE NOTICE 'Database already exists';
ELSE
PERFORM dblink_connect('host=localhost user=' || _user || ' password=' || _password || ' dbname=' || current_database());
PERFORM dblink_exec('CREATE DATABASE ' || _db);
END IF;
END
$do$

我必须启用dblink扩展，此外还必须为DBLINK提供凭据。 适用于Postgres 9.4。

小开

另一种方法是，万一您想要一个shell脚本，如果数据库不存在，它就会创建数据库，否则就保持原样：

psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'my_db'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE my_db"

我发现这在DevOps配置脚本中很有帮助，您可能希望在同一实例上运行多次。

对于那些想要解释的人：

-c = run command in database session, command is given in string
-t = skip header and footer
-q = silent mode for grep
|| = logical OR, if grep fails to find match run the subsequent command
小开

如果你不关心数据，您可以先删除数据库，然后重新创建它：

DROP DATABASE IF EXISTS dbname;
CREATE DATABASE dbname;
小开

PostgreSQL不支持CREATE DATABASE语句的IF NOT EXISTS。它仅在CREATE SCHEMA中受支持。此外，CREATE DATABASE不能在事务中发出，因此它不能在具有异常捕获的DO块中。

当发出CREATE SCHEMA IF NOT EXISTS并且模式已经存在时，将引发带有重复对象信息的通知（不是错误）。

为了解决这些问题，您需要使用dblink扩展，它打开一个到数据库服务器的新连接，并在不进入事务的情况下执行查询。可以通过提供空字符串来重用连接参数。

下面是PL/pgSQL代码，它以与CREATE SCHEMA IF NOT EXISTS中相同的行为完全模拟CREATE DATABASE IF NOT EXISTS。通过dblink调用CREATE DATABASE，捕捉duplicate_database异常（数据库已存在时发出），并将其转换为通知，传播errcode。字符串消息附加了, skipping，其方式与CREATE SCHEMA IF NOT EXISTS的方式相同。

CREATE EXTENSION IF NOT EXISTS dblink;


DO $$
BEGIN
PERFORM dblink_exec('', 'CREATE DATABASE testdb');
EXCEPTION WHEN duplicate_database THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;

与其他解决方案一样，此解决方案没有任何争用条件，其中数据库可以由外部进程（或相同脚本的其他实例）在检查数据库是否存在和其自身创建之间创建。

此外，当CREATE DATABASE失败，并且除了数据库之外的其他错误已经存在时，则该错误被传播为错误，并且不会被静默地丢弃。只有duplicate_database错误的捕获。所以它的行为就像IF NOT EXISTS一样。

您可以将此代码放入自己的函数中，直接或从事务中调用它。只是回滚（恢复已删除的数据库）不起作用。

测试输出（通过DO调用两次，然后直接调用）：

$ sudo -u postgres psql
psql (9.6.12)
Type "help" for help.


postgres=# \set ON_ERROR_STOP on
postgres=# \set VERBOSITY verbose
postgres=#
postgres=# CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION
postgres=# DO $$
postgres$# BEGIN
postgres$# PERFORM dblink_exec('', 'CREATE DATABASE testdb');
postgres$# EXCEPTION WHEN duplicate_database THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $$;
DO
postgres=#
postgres=# CREATE EXTENSION IF NOT EXISTS dblink;
NOTICE:  42710: extension "dblink" already exists, skipping
LOCATION:  CreateExtension, extension.c:1539
CREATE EXTENSION
postgres=# DO $$
postgres$# BEGIN
postgres$# PERFORM dblink_exec('', 'CREATE DATABASE testdb');
postgres$# EXCEPTION WHEN duplicate_database THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $$;
NOTICE:  42P04: database "testdb" already exists, skipping
LOCATION:  exec_stmt_raise, pl_exec.c:3165
DO
postgres=#
postgres=# CREATE DATABASE testdb;
ERROR:  42P04: database "testdb" already exists
LOCATION:  createdb, dbcommands.c:467
小开

只需使用createdb CLI工具创建数据库：

PGHOST="my.database.domain.com"
PGUSER="postgres"
PGDB="mydb"
createdb -h $PGHOST -p $PGPORT -U $PGUSER $PGDB

如果数据库存在，它将返回一个错误：

createdb: database creation failed: ERROR:  database "mydb" already exists
小开

如果可以使用shell，请尝试

psql -U postgres -c 'select 1' -d $DB &>dev/null || psql -U postgres -tc 'create database $DB'

我认为psql -U postgres -c "select 1" -d $DB比SELECT 1 FROM pg_database WHERE datname = 'my_db'更容易，而且只需要一种报价，更容易与sh -c结合。

我在我的Ansible任务中使用这个

- name: create service database
shell: docker exec postgres sh -c '{ psql -U postgres -tc "SELECT 1" -d \{\{service_name}} &> /dev/null && echo -n 1; } || { psql -U postgres -c "CREATE DATABASE \{\{service_name}}"}'
register: shell_result
changed_when: "shell_result.stdout != '1'"
小开

在我看来，在通读了所有这些复杂的解决方案之后，由于缺少Postgres用户创建的“如果不存在”选项，这些解决方案是可怕的工作，我几乎忘记了有一种简单的方法可以在shell级别处理它。尽管这可能不是一些人想要的，但我认为很多人想要简单，而不是创建程序和复杂的结构。

我正在使用Docker，下面是我的bash脚本中的重要片段，该脚本在devsetup中加载数据：

execute_psql_command_pipe () {
$DOCKER_COMMAND exec -it $POSTGRES_CONTAINER bash -c "echo \"$1\"| psql -h localhost -U postgres || echo psql command failed - object likely exists"
}


read -r -d '' CREATE_USER_COMMANDS << EOM
create user User1 WITH PASSWORD 'password';
create user User2 WITH PASSWORD 'password';
EOM


execute_psql_command_pipe "$CREATE_USER_COMMANDS"
它

有一些错误，但这是我能找到的让它做我想做的最简单的方法：在脚本的第一遍创建，在存在的第二遍继续。 顺便说一下，虽然没有显示echo输出，但是命令继续执行，因为echo命令以0退出。

可以对任何命令（如db create）执行相同

的操作。 对于可能发生的任何其他错误，这显然会失败（或成功，具体取决于角度），但您可以获得psql输出打印机，以便可以添加更多处理。

小开

最好的方法就是运行SQL.

CREATE DATABASE MY_DATABASE;

如果数据库已经存在，则抛出“数据库已存在”错误。你可以做任何你想做的事情，否则它会创建数据库。我认为它不会在您的数据库上创建新的数据库。

小开

我最后使用了一个简单干净的方法：

createdb $DATABASE 2> /dev/null || echo "database already exists"

如果您预期的是database "x" already exists以外的其他错误，这显然是行不通的（例如，权限被拒绝）。在任何情况下，如果这是一个问题，人们总是可以在这一点之前执行这样的检查。

不要忘记设置DATABASE的值，并传入createdb命令所需的开关。最好你也可以这样做：

export PGHOST=localhost
export PGUSER=user
export PGPASSWORD=p455w0rd
...

提交答案