У даній статті ми проведемо установку СУБД PostgreSQL 11 в Linux CentOS 7, виконаємо базову настройку сервера і СУБД, розглянемо основні параметри конфігураційного файлу, а так само способи тюнінга продуктивності. PostgreSQL – популярна вільна об’єктно-реляційна система управління базами даних. Не дивлячись на те, що вона не так поширена як MySQL / MariDB, вона є самою професійною.

Сильні сторони PostgreSQL:

  • Повна відповідність стандартам SQL;
  • Висока продуктивність за рахунок керування різноманітним паралелізмом (MVCC);
  • Масштабованість (широко використовується в високонавантажених середовищах);
  • Підтримка безлічі мов програмування;
  • Надійні механізми транзакцій і реплікації;
  • Підтримка даних в форматі JSON.

Установка PostgreSQL в CentOS / RHEL

Хоча PostgreSQL можна встановити з базового сховища CentOS, ми виконаємо установку сховища від розробників, так як в ньому завжди присутня актуальна версія пакету.

Першим кроком встановлюємо репозиторій PosgreSQL (на даний момент він встановлюється в такий спосіб):

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

В даному репозиторії є як найновіші версії PostgreSQL, так і більш старі версії. Інформація про репозиторії виглядає наступним чином:

підключення сховища PosgreSQL

Встановимо останню доступну версію версію (PostrgeSQL 11) c допомогою yum.

yum install postgresql11-server -y

В процесі установки устаналівается сам сервері PostgreSQL і необхідні біблотеки:

Installing : libicu-50.2-3.el7.x86_64 1/4
Installing : postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4
Installing : postgresql11-11.5-1PGDG.rhel7.x86_64 3/4
Installing : postgresql11-server-11.5-1PGDG.rhel7.x86_64 4/4

Після установки пакетів, потрібно провести ініціалізацію бази даних:

/usr/pgsql-11/bin/postgresql-11-setup initdb

Так само відразу додамо сервер БД в автозавантаження і запустимо його:

systemctl enable postgresql-11

systemctl start postgresql-11

Щоб переконатися, що сервер запустився і ніяких проблем немає, перевіримо його статус:

[root@server ~]# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2019-09-18 13:01:56 +06; 26s ago
Docs: https://www.postgresql.org/docs/11/static/
Process: 6614 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 6619 (postmaster)
CGroup: /system.slice/postgresql-11.service
├─6619 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─6621 postgres: logger
├─6623 postgres: checkpointer
├─6624 postgres: background writer
├─6625 postgres: walwriter
├─6626 postgres: autovacuum launcher
├─6627 postgres: stats collector
└─6628 postgres: logical replication launcher
Sep 18 13:01:56 server.1.com systemd[1]: Starting PostgreSQL 11 database server...
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.399 +06 [6619] LOG: listening on IPv6 address "::1", port 5432
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.399 +06 [6619] LOG: listening on IPv4 address "127.0.0.1", port 5432
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.401 +06 [6619] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.409 +06 [6619] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.427 +06 [6619] LOG: redirecting log output to logging collector process
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.427 +06 [6619] HINT: Future log output will appear in directory "log".
Sep 18 13:01:56 server.1.com systemd[1]: Started PostgreSQL 11 database server.

Якщо вам потрібен доступ до PostgreSQL зовні, вам потрібно відкрити порт TCP / 5432, в стандартному firewall в Centos 7:

# firewall-cmd --get-active-zones

public
interfaces: eth0

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload

Або через iptables:

#iptables-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

#service iptables restart

Якщо включений SELinux, виконайте:

setsebool -P httpd_can_network_connect_db 1

Підключення до PostgreSQL, створення БД, користувача

За замовчуванням при установці PostgreSQL в системі є один користувач -postgres.

Я не рекомендую використовувати його для роботи з базами даних, краще створювати користувачів для кожної БД окремо.

Щоб підключитися до сервера postgres потрібно ввести команду:

[root@server /]# sudo -u postgres psql

psql (11.5)
Type "help" for help.

