Архив метки: SQL

pg_stat_activity хранение истории запросов PostgreSQL

View pg_stat_activity позволяет посмотреть текущие запросы в PostgreSQL. Настроим автоматический сбор и хранение этой информации.

https://habr.com/ru/post/413411/
https://github.com/dbacvetkov/PASH-Viewer/wiki/How-to-create-pg_stat_activity-historical-table

Создаем таблицу pg_stat_activity_history для исторических данных:

--create table for historical data PG10+
create table pg_stat_activity_history as 
SELECT clock_timestamp() as sample_time, datname, pid, usesysid, usename, backend_type, application_name, client_hostname, client_addr, wait_event_type, wait_event, query, query_start, 1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration
from pg_stat_activity where 1=2;

--create table for historical data PG9.6
create table pg_stat_activity_history as 
SELECT clock_timestamp() as sample_time, datname, pid, usesysid, usename, application_name, client_hostname, client_addr, wait_event_type, wait_event, query, query_start, 1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration
from pg_stat_activity where 1=2;

create index pg_stat_activity_history_idx on pg_stat_activity_history(sample_time);

Читать далее

0

pgsentinel настройка (хранение истории активных сессий PostgreSQL)

Компиляция и добавление pgsentinel в PostgreSQL

Расширение pgsentinel предоставляет возможность собирать и просматривать историю активных сессий в PostgreSQL.

https://habr.com/ru/post/416909/
https://github.com/pgsentinel/pgsentinel

Для успешной компиляции библиотеки, необходимо чтобы на сервере был установлен пакет postgresql11-devel (для каждой версии PostgreSQL естественно будет свой пакет). Так же потребуются пакеты gcc, пакет openssl-devel (начиная с 10-й версии PostgreSQL) и пакеты llvm и clang (начиная с 11-й версии PostgreSQL). О том, каких пакетов не хватает в конкретном случае, можно узнать из сообщений об ошибках при компиляции.

Создадим директорию под папки с версиями pgsentinel:

mkdir /var/lib/pgsql/11/ext

Далее копируем туда папку с релизами pgsentinel (с любой доступной версией,на данный момент это beta, релиза нет, копируем репозиторий. В репозитории нас интересует диреткория src).
Заходим в директорию pgsentinel и выполняем там команду (для PG_CONFIG указать путь к нужной версии PostgreSQL):

make PG_CONFIG=/usr/pgsql-11/bin/pg_config USE_PGXS=1 install

Читать далее

1+

PostgreSQL выдача прав пользователю с учетом создаваемых в будущем объектов

Многие сталкиваются в PostgreSQL с тем, что выдав пользователю права на, например, таблицы в бд, на новые таблицы эти права не распространяются. Т.е., если мы выдаем права на update на все таблицы в схеме public следующим образом:

GRANT UPDATE ON ALL TABLES IN SCHEMA PUBLIC TO testuser;

Потом создадим в схеме новую таблицу, то пользователь не будет иметь прав выполнять update на этой новой таблице. Та же ситуация и с другими объектами бд (функции, sequence-ы и т.д.).
Для того, чтобы решить эту проблему, нужно изменить у пользователя права доступа по умолчанию ALTER DEFAULT PRIVILEGES

Допустим, мы создали нового пользователя testuser и от нас требуется выдать ему все права на схему public в БД testdb.

GRANT CONNECT ON DATABASE testdb TO testuser; --права на подключение к бд
GRANT ALL ON SCHEMA public TO testuser; --права на CREATE и USAGE
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO testuser; --права на таблицы
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO testuser; --права на sequence-ы
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO testuser; --права на функции
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TYPES TO testuser; --права на типы

Теперь testuser может создавать таблицы и другие объекты в схеме public. А так же будет иметь права на созданные другими пользователями (например, владельцем бд) объекты.
REVOKE выполняется так же.

Важно! Если при выдаче или отзыве прав указать не конкретного пользователя а роль PUBLIC, например так:

ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT SELECT ON TABLES TO PUBLIC;

То все пользователи получат права select таблиц в этой схеме.

Так же рекомендуется для безопасности отзывать все права у роли PUBLIC на всех базах:

REVOKE ALL ON schema public FROM public;
0

Репликация в PostgreSQL при помощи repmgr (настройка, switchover, failover)

Не так давно, мне довелось разбираться с настройкой репликации PostgreSQL при помощи утилиты repmgr. В процессе работы, я столкнулся с тем, что не смог найти полного гайда по настройке и выполнению switchover и failover в различных ситуациях. В итоге, я написал для себя инструкцию, надеюсь она будет полезна тем, кто решит управлять репликацией в PostgreSQL при помощи инструмента repmgr. Читать далее

3+

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