Администрирование PostgreSQL, часть 2 - Работа с psql и DBeaver

Вторая часть курса по PostgreSQL - сегодня разбираемся с двумя основными инструментами администратора: консольным psql и графическим DBeaver 🐧.

Предисловие

В первой части мы подняли PostgreSQL в Docker, подключились к нему и выполнили SELECT version(); с помощью двух инструментов: psql и DBeaver.

psql - это полноценная консольная среда со своими мета-командами, переменными, скриптами и настройками.

DBeaver при всех своих визуальных удобствах работает поверх того же самого протокола и того же SQL - разница в основном в том, что там, где в psql вы наберёте \dt, в DBeaver вы откроете дерево Database Navigator.

Поэтому в этом уроке везде будем рассматривать показывать по два варианта одного и того же действия: команду в psql и то же самое, но через интерфейс DBeaver.

Таблиц у нас пока нет - вымышленную схему raven создадим в одном из следующих уроков.

Вводные данные

ПО, используемое в статье:

Стенд - тот самый контейнер postgres из первой части, запущенный и доступный на 127.0.0.1:5432.

Подключение к серверу

Через psql внутри контейнера

Способ из прошлого урока - psql уже есть в образе:

cd ~/Postgres

docker compose exec postgres psql -U ivan -d raven

Через psql на хосте

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

sudo apt install -y postgresql-client

psql -h 127.0.0.1 -p 5432 -U ivan -d raven

psql спросит пароль интерактивно.

Если не хочется вводить его каждый раз, можно завести файл ~/.pgpass:

echo "127.0.0.1:5432:raven:ivan:r4ven_me" >> ~/.pgpass

chmod 600 ~/.pgpass

Проверить, к чему подключились, можно мета-командой:

\conninfo

Через pgcli на хосте

pgcli - альтернативный консольный клиент для PostgreSQL с подсветкой синтаксиса и умным автодополнением: в отличие от psql, он понимает конкретные таблицы и столбцы именно вашей базы, а не только ключевые слова SQL. Тоже есть в стандартных репозиториях Debian:

sudo apt install -y pgcli

Подключаемся - синтаксис похож на psql, но понимает и строку подключения в виде URI:

pgcli -h 127.0.0.1 -p 5432 -U ivan -d raven

pgcli postgresql://ivan@127.0.0.1:5432/raven

Пароль подхватится из уже настроенного выше ~/.pgpass - отдельно ничего заводить не нужно.

Мета-команды те же, что и в psql (\dt, \d, \l и так далее) - pgcli построен поверх той же логики подключения, просто добавляет автодополнение по Tab, подсветку синтаксиса и многострочный ввод из коробки.

В дальнейшем мы будем пользоваться актуальной версией psql из контейнера с сервером Postgres:

docker compose exec postgres psql -U ivan -d raven

Справка

Если забыли команду - psql подскажет сам:

\?           -- список мета-команд psql
\? variables -- список переменных psql
\h           -- список SQL-команд
\h SELECT    -- справка по конкретной SQL-команде

man psql

В DBeaver прямого аналога \h нет, но при наборе SQL включается автодополнение с описанием синтаксиса (Ctrl+Space), а полную документацию по PostgreSQL DBeaver открывает по F1 на курсоре над командой.

Информация об объектах БД

Самые частые мета-команды для определения рабочей среды:

\l      -- список баз данных
\du     -- список ролей (синоним \dg)
\dn     -- список схем
\dt     -- список таблиц текущей схемы (пока пусто)
\df     -- список функций
\d pg_type    -- структура конкретного объекта
\d+ pg_type   -- то же самое, но с размером на диске

В DBeaver всё то же самое - без единой строчки SQL. В панели Database Navigator разворачиваем raven → Schemas → public, и там уже лежат отдельные ветки Tables, Views, Functions, Sequences. Двойной клик по любому объекту откроет вкладку с его структурой, а вкладка DDL этой же формы покажет тот самый CREATE TABLE, который бы сгенерировал \d+ вручную.

Форматирование вывода

По умолчанию psql выводит результат таблицей. Если столбцов много и строка не влезает в терминал - выручает расширенный формат:

\x
SELECT * FROM pg_stat_activity LIMIT 1;

Вывод превращается из таблицы в список “столбец: значение” по одной записи. Есть и одноразовый вариант, без переключения режима на всю сессию:

SELECT * FROM pg_stat_activity LIMIT 1 \gx

Прочие полезные переключатели:

\a          -- вкл/выкл выравнивание столбцов
\t          -- вкл/выкл заголовки и итоговую строку "(N rows)"
\timing on  -- показывать время выполнения каждого запроса
\pset       -- полный список параметров форматирования

В DBeaver результат запроса живёт во вкладке Grid (таблица) или Text (простой текст) - переключаются кнопками внизу панели результата. Время выполнения показывается автоматически в статус-баре после каждого запроса, аналог \timing включён по умолчанию. А чтобы посмотреть одно длинное значение целиком - вместо \x в DBeaver используется панель Value Viewer (открывается снизу или отдельным окном при клике на ячейку).

Переменные и подстановка значений

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

\set my_limit 5
SELECT * FROM pg_stat_activity LIMIT :my_limit;

\echo :my_limit
\unset my_limit

Можно и наоборот - забрать результат запроса в переменную:

SELECT now() AS ts \gset
\echo :ts

Импорт переменной окружения хоста:

