PostgreSQL: команды, лайфхаки и администрирование

Основные команды psql, SQL и администрирование, резервное копирование, роли, производительность и полезные приёмы на одной странице

Подключение

Метакоманды psql

Команды, начинающиеся с обратной косой черты, работают только в psql, а не в SQL.

Базы данных и схема

Индексы

Данные: выборки, изменения, транзакции

Роли и права

Резервное копирование и восстановление

Проверка резервной копии

# Восстановить в тестовую базу и проверить количество строк
createdb appdb_check
pg_restore -d appdb_check -j 4 appdb.dump
psql -d appdb_check -At -c "SELECT count(*) FROM orders;"
dropdb appdb_check

Производительность и диагностика

Для последнего запроса нужно расширение pg_stat_statements: добавьте его в shared_preload_libraries, перезапустите сервер и выполните CREATE EXTENSION pg_stat_statements;.

Настройки сервера

Полезные параметры

Лайфхаки

1. Пароль без истории. Вместо PGPASSWORD в команде используйте файл ~/.pgpass в формате host:port:db:user:password с правами chmod 600.
2. Проверить миграцию в транзакции. Оберните скрипт в BEGIN; и в конце выполните ROLLBACK;, пока не убедитесь, что всё работает. Или используйте psql -1.
3. Остановиться при ошибке. В начале скрипта: \set ON_ERROR_STOP on. Иначе psql продолжит выполнять после сбоя.
4. Удобная запись в .psqlrc. Строка \x auto и \timing on в ~/.psqlrc сразу делают вывод читаемее и показывают время запросов.
5. Выгрузить CSV с клиента. \copy (SELECT ...) TO 'out.csv' CSV HEADER. Запрос в скобках, а не просто имя таблицы.
6. Быстрая проверка количества без блокировок. Для больших таблиц точный count(*) медленный. Оценка: SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';.
7. Идентификаторы без лишних расширений. Начиная с PostgreSQL 13, gen_random_uuid() встроен в ядро.
8. Пакетная вставка. Одна инструкция INSERT ... VALUES (...), (...), (...) на тысячи строк быстрее тысяч отдельных INSERT.
9. Очередь без дублей. FOR UPDATE SKIP LOCKED позволяет нескольким воркерам брать задачи параллельно.
10. Порог для сложных запросов. Если EXPLAIN ANALYZE показывает Seq Scan по большой таблице с редким условием, проверьте индекс и актуальность статистики через ANALYZE.
11. Сохранить вывод команды в переменную shell. COUNT=$(psql -At -d appdb -c "SELECT count(*) FROM orders;").
12. Список подключений по приложениям. SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1;. Задайте имя приложения в строке подключения, чтобы его было видно.
13. Не используйте OFFSET для глубокой пагинации. При больших смещениях запрос читает и отбрасывает строки. Используйте курсорную пагинацию по индексированному ключу: WHERE id > :last_id ORDER BY id LIMIT 50.
14. Округление времени. date_trunc('day', created_at) группирует по суткам, date_trunc('hour', ...) по часам.

Работа с JSON

-- Значение по ключу как текст
SELECT payload->>'email' FROM events;

-- Вложенный путь
SELECT payload#>>'{address,city}' FROM events;

-- Фильтр по содержимому (с GIN-индексом)
SELECT * FROM events WHERE payload @> '{"type": "signup"}';

-- Развернуть массив в строки
SELECT jsonb_array_elements_text(tags) FROM posts;

Типичные задачи одной командой

# Создать пользователя и базу для приложения
psql -U postgres -c "CREATE ROLE app LOGIN PASSWORD 'secret';"
psql -U postgres -c "CREATE DATABASE appdb OWNER app;"

# Ежедневный бэкап с датой и проверкой
pg_dump -Fc -f /backup/appdb-$(date +%F).dump appdb && echo "backup ok"

# Сколько активных подключений и лимит
psql -At -U postgres -c "SELECT count(*) FROM pg_stat_activity;" \
  -c "SHOW max_connections;"

# Выполнить SQL из файла с выводом времени
psql -d appdb -v ON_ERROR_STOP=1 -c "\timing on" -f report.sql