PostgreSQL

psql, мета-команды, роли, бэкапы pg_dump и поиск медленных запросов.

Подключение

Подключиться к базе

psql -h <host> -p <port> -U <user> -d <database>

Подключиться по connection string

psql "postgresql://<user>@<host>:<port>/<database>"

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

psql -h <host> -U <user> -d <database> -c "SELECT now();"

Выполнить SQL-файл

psql -h <host> -U <user> -d <database> -f script.sql

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

Список баз

\l

Переключиться на другую базу

\c <database>

Список таблиц

\dt

Структура таблицы

\d+ <table>

Список ролей

\du

Расширенный вывод (удобно для широких строк)

\x auto

Показывать время выполнения запросов

\timing on

Роли и права

Создать пользователя

CREATE ROLE <user> WITH LOGIN PASSWORD 'password';

Создать базу с владельцем

CREATE DATABASE <database> OWNER <user>;

Права на чтение всех таблиц схемы

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user>;

Бэкап и восстановление

Дамп базы в SQL

pg_dump -h <host> -U <user> -d <database> > <database>.sql

Дамп в custom-формате

pg_dump -h <host> -U <user> -d <database> -Fc -f <database>.dump

Custom-формат сжат и позволяет восстанавливать выборочно через pg_restore.

Восстановить custom-дамп в 4 потокапродвинутая

pg_restore -h <host> -U <user> -d <database> -j 4 --no-owner <database>.dump

Диагностика

Активные запросы

SELECT pid, state, now() - query_start AS duration, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC;

Отменить запрос

SELECT pg_cancel_backend(<pid>);

Завершить соединение

SELECT pg_terminate_backend(<pid>);

Размер таблиц с индексамипродвинутая

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Кто кого блокируетпродвинутая

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

План выполнения с реальными цифрамипродвинутая

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM <table> WHERE id = 1;