\getenv pg_ver PG_VERSION
\echo :pg_ver

Прямого аналога переменных psql в DBeaver нет, но похожая задача решается через параметризацию запроса - если в SQL-редакторе написать :my_limit или ${my_limit}, DBeaver перед выполнением сам предложит окно для ввода значения (Bind parameters).

Выполнение скриптов и вывод в файл

Выполнить запрос из файла:

\i /path/to/script.sql

Отправить результат в файл вместо экрана:

\o /tmp/result.txt
SELECT * FROM pg_roles;
\o

Передать вывод во внешнюю команду ОС:

SELECT datname FROM pg_database \g | sort

Выполнить произвольную команду ОС без выхода из psql:

\! df -h

\! echo 'SELECT current_date;' > /tmp/script.sql

\! cat /tmp/script.sql

\i /tmp/script.sql

А \gexec - отдельный трюк: выполняет не сам запрос, а его результат как SQL. Удобно, когда нужно сгенерировать и тут же применить набор команд:

SELECT 'ANALYZE ' || tablename || ';' FROM pg_tables WHERE schemaname = 'pg_catalog' LIMIT 3 \gexec

В DBeaver сценарий из нескольких SQL-команд выполняется через Execute SQL Script (Alt+X) - в отличие от одиночного Execute SQL Statement (Ctrl+Enter), он прогоняет весь открытый файл целиком, как \i. Результат любого запроса можно сохранить в файл через кнопку Export data над таблицей результатов - мастер экспорта умеет CSV, JSON, SQL-инсерты, Excel и ещё десяток форматов.

Персонализация: ~/.psqlrc

Файл ~/.psqlrc выполняется автоматически при каждом запуске psql - удобно вынести туда привычные настройки:

cat > ~/.psqlrc << EOF
\set PROMPT1 '%n@%/%R%x%# '
\set PROMPT2 '%n@%/%R%x%# '
\setenv PSQL_PAGER 'less -XS'
\timing on
EOF

systemctl --user restart postgres

Аналог в DBeaver ищем в Window → Preferences → Editors → SQL Editor: там настраиваются автокоммит, размер выборки по умолчанию, план выполнения, подсветка и форматирование SQL - вся та же личная настройка среды, только через диалоговые окна, а не текстовый файл.

Транзакции и обработка ошибок

По умолчанию psql работает в режиме автокоммита - каждая команда фиксируется сразу. Отключить:

\set AUTOCOMMIT off

Внутри явной транзакции ошибка обычно рвёт всё до ROLLBACK:

BEGIN;
SELECT 1 / 0; -- ошибка
SELECT 1;     -- уже не выполнится, транзакция прервана
ROLLBACK;

Режим ON_ERROR_ROLLBACK спасает от полного отката - под капотом psql расставляет SAVEPOINT перед каждой командой и откатывается только к нему:

\set ON_ERROR_ROLLBACK on

BEGIN;
SELECT 1 / 0; -- ошибка, но транзакция жива
SELECT 1;     -- а это уже выполнится
COMMIT;

В DBeaver автокоммит переключается одной кнопкой на панели SQL-редактора (Auto-commit / Manual), а ручное управление транзакцией - соседними кнопками Commit и Rollback. Собственного аналога ON_ERROR_ROLLBACK там нет: ошибка в одном из statement’ов ручной транзакции всё так же требует Rollback или явного SAVEPOINT в самом SQL.

Возможные проблемы

• вывод “ломается” на длинных строках или обрезается пейджером

WARNING: terminal is not fully functional

Обычно проявляется при подключении через минимальный терминал (например, cron или в CI). Решение - отключить пейджер на сессию:

\pset pager off

• DBeaver не видит русские данные / кракозябры в результатах

Почти всегда дело в кодировке соединения. Проверить фактическую кодировку сервера:

SHOW server_encoding;
SHOW client_encoding;

Обе должны быть UTF8 - это кодировка по умолчанию у официального образа postgres, так что в норме проблема возникает только если её явно поменяли при создании подключения в DBeaver (Connection settings → PostgreSQL → Client encoding).

• не хватает прав на объект в Database Navigator

DBeaver молча показывает пустое дерево вместо ошибки, если у пользователя нет прав на схему. У нас пока единственный пользователь - суперпользователь ivan, так что в рамках этого курса проблема не всплывёт, но запомните симптом - про права и роли будет отдельный урок.

Послесловие

psql очень мощный инструмент - фактически это маленький язык сценариев вокруг SQL. DBeaver в этом смысле честно закрывает те же задачи, просто использует для этого графический интерфейс. Уметь работать с этими двумя вариантами считаю полезным: psql актуалне в консоли, если нужен оперативный доступ к БД, например по SSH на сервере без GUI, а DBeaver - конечно удобнее для повседневной работы с базами, включая гибкость экспорта данных.

В следующем уроке переходим к внутренностям - архитектура PostgreSQL и MVCC.

Спасибо, что читаете. Успехов в изучении PostgreSQL! 🐧

Используемые материалы

• Документация: psql
• Документация: файл .pgpass
• DBeaver Community Edition
• Моя статья: установка и запуск PostgreSQL в Docker

👨‍💻Ну и…

Не забывайте про нашу телегу📱и чат 💬

Или может хотите стать соавтором? Тогда клик сюда🔗

Всех благ✌️

That should be it. If not, check the logs 🙂