PostgreSQL: шпаргалка администратора — базы, права, размеры, запросы, WAL

Команды и запросы, которые помогают в администрировании PostgreSQL. SQL-запросы выполняются в psql или любом клиенте, комментарии в них пишутся через --. Вместо условных имён (database_name, table_name, user_name) подставьте свои.

Конфигурация

Где лежат файлы настроек:

sudo su - postgres
psql -c "SHOW hba_file;"                    # путь к pg_hba.conf
psql -U postgres -c 'SHOW config_file'      # путь к postgresql.conf

Применить изменения настроек без перезапуска — любым из способов:

pg_ctl reload
kill -HUP $(head -1 $PGDATA/postmaster.pid)
psql -c "select pg_reload_conf();"

Проверить pg_hba.conf: запрос покажет правила так, как их понял сервер, и строки с ошибками:

table pg_hba_file_rules;

Команды psql

\l           список баз
\c dbname    подключиться к другой базе
\conninfo    к какой базе и под кем подключены
\du          роли и их права
\dt          таблицы схемы public
\dt public.* таблицы конкретной схемы
\dt *.*      таблицы всех схем
\timing      показывать время выполнения запросов
\gx          выполнить запрос с вертикальным выводом

Базы данных

select datname, datistemplate, datallowconn, datconnlimit from pg_database;  -- список баз и ограничения
select pg_size_pretty(pg_database_size('database_name'));                   -- размер базы
alter database test rename to db;            -- переименовать базу
alter database db connection limit 10;       -- максимум подключений к базе
drop database if exists db;                  -- удалить базу без ошибки, если её нет

Размеры таблиц и схем

select pg_size_pretty(pg_table_size('table_name'));           -- таблица без индексов
select pg_size_pretty(pg_indexes_size('table_name'));         -- только индексы
select pg_size_pretty(pg_total_relation_size('table_name'));  -- таблица вместе с индексами

Размер каждой схемы и её доля от всей базы:

SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;

Размер всех таблиц одной схемы:

SELECT pg_size_pretty(sum(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint) FROM pg_tables
WHERE schemaname = 'schema_name';

Десять самых больших временных таблиц:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') 
AND nspname LIKE '%pg_temp%'
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 10;

Владельцы таблиц

Таблицы, принадлежащие пользователю:

select * from pg_tables where tableowner = 'user_name';

Сменить владельца всех таблиц схемы. Запрос генерирует готовые команды ALTER TABLE; в psql их можно сразу выполнить, если заменить точку с запятой в конце на \gexec:

select 'ALTER TABLE ' || t.schemaname || '.' || t.tablename || ' OWNER TO "new_owner";' from pg_tables t where t.schemaname = 'schemaname';

Пользователи и права

Права пользователя на таблицы и права на конкретную таблицу:

SELECT grantor, grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'myuser';
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'table_name';

Создать пользователя и выдать ему роль:

CREATE USER "va.pupkin" WITH PASSWORD 'сложный_пароль';
GRANT role_name TO "va.pupkin";

Выдать и отозвать права:

grant all privileges on database database_name to "user_name";     -- все права на базу
revoke all privileges on database database_name from "user_name";  -- отозвать права на базу
GRANT ALL PRIVILEGES ON TABLE table_name TO user_name;             -- все права на таблицу
ALTER USER "user_name" WITH SUPERUSER;                             -- сделать суперпользователем

Заблокировать и разблокировать вход, проверить, может ли роль входить (t — может, f — нет):

ALTER USER someone WITH NOLOGIN;
ALTER USER someone WITH LOGIN;
select rolcanlogin from pg_roles where rolname = 'database_user';

Параметры, заданные пользователю, и их изменение:

select usename, useconfig from pg_shadow;           -- параметры пользователей
ALTER USER "username" SET ENABLE_SEQSCAN TO ON;     -- задать параметр
ALTER USER "username" RESET ENABLE_SEQSCAN;         -- сбросить параметр

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

SELECT * FROM pg_stat_activity WHERE state = 'active';                              -- все активные запросы
select client_addr, pid, usename, query, query_start, state_change from pg_stat_activity where state = 'active';
SELECT pid, query, query_start FROM pg_stat_activity WHERE usename = 'user' and state = 'active';
SELECT pid, usename, client_addr FROM pg_stat_activity where datname = 'BaseName';  -- кто подключён к базе
SELECT datname FROM pg_stat_activity WHERE usename = 'app-user';                    -- к каким базам подключён пользователь

Остановить запрос по номеру процесса. pg_cancel_backend отменяет только текущий запрос, pg_terminate_backend закрывает всё подключение:

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

Функции

Посмотреть код функции и выгрузить его в файл из psql:

SELECT pg_get_functiondef(p.oid), p.prosrc FROM pg_proc p WHERE proname = 'func_name';
SELECT pg_catalog.pg_get_functiondef('function_name'::regproc) \g /tmp/function_name.sql

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

Структура всех баз и ролей без данных и её восстановление:

pg_dumpall -s > dump.dmp
psql -f dump.dmp

Перенести одну схему в другую базу или на другой сервер:

pg_dump --schema='schema_name' "Database_Name" > /tmp/schema_name.sql
psql -d "Database_Name" -f /tmp/schema_name.sql

Ошибка option "locale" not recognized при восстановлении появляется, когда дамп из новой версии PostgreSQL загружают в старую. Помогает заменить параметр в файле дампа:

sed 's/LOCALE/LC_COLLATE/' /tmp/1.dmp > /tmp/2.dmp

WAL и репликация

select * from pg_ls_waldir() LIMIT 100;     -- файлы в каталоге WAL
select * from pg_current_wal_insert_lsn();  -- текущая позиция записи WAL
SELECT 'AA39/4C451F48'::pg_lsn - 'AA39/3B98D330'::pg_lsn;  -- разница между позициями в байтах
select * FROM pg_stat_bgwriter \gx

Состояние репликации: на основном сервере и на реплике:

select * from pg_stat_replication;   -- на основном сервере
select * from pg_stat_wal_receiver;  -- на реплике

Слоты репликации. Неиспользуемый слот удерживает WAL на диске, и каталог может разрастись до заполнения диска:

select * from pg_replication_slots;
SELECT pg_drop_replication_slot('slot_name');

Публикации логической репликации:

select * from pg_catalog.pg_publication;
select * from pg_publication_tables;

Обслуживание

Очистка и сбор статистики для планировщика с подробным отчётом. Обычный VACUUM, в отличие от VACUUM FULL, не блокирует таблицу:

VACUUM (VERBOSE, ANALYZE) schema."table";

Две последние записи таблицы по полю с датой:

select * from "table_name" order by data desc limit 2;

PgBouncer: хеш пароля для userlist.txt

Для авторизации md5 в PgBouncer нужна строка «md5» и хеш от пароля, склеенного с именем пользователя (сначала пароль, потом имя):

echo -n "md5"; echo -n "passworduser" | md5sum | awk '{print $1}'