Работа с базой данных PostgreSQL

PostgreSQL – опенсорсная реляционная СУБД. Отличается гибкостью и надежностью, поддерживает большое количество полезных возможностей. Часто используется в проектах, где требуется работа со сложными структурами данных, с которыми не справляются простые СУБД.

В этой статье мы разберемся, как работать с PostgreSQL. В качестве примера я буду использовать Ubuntu 18.04.

Установка PostgreSQL

PostgreSQL есть в репозитории Ubuntu, поэтому установка выполняется одной командой. Но сначала нужно проверить обновления самой системы:

Для выполнения команды нужны права суперпользователя. Так что придется вспомнить пароль.

После установки апдейтов инсталлируем PostgreSQL:

PostgreSQL установится вместе с пакетом contrib, в котором содержится дополнительная функциональность, а также утилиты для работы СУБД.

Чтобы убедиться, что все работает, проверим версию:

При установке автоматически создается роль и пользователь postgres.

Настройка PostgreSQL

Работать с PostgreSQL мы будем через терминал с помощью встроенной утилиты psql. Запускаем ее следующей командой:

Можно установить сторонние инструменты для администрирования PostgreSQL, но в этом мало смысла — psql справляется со всеми основными задачами.

Чтобы получить поддержку, вводим в терминале команду:

Если нужна справка по конкретной команде, пишем:

Выйти из psql можно командой \q.

Управление пользователями

В PostgreSQL используется концепция ролей. Одну роль можно рассматривать как отдельного пользователя или как группу пользователей. Роли могут владеть объектами БД и выдавать разрешения другим ролям.

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

Система запросит имя для новой роли, пароль, а также позволит настроить привилегии — например, нужно ли давать права суперпользователя или разрешать создавать другие роли и базы данных.

Если вы уже зашли в psql, то создать новую роль можно командой:

Затем задаем пароль:

Вывести список всех ролей можно командой /du. Кроме имен отобразятся привилегии каждого роли.

Чтобы закрыть список ролей, выполняем команду q.

Для удаления пользователя выполняем команду:

Это можно также сделать из консоли системы с помощью команды:

Чтоб сменить пароль пользователя, подключаемся к psql с правами суперпользователя. Затем выполняем следующую команду:

Эта операция сохраняется в файле .psql_history вместе с паролем, который не будет зашифрован. В качестве дополнительной меры безопасности эту запись рекомендуется удалить. Файл обычно находится в директории /var/lib/postgresql.

Работа с базами данных в PostgreSQL

Создать базу данных из консоли можно следующей командой:

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

Чтобы посмотреть список всех БД, выполняем команду \l.

Для удаления базы данных используется та же команда, что и для удаления роли — drop. В терминале системы синтаксис будет таким:

В клиенте psql синтаксис похожий:

Подключение к базе данных

По умолчанию psql подключается от имени текущего пользователя Linux к БД с таким же названием. Если эти данные совпадают, достаточно выполнить запуск самого терминального клиента:

Чтобы вывести информацию о текущем соединении, выполняем команду:

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

Если имя роли не совпадает с именем пользователя в Linux, прописываем дополнительные параметры.

Имя роли и название БД совпадают:

Название базы данных отличается от имени роли:

Чтобы переключиться на другую базу данных внутри psql используем команду:

Создание резервной копии и восстановление из бэкапа

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

Чтобы было проще разобраться, рассмотрим каждый параметр:

• хост – сервер, на котором располагается БД. Например, можно указать localhost, домен, IP-адрес.
• имя_роли – имя пользователя PostgreSQL, под которым мы работаем с базой данных.
• формат_дампа – формат, в котором дамп сохранится на сервере. Доступны следующие форматы: c (custom) – архив .tar.gz, t (tar) – архив .tar, p (plain) – текст без сжатия, обычно .sql.
• путь_к_дампу – путь, по которому будет сохранена резервная копия.
• имя_БД – название БД, для которой будет создана резервная копия.

Выглядит это примерно так:

Для выполнения этой команды нужно ввести пароль, который используется при входе в psql от имени указанной роли (mybase в приведенном примере).

Восстановление из резервной копии выполняется аналогичным образом:

Параметры похожие, отличия минимальные. Важно знать хост, помнить формат и путь к бэкапу.

Мы разобрались с основными действиями и настройками PostgreSQL. На этом все!

Определение структуры данных

Создание и удаление базы данных

Создание базы данных

Для создания базы данных используется команда CREATE DATABASE , после которой указывается название базы данных.

Для выполнения запросов будем использовать графический клиент pgAdmin, хотя также можно использовать консольный клиент psql.

Чтобы создать новую базу, данных откроем pgAdmin . В левой части программы выберем какую-нибудь базу данных, например, стандартную бд postgres, и нажмем на нее правой кнопкой мыши.

В появившемся меню выберем пункт Query Tool. , и в центральной части программы откроется поле для ввода кода SQL. В это поле введем следующий код:

Для выполнения кода нажмем на значок молнии, и после этого будет создана база данных usersdb.

Чтобы увидеть нашу базу данных, нажмем в левой части на узел Databases правой кнопкой мыши и в контекстном меню выберем Refresh. :

Произойдет обновление, и мы увидем созданную базу данных.

По умолчанию база является неактивной, поэтому ее значок имеет серый цвет. Но чтобы к ней подключиться, достаточно нажать на нее и раскрыть ее узел.

Удаление базы данных

Для удаления базы данных применяется команда DROP DATABASE , после которой указывается название базы данных.

Удаляемая база данных должна быть неактивной, то есть подключение к ней должно быть закрыта.

Как удалить базу данных PostgreSQL, если есть активные подключения к ней?

Не скажу что PostgreSQL какой-то падучий или проблемный, но перебои электропитания или неполадки оборудования могут завалить что-угодно. А при починке проблем в базах PostgreSQL, я пришел к выводу что проще и быстрее всего просто задампить проблемную базу, удалить, создать заново и восстановить из дампа. Однозначно получается быстрее. Особенно если еще и автоматизировать таким скриптом:

Но статья не о починках баз и не соревнованиях по удалению/восстановлению, а том что базу вы можете и не удалить, если будут подключения к БД. А они наверняка будут! Иначе к чему тогда спешить с починкой!? Поэтому разберемся как же нам завершить все подключения.

Можно повыключать другие приложения, подключающиеся к базе, или зафаерволиться, но проще и быстрее все сделать одним единственным запросом к БД.

Сборщик статистики PostgreSQL поддерживает сбор и отчетность о деятельности сервера. Представление pg_stat_activity содержит всю необходимую информацию о процессах подключений к серверу.

Составить запрос для завершения всех подключений к БД с именем DBNAME , за исключением нашего, не составит труда:

Функция pg_terminate_backend() завершает подключения по PID процесса, который мы выбрали из представления pg_stat_activity. В условии указали интересующую базу и исключили PID «нашего» процесса.

Для PostgreSQL версии 9.1 и ниже запрос будет немножко другой:

Ну и скрипт тогда до ума доведем:

Отмечу что в скрипте мы попытаемся выполнить оба запроса: для новых и старых версий, ведь один из них выполнится успешно, а определять версию сервера больно накладно будет.

июн 2, 08:57 Дмитрий в рубрике sql .

Добавить в Twitter

Добавить в Telegram

Оставить комментарий

Привет, читатель! Здесь пишут о веб-разработке и немного на другие темы, так или иначе связанные с компьютером. Используй страницу поиска и архива для поиска нужной тебе информации.

Как удалить базу данных PostgreSQL, если есть активные подключения к ней?

Мне нужно написать script, который выведет базу данных PostgreSQL. Там может быть много соединений, но script должен игнорировать это.

Стандартный DROP DATABASE db_name запрос не работает, когда есть открытые соединения.

Как я могу решить проблему?

ОТВЕТЫ

Ответ 1

Это приведет к удалению существующих соединений, кроме ваших:

Запрос pg_stat_activity и получите значения pid, которые вы хотите убить, затем введите SELECT pg_terminate_backend(pid int) им.

PostgreSQL 9.2 и выше:

PostgreSQL 9.1 и ниже:

Как только вы отключите всех, вам придется отключить и выпустить команду DROP DATABASE из соединения из другой базы данных, а не той, которую вы пытаетесь удалить.

Обратите внимание на переименование столбца procpid на pid . См. этот список рассылки.

Ответ 2

В PostgreSQL 9.2 и выше отключить все, кроме сеанса, из базы данных, к которой вы подключены:

В старых версиях это то же самое, просто измените pid на procpid . Чтобы отключиться от другой базы данных, просто измените current_database() на имя базы данных, из которой вы хотите отключить пользователей.

Вы можете захотеть REVOKE CONNECT прямо у пользователей базы данных, прежде чем отключать пользователей, иначе пользователи просто продолжат повторное подключение, и вы никогда не получите возможность сбросить БД. См. этот комментарий и вопрос, с которым он связан, Как отключить всех других пользователей из базы данных.

Если вы просто хотите отключить простаивающих пользователей, см. этот вопрос.

Ответ 3

Вы можете убить все соединения перед удалением базы данных с помощью функции pg_terminate_backend(int) .

Вы можете получить все работающие бэкэнды, используя системный вид pg_stat_activity

Я не совсем уверен, но следующее, вероятно, убьет все сеансы:

Конечно, вы не можете подключиться к этой базе данных

Ответ 4

Я заметил, что postgres 9.2 теперь вызывает столбец pid, а не procpid.

Я обычно вызываю его из оболочки:

Надеюсь, что это полезно. Благодаря @JustBob для sql.

Ответ 5

В зависимости от вашей версии postgresql вы можете столкнуться с ошибкой, которая заставляет pg_stat_activity опускать активные соединения с отключенными пользователями. Эти соединения также не отображаются внутри pgAdminIII.

Если вы выполняете автоматическое тестирование (в котором вы также создаете пользователей), это может быть вероятным сценарием.

В этом случае вам нужно вернуться к запросам типа:

ПРИМЕЧАНИЕ. В 9.2+ у вас будет изменение procpid на pid .

Ответ 6

В командной строке Linux, я бы остановил все процессы postgresql, которые выполняются, связывая эту команду sudo/etc/init.d/postgresql restart

введите команду bg, чтобы проверить, все еще запущены другие процессы postgresql.

а затем dropdb dbname, чтобы удалить базу данных

Это работает для меня в командной строке linux

Ответ 7

PostgreSQL 9.2 и выше:

SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = ‘YOUR_DATABASE_NAME_HERE’

Ответ 8

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

. не работает, чтобы заблокировать новые подключения!

Благодаря @araqnid @GoatWalker!= D

Ответ 9

В моем случае мне пришлось выполнить команду для удаления всех подключений, включая активное соединение с администратором

который завершил все подключения и покажет мне фатальное сообщение об ошибке:

FATAL: terminating connection due to administrator command SQL state: 57P01

После этого можно было сбросить базу данных

Ответ 10

Я просто перезапустил службу в Ubuntu, чтобы отключить подключенные клиенты.

Postgresql-невозможно удалить базу данных из-за некоторых автоматических подключений к БД

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

когда я использую:

Я прекратил соединение с этой БД, но если я попытаюсь удалить базу данных после этого, кто-то автоматически подключится к этой базе данных и даст эту ошибку. Что может быть, что делать? Никто не использует эту базу данных, кроме меня.

9 ответов:

вы можете предотвратить будущие соединения:

(и, возможно, другие пользователи и роли; см. \l+ на psql )

затем вы можете завершить все подключения к этой БД, кроме собственного:

на старых версиях pid называлась procpid так что вам придется смириться с этим.

так как вы отозвали CONNECT права, все, что пытается автоматически подключиться, больше не должно быть в состоянии сделать это.

теперь вы сможете отбросить ДЕЦИБЕЛ.

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

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

сначала вам нужно отменить

это наверняка сработает.

просто проверьте, что такое соединение, откуда оно исходит. Вы можете увидеть все это в:

возможно, это ваша связь?

Я нашел решение этой проблемы попробуйте запустить эту команду в терминале

убить процесс по этой команде

если нет потенциального влияния на другие службы на вашем компьютере, просто service postgresql restart

Это означает, что другой пользователь обращается к базе данных. Просто перезапустите postgresql. Эта команда будет делать трюк

затем попробуйте удалить базу данных:

это будет делать трюк. Счастливого кодирования

pgAdmin 4 решение с помощью UI

сначала включите показать активность на приборной панели, если у вас нет:

теперь отключите все процессы, использующие БД:

1. щелкните имя БД
2. Щелкните Панель Мониторинга > Сеансы
3. Нажмите кнопку «Обновить» значок
4. щелкните значок Удалить (x) рядом с каждым процессом, чтобы завершить их

теперь должна быть возможность удалить БД.

в моем случае я использую AWS Redshift (на основе Postgres). И, кажется, нет никаких других подключений к БД, но я получаю эту же ошибку.

в моем случае кажется, что кластер баз данных все еще выполняет некоторую обработку в базе данных, и хотя нет других внешних/пользовательских подключений, база данных все еще используется внутри. Я нашел это, выполнив следующее:

так что мой Хак должен был написать цикл в моем коде, ища строки с моим именем базы данных в нем. (конечно, цикл не бесконечен, а является сонным циклом и т. д.)

если строки найдены, перейдите к удалению каждого PID, один за другим.

если строки не найдены, перейдите к удалению базы данных

Примечание: В моем случае я пишу тесты Java unit/system, где это можно считать приемлемым. Это неприемлемо для производственного кода.

вот полный Хак, в Java (игнорировать мои тестовые / служебные классы).

в терминале попробуйте эту команду:

501 1445 3645 0 12: 05AM 0: 00.03 postgres: sasha dbname [местный] холостом ходу

третий номер (3645) — PID.

вы можете удалить этот

и после этого запустите соединение PostgreSQL.