PostgreSQL управление расширениями (extensions)

Расширения PostgreSQL предоставляют дополнительный функционал. Часть расширений входит в пакет contrib и доступны в
PostgreSQL сразу после его установки (пакет свой под каждую версию PG, например postgresql11-contrib).
Расширения ставятся индивидуально в каждую БД отдельно.
Так же, можно установить и использовать расширения не входящие в contrib-пакет. Для этого их необходимо скомпилировать
под конкретную мажорную версию PG и добавить необходимые файлы в директории PG.
PostgreSQL хранит файлы расширений в директориях (директория зависит от версии):

  • в /usr/pgsql-11/share/extension — sql-файлы и control-файлы
  • в /usr/pgsql-11/lib — so-файлы

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

select * from pg_available_extensions;
\dx

Читать далее

0

PostgreSQL узнать replication lag

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

--Начиная с PG10
select
client_addr as client, usename as user, application_name as name, state,
sync_state as mode,
(pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending,
(pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write,
(pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush,
(pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay,
(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) / 1024)::int / 1024 as
total_lag
from pg_stat_replication;

--До PG10
select
client_addr as client, usename as user, application_name as name, state,
sync_state as mode,
(pg_xlog_location_diff(pg_current_xlog_location(),sent_location) /
1024)::int as pending,
(pg_xlog_location_diff(sent_location,write_location) / 1024)::int as write,
(pg_xlog_location_diff(write_location,flush_location) / 1024)::int as
flush,
(pg_xlog_location_diff(flush_location,replay_location) / 1024)::int as
replay,
(pg_xlog_location_diff(pg_current_xlog_location(),replay_location) /
1024)::int / 1024 as total_lag
from pg_stat_replication;
0

PostgreSQL статистика по пользовательским таблицам (чтения/изменения, autovacuum и т.д)

--all info
select * from pg_stat_user_tables where schemaname = 'public';

--autovacuum counts
select relname, last_autovacuum, autoanalyze_count, autovacuum_count,
last_autoanalyze,
seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd,
n_tup_del, n_dead_tup, n_mod_since_analyze
from pg_stat_user_tables where schemaname = 'public' order by
autovacuum_count desc;

Документация

0

PostgreSQL сбросить коннекты к бд

Смотрим список подключений:

SELECT pid, usename, client_addr, backend_start, query FROM
pg_stat_activity WHERE datname = 'dbname';

Сбрасываем все подключения к бд (кроме того из под которого работаем сейчас, если мы подключены к базе dbname):

SELECT pg_terminate_backend( pid ) FROM pg_stat_activity WHERE pid <>
pg_backend_pid( ) AND datname = 'dbname';
0

PostgreSQL узнать число открытых/разрешенных подключений

select max_conn,used,res_for_super,max_conn-used-res_for_super as
res_for_normal
from
(select count(*) used from pg_stat_activity) t1,
(select setting::int res_for_super from pg_settings where
name='superuser_reserved_connections') t2,
(select setting::int max_conn from pg_settings where
name='max_connections') t3;
0

PostgreSQL посмотреть текущие запросы к базам

До версии PG 9.6:

--all info
select * from pg_stat_activity;
--connections count
select datname, count(*) as connections from pg_stat_activity group by
datname;
select datname, count(*) as connections, state, waiting from
pg_stat_activity group by datname, state, waiting order by datname;
--connections not idle (active)
select datname, count(*) as connections, state, waiting from
pg_stat_activity
where state <> 'idle' and query not like '%FROM pg_stat_activity%' group by
datname, state, waiting order by datname;
--connections not idle (active) with time of longes query
select datname, count(*) as connections, state, waiting,
max(age(clock_timestamp() as age, query_start)) from pg_stat_activity
where state <> 'idle' and query not like '%FROM pg_stat_activity%' group by
datname, state, waiting order by datname;
--connections not idle (active) with time of longes query and query type
select datname, count(*) as connections, state, waiting,
max(age(clock_timestamp(), query_start)) as age, lower(left(query,
position(' ' in query) - 1)) as query_type from pg_stat_activity
where state <> 'idle' and query not like '%FROM pg_stat_activity%' group by
datname, state, waiting, lower(left(query, position(' ' in query) - 1))
order by datname;
--active with query text
select pid, datname, state, waiting, age(clock_timestamp(), query_start),
query from pg_stat_activity where state <> 'idle' and query not like '%FROM
pg_stat_activity%';

Начиная с версии PG 9.6:

Читать далее

0

PostgreSQL посмотреть права на Sequence в бд

Для того чтобы посмотреть права выданные в бд на sequences (последовательности) нужно выполнить запрос:

SELECT relname, relacl
FROM pg_class
WHERE relkind = 'S'
AND relacl is not null
AND relnamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
);

Документация
relkind: r = обычная таблица, i = индекс (index), S = последовательность (sequence), v = представление (view), m = материализованное представление (materialized view), c = составной тип (composite), t = таблица TOAST, f = сторонняя таблица (foreign)

0

PostgreSQL поиск недостающих индексов

Для анализа на недостающие индексы, в бд выполнить следующий запрос:

SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when
seq_scan-idx_scan&gt;0
THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I',
schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE pg_relation_size(format('%I.%I', schemaname,
relname)::regclass)&gt;100000 ORDER BY too_much_seq DESC;

Скрипт проверяет таблицы на предмет многочисленных полных сканирований (ислючая небольшие таблицы, содержащие менее 100 тыс записей, т.к. даже при наличии индексов, планировщик запросов PostgreSQL предпочитает выполнять полное сканирование таблицы — на малых таблицах это быстрее поиска по индексу). В случае если полных сканирований значительно больше сканирваний — стоит проанализировать таблицу и запросы к ней на предмет недостающих индексов.

0