postgres=#

Відкрилася консоль PostgreSQL. Покажемо кілька простих прикладів управління PostgreSQL з консолі psql.

Оскільки будь-який користувач системи може авторизуватися в postrgesql, спочатку потрібно змінити пароль користувача postgres.

ALTER ROLE postgres WITH PASSWORD 'super_str0ng_pa$$word';

Відразу створимо нову базу даних, користувача і дамо йому повні права на цю БД:

postgres=# CREATE DATABASE mydbtest;

postgres=# CREATE USER mydbuser WITH password '123456789';

postgres=# GRANT ALL PRIVILEGES ON DATABASE mydbtest TO mydbuser;

Підключитися до БД:

postgres=# c databasename

Вивести список таблиць:

postgres=# dt

Вивести список запитів до бази:

postgres=# select * from pg_stat_activity where datname="dbname"

Скинути все підключення до бази:

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname="dbname"

Інформацію про поточну сесії можна отримати так:

postgres=# conninfo

Для завершення роботою з консоллю psql, виконайте:

postgres=# q

Як ви вже помітили, синтаксис не відрізняється від тієї ж MariaDB або MySQL і тому особливо зупинятися на однотипних командах ми не будемо.

Відзначимо, що для більш зручного управління базами PostgreSQL з веб-інтерфейсу рекомендується використовувати pgAdmin4 (написаний на Python і Javascript / jQuery). Це аналог звичного багатьом веб розробникам PhpMyAdmin.

pgAdmin4 веб інтерфейс управління

Основні параметри конфігураційних файлів PostgreSQL

Файли конфігурації postgresql знаходяться в директорії / var / lib / pgsql / 11 / data:

  • postgresql.conf – безпосередньо сам файл конфігурації postgresql;
  • pg_hba.conf – файл з настройками доступу. В даному файлі, можна виставляти різні обмеження для користувачів, встановлювати політику підключення до БД;
  • pg_ident.conf – цей файл використовується при ідентифікації клієнтів по протоколу ident.

Щоб заборонити локальним користувачам вхід в postgres без авторизації, в файлі pg_hba.conf вкажіть:

local all all md5
host all all 127.0.0.1/32 md5

Розглянемо найбільш важливі параметри в файлі конфігурації postgresql.conf:

  • прослухати_адреси – вказує на будь IP адреси сервер буде приймати клієнтські підключення. За замовчуванням вказано localhost, це означає, що можливо тільки локальне підключення. Щоб случашать на всіх IPv4 інтерфейси, вкажіть 0.0.0.0
  • max_connections – як і в інших СУБД, це максимальна кількість одночасних підключення до сервера БД;
  • temp_buffers – максимальний розмір тимчасових буферів;
  • shared_buffers – обсяг пам’яті, що використовується сервером баз даних. Зазвичай виставляється в розмірі 25% від пам’яті, встановленої на сервері;
  • ефективний_ розмір кешу – параметр, який допомагає планувальником postgres визначити кількість доступної пам’яті для кешування на диск. Зазвичай параметр виставляється розміром в 50-75% від всієї ОЗУ на сервері;
  • work_mem – обсяг пам’яті, який буде використовуватися внутрішніми операціями сортування СУБД – ORDER BY, DISTINCT і злиття;
  • service_work_mem – обсяг пам’яті, який буде використовуватися внутрішніми операціями – VACUUM, CREATE INDEX і ALTER TABLE ADD FOREIGN KEY;
  • fsync – якщо цей параметр включений, то СУБД буде чекати фізичної записи даних на жорсткий диск. При включеному fsynс вам буде простіше відновити БД після системного або апаратного збою. Природно, що включення даного параметра значно знижує продуктивність СУБД, але підвищує надійність зберігання. При відключенні цього параметра варто відключати і full_page_writes;
  • max_stack_depth – максимальний розмір стека (2 Мб за замовчуванням);
  • max_fsm_pages – за допомогою даного параметра, можна управляти вільним дисковим простором на сервері. Наприклад, після видалення даних з таблиці, займане раніше місце не звільняється на диску, а позначається на карті вільного простору з міткою «вільно» і далі використовується для нових записів в таблиці. Якщо на сервері активно ведеться запис / видалення даних в таблицях, збільшення даного параметра, позитивно вплине на продуктивність;
  • wal_buffers – обсяг з розділяється пам’яті (shared_buffers), який використовується для зберігання даних WAL;
  • wal_writer_delay – час між періодами записи WAL на диск;
  • commit_delay – затримка між записом транзакції в буфер WAL і скиданням його на диск;
  • синхронний_комітет – параметр визначає, що результат про успішне завершення транзакції буде відправлений тоді, коли дані WAL фізично запишуться на диск.

Резервних копій та відновлення БД в PostgreSQL

Створити резервну копію в PostgreSQL БД можна декількома способами. Розглянемо найпростіший варіант.

Для початку перевіримо, які БД запущені на сервері:

postgres=# list

postgres - вивести список баз даних

У нас є 4 бази даних, 3 з яких системні (postgres і template).

Раніше ми створювали БД з ім’ям “mydbtest”, на її прикладі і виконаємо резервне копіювання.

Один із способів резервного копіювання, це виконання його за допомогою утиліти pg_dump:

sudo -u postgres pg_dump mydbtest > /root/dupm.sql – виконуємо запит від користувача postgres, вказуємо потрібну БД і шлях до файлу в який потрібно зберегти дамп бази. Дамп бази може забрати ваша система резервних копіювання, або в разі використання веб сервера, ви можете відправити його в ваше хмарне сховище.

Щоб відновити вказаний дамп в потрібну БД, можна скористатися утилітою psql:

sudo -u postgres psql mydbtest < /root/dupm.sql

резервне копіювання postgres за допомогою psql

Так само можна створити бекап в спеціальному форматі дампа і стислому із застосуванням gzip:

sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql

Відновлюється такий дамп за допомогою утиліти pg_restore:

sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql

Більш розширені настройки можна подивитися в довідці за даними утилітам:

man psql
man pg_dump
man pg_restore

Оптимізація і тюниг PostgreSQL

У попередній статті про MariaDB, ми показували, як можна привести практично до ідеалу параметри конфігураційного файлу my.cnf за допомогою тюнерів. Для PostgreSQL існує, хоча правильніше сказати існувала така утиліта як PgTun, але на жаль вона вже давно не оновлюється. У той же час є маса онлайн сервісів, за допомогою яких ви можете налаштувати оптимальну конфігурацію для вашого PostgreSQL. Мені подобається сервіс pgtune.leopard.in.ua.

Інтерфейс дуже простий. Вам потрібно вказати параметри вашого сервера (профіль, процесори, пам’ять, тип дисків) і натиснути кнопку “Generate”. В результаті вам буде запропоновано варіант конфігураційного файлу postgresql.conf з рекомендованими значеннями основних параметрів СУБД.

Наприклад, для VPS SSD сервера з 2 Гб оперативної пам’яттю, 2 CPU для запуску декількох сайтів рекомендуються наступні настройки в postgresql.conf:

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 2 GB
# CPUs num: 2
# Connections num: 20
# Data Storage: ssd
max_connections = 20
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26214kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

pgtune для postgresql

І це насправді не єдиний ресурс, на момент написання статті, були достпни аналогічні сервіси:

  • Конфігуратор Cybertec PostgreSQL Конфігуратор Cybertec PostgreSQL
  • Інструмент налаштування PostgreSQLІнструмент налаштування PostgreSQL

За допомогою подібних сервісів, можна швидко налаштувати основні параметри СУБД для вашого обладнання і виконуваних завдань. Надалі вже потрібно спиратися не тільки на ресурси сервера, але і аналізувати в цілому роботу БД, її розмір, кількість коннектов і на основі цього, виконувати подальшу тонку донастройку параметрів PostgreSQL.

Leave a Reply

Your email address will not be published. Required fields are marked *