Имитировать CREATE DATABASE, ЕСЛИ НЕ СУЩЕСТВУЕТ ДЛЯ PostgreSQL?

Я хочу создать базу данных, которая не существует через JDBC. В отличие от MySQL, PostgreSQL не поддерживает синтаксис create if not exists. Каков наилучший способ сделать это?

Приложение не знает, существует ли база данных или нет. Он должен проверять, и если база данных существует, она должна использоваться. Поэтому имеет смысл подключиться к требуемой базе данных, и если соединение не удается из-за отсутствия базы данных, оно должно создать новую базу данных (путем подключения к базе данных по умолчанию postgres). Я проверил код ошибки, возвращенный Postgres, но я не смог найти соответствующий код, который тот же вид.

Другим способом достижения этой цели будет подключение к базе данных postgres и проверка наличия нужной базы данных и принятие соответствующих мер. Второй - немного утомительно для разработки.

Есть ли способ достичь этой функциональности в Postgres?

Ответ 1

ограничения

Вы можете запросить системный каталог 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.

Обходной путь из оболочки

С \gexec вам нужно вызвать psql только один раз:

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

Вам может понадобиться больше опций psql для вашего соединения; роль, порт, пароль,... Смотрите:

Запустите пакетный файл с помощью команды psql без пароля

То же самое нельзя вызвать с помощью psql -c "SELECT...\gexec" поскольку \gexec является мета-командой psql, а опция -c ожидает одну команду, для которой в руководстве указано:

command должна быть либо командной строкой, которая полностью разбирается сервером (т.е. не содержит специфичных для psql функций), либо отдельной командой обратной косой черты. Таким образом, вы не можете смешивать команды SQL и psql meta -c в опции -c.

Обходной путь из транзакции Postgres

Вы можете использовать соединение dblink обратно к текущей базе данных, которая работает за пределами блока транзакции. Поэтому эффекты также не могут быть отменены.

Для этого установите дополнительный модуль dblink (один раз для каждой базы данных):

Как использовать (установить) dblink в PostgreSQL?

Затем:

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 для соединения. Смотрите Ортвин добавил ответ:

Имитировать создание базы данных, если не существует для PostgreSQL?

Подробное объяснение для dblink:

Как мне делать большие неблокирующие обновления в PostgreSQL?

Вы можете сделать это функцией для повторного использования.

Ответ 2

другой вариант, на всякий случай, если вы хотите иметь оболочку script, которая создает базу данных, если она не существует, и в противном случае просто сохраняет ее как есть:

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, которые вы можете запускать несколько раз в одном экземпляре.

Ответ 3

Мне пришлось использовать слегка расширенную версию @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.

Ответ 4

Если вас не волнуют данные, вы можете сначала удалить базу данных, а затем воссоздать ее:

DROP DATABASE IF EXISTS dbname;
CREATE DATABASE dbname;

Ответ 5

PostgreSQL не поддерживает IF NOT EXISTS для оператора CREATE DATABASE. Поддерживается только в CREATE SCHEMA. Более того, CREATE DATABASE не может быть выдан в транзакции, поэтому он не может быть в блоке DO с перехватом исключений.

Когда выдается CREATE SCHEMA IF NOT EXISTS и схема уже существует, тогда появляется уведомление (не об ошибке) с дублирующейся информацией об объекте.

Для решения этих проблем необходимо использовать расширение dblink которое открывает новое соединение с сервером базы данных и выполняет запрос, не вступая в транзакцию. Вы можете повторно использовать параметры подключения, указав пустую строку.

Ниже приведен код PL/pgSQL который полностью имитирует CREATE DATABASE IF NOT EXISTS с таким же поведением, как в CREATE SCHEMA IF NOT EXISTS. Он вызывает CREATE DATABASE через dblink, перехватывает исключение 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

Ответ 6

Обновление до PostgreSQL 9.5 или выше. Если (не) существует было введено в версии 9.5.