Как удалить базу данных PostgreSQL, если к ней есть активные подключения?
Мне нужно написать скрипт, который удалит базу данных PostgreSQL. С ним может быть много связей, но сценарий должен это игнорировать.
Стандарт DROP DATABASE db_name запрос не работает при открытых соединениях.
Как я могу решить проблему?
10 ответов
Это приведет к удалению существующих подключений, кроме вашего:
запрос pg_stat_activity и получите значения pid, которые вы хотите убить, затем выполните команду SELECT pg_terminate_backend(pid int) им.
PostgreSQL 9.2 и выше:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
AND pid <> pg_backend_pid();
PostgreSQL 9.1 и ниже:
SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
AND procpid <> pg_backend_pid();
После того, как вы отключите всех, вам придется отключить и выполнить команду DROP DATABASE из соединения с другой базой данных, а не той, которую вы пытаетесь сбросить.
Обратите внимание на переименование procpid столбец к pid, Смотрите эту ветку списка рассылки.
В PostgreSQL 9.2 и выше отключить все, кроме сеанса, от базы данных, к которой вы подключены:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid();
В старых версиях тоже самое, просто поменяй pid в procpid, Чтобы отключиться от другой базы данных просто измените current_database() на имя базы данных, от которой вы хотите отключить пользователей.
Вы можете захотеть REVOKE CONNECT непосредственно от пользователей базы данных, прежде чем отключать пользователей, иначе пользователи просто продолжат повторное подключение, и у вас никогда не будет возможности удалить БД. Посмотрите этот комментарий и вопрос, с которым он связан, как мне отсоединить всех других пользователей от базы данных.
Если вы просто хотите отключить незанятых пользователей, посмотрите этот вопрос.
Предстоящий PostgreSQL 13 представит FORCE вариант.
DROP DATABASE удаляет базу данных... Кроме того, если кто-либо еще подключен к целевой базе данных, эта команда завершится ошибкой, если вы не используете параметр FORCE, описанный ниже.
СИЛА
Попытка разорвать все существующие подключения к целевой базе данных. Он не завершается, если в целевой базе данных присутствуют подготовленные транзакции, активные слоты логической репликации или подписки.
DROP DATABASE db_name WITH (FORCE);
Я просто перезапускаю сервис в Ubuntu, чтобы отключить подключенных клиентов.
sudo service postgresql stop
sudo service postgresql start
psql
DROP DATABASE DB_NAME;
Вы можете прервать все соединения перед удалением базы данных, используя pg_terminate_backend(int) функция.
Вы можете получить все работающие бэкэнды, используя системное представление pg_stat_activity
Я не совсем уверен, но следующее, вероятно, убьет все сессии:
select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'doomed_database'
Конечно, вы не можете быть подключены к этой базе данных
В зависимости от вашей версии postgresql вы можете столкнуться с ошибкой, которая делает pg_stat_activity чтобы пропустить активные соединения от сброшенных пользователей. Эти соединения также не показаны внутри pgAdminIII.
Если вы выполняете автоматическое тестирование (в котором вы также создаете пользователей), это может быть вероятным сценарием.
В этом случае вам нужно вернуться к таким запросам, как:
SELECT pg_terminate_backend(procpid)
FROM pg_stat_get_activity(NULL::integer)
WHERE datid=(SELECT oid from pg_database where datname = 'your_database');
ПРИМЕЧАНИЕ: в 9.2+ у вас будут изменения procpid в pid,
PostgreSQL 9.2 и выше:
SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = 'YOUR_DATABASE_NAME_HERE'
Я заметил, что postgres 9.2 теперь вызывает pid столбца, а не procpid.
Я склонен называть это из оболочки:
#!/usr/bin/env bash
# kill all connections to the postgres server
if [ -n "$1" ] ; then
where="where pg_stat_activity.datname = '$1'"
echo "killing all connections to database '$1'"
else
echo "killing all connections to database"
fi
cat <<-EOF | psql -U postgres -d postgres
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
${where}
EOF
Надеюсь, что это полезно. Спасибо @JustBob за sql.
Вот мой хак... =D
# Make sure no one can connect to this database except you!
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "UPDATE pg_database SET datallowconn=false WHERE datname='<DATABASE_NAME>';"
# Drop all existing connections except for yours!
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<DATABASE_NAME>' AND pid <> pg_backend_pid();"
# Drop database! =D
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "DROP DATABASE <DATABASE_NAME>;"
Я поставил этот ответ, потому что включить команду (выше), чтобы заблокировать новые подключения и потому, что любая попытка с командой...
REVOKE CONNECT ON DATABASE <DATABASE_NAME> FROM PUBLIC, <USERS_ETC>;
... не работает, чтобы заблокировать новые подключения!
Спасибо @araqnid @GoatWalker! = D
/questions/15225437/postgresql-vremenno-otklyuchit-soedineniya/15225455#15225455
В командной строке Linux я бы сначала остановил все запущенные процессы postgresql, связав эту команду sudo /etc/init.d/postgresql restart
введите команду bg, чтобы проверить, работают ли другие процессы postgresql
затем следует dropdb dbname для удаления базы данных
sudo /etc/init.d/postgresql restart
bg
dropdb dbname
Это работает для меня в командной строке Linux
В моем случае мне пришлось выполнить команду, чтобы сбросить все соединения, включая мое активное соединение администратора
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
который разорвал все соединения и покажет мне фатальное сообщение об ошибке:
FATAL: terminating connection due to administrator command SQL state: 57P01
После этого можно было сбросить базу данных
В Windows вы можете открыть Диспетчер задач -> Службы, найти процесс "postgresql" и перезапустить его. Когда служба снова запустится, базу данных можно будет удалить.
У меня ничего не сработало, кроме того, что я вошел в систему с помощью pgAdmin4, а на панели инструментов я отключил все соединения, кроме pgAdmin4, а затем смог переименовать, щелкнув правой кнопкой мыши в базе данных и свойствах и набрав новое имя.