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

PostgreSQL компиляция библиотеки libzbxpgsql и пересборка RPM-пакета

Библиотека libzbxpgsql используется для мониторинга PostgreSQL в Zabbix. Последний релиз библиотеки был в 2017 году и свежий пока не вышел, в то же время, последняя версия libzbxpgsql не работает с zabbix agent версий 4+. В официальном репозитории https://github.com/cavaliercoder/libzbxpgsql есть фикс этой проблемы https://github.com/cavaliercoder/libzbxpgsql/pull/141, фикс проблемы с DEBUG логированием zabbix агентом https://github.com/cavaliercoder/libzbxpgsql/pull/142.
Для того чтобы можно было использовать исправления, необходима виртуалка с CentOS 7+ и возможностью поставить на нее пакеты для компиляции исходников (make, gcc и т.д.).
Так же ставим пакет rpm-build, он потребуется для пересборки rpm-пакета:

yum install rpm-build

Устанавливаем последнюю доступную версию пакета libzbxpgsql (из репозитория или можно перекинуть на машину свой пакет и поставить локально):

yum install libzbxpgsql

Читать далее

0

pg_cron установка и настройка

pg_cron это расширение для PostgreSQL реализующее cron внутри СУБД, т.е. задания запускаются самой СУБД, и синхронизируются на
Standby. В случае переезда на Standby задания начнут выполняться на нем. Это удобнее использования системного cron, если задания
касаются только PostgreSQL.
Документация и исходники https://github.com/citusdata/pg_cron

pg_cron можно как собрать из из исходников, так и установить из пакета:

sudo yum install pg_cron_11

Редактируем конфиг postgresql.conf:

shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

Читать далее

0

PgBouncer настройка пулера соединений PostgreSQL

PgBouncer (https://pgbouncer.github.io/) это пулер соединений для PostgreSQL. Если планируется что к базам будет много одновременных
коннектов, то необходимо настраивать пулер соединений, т.к. PostgreSQL на каждое соединение создает отдельный процесс и общая
производительность СУБД начинает деградировать с увеличением числа коннектов. Если планируется более 500-1000 соединений, то
настраивается пулер, например, pgbouncer.

Устанавливаем на сервер PostgreSQL pgbouncer и выдаем права на директории конфигов и логов:

sudo yum install pgbouncer
chown -R postgres:postgres /etc/pgbouncer
chmod 755 /var/log/pgbouncer

Читать далее

0

pgBackRest настройка бекапов PostgreSQL

Официальный User Guige.
Параметры конфигурации.

Устанавливаем на сервере PostgreSQL pgbackrest:

sudo yum install pgbackrest

Выдаем права владения postgres:postgres на файл конфига /etc/pgbackrest.conf:

chown postgres:postgres /etc/pgbackrest.conf

Далее, в зависимости от конфигурации, либо создаем локальную директорию для бекапов с владельцем postgres:postgres, либо
монтируем шару (например, /mnt/pgbackup как будет описано далее в инструкции) с тем же владельцем postgres:postgres.

Читать далее

0

Barman сжатие WAL

Barman это утилита для бекапирования кластера PostgreSQL.

В Barman доступно сжатие WAL-логов бекапов. (Сжатие FULL (base) бекапов, на текущий момент, средствами Barman недоступно.)
Для включения сжатия есть два варианта:
1. Включить сжатие глобально для всех бекапов, для этого нужно в конфиге /etc/barman.conf прописать в блоке [barman]:
[barman] …
compression = gzip

2. Включать сжатие только для нужных проектов (бекапов), для этого в конфиге конкретного бекапа нужно указать:
[backup_name] …
compression = gzip

Читать далее

0

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. Читать далее

4+

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