У даній статті ми проведемо установку СУБД PostgreSQL 11 в Linux CentOS 7, виконаємо базову настройку сервера і СУБД, розглянемо основні параметри конфігураційного файлу, а так само способи тюнінга продуктивності. PostgreSQL - популярна вільна об'єктно-реляційна система управління базами даних. Не дивлячись на те, що вона не так поширена як MySQL / MariDB, вона є найбільш професійній.
Сильні сторони PostgreSQL:
- Повна відповідність стандартам SQL;
- Висока продуктивність за рахунок керування різноманітним паралелізмом (MVCC);
- Масштабованість (широко використовується в високонавантажених середовищах);
- Підтримка безлічі мов програмування;
- Надійні механізми транзакцій і реплікації;
- Підтримка даних в форматі JSON.
зміст:
- Установка PostgreSQL в CentOS / RHEL
- Підключення до PostgreSQL, створення БД, користувача
- Основні параметри конфігураційних файлів PostgreSQL
- Резервних копій та відновлення БД в PostgreSQL
- Оптимізація і тюниг PostgreSQL
Установка 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, так і більш старі версії. Інформація про репозиторії виглядає наступним чином:
Встановимо останню доступну версію версію (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.
Основні параметри конфігураційних файлів 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:
- listen_addresses - вказує на будь IP адреси сервер буде приймати клієнтські підключення. За замовчуванням вказано localhost, це означає, що можливо тільки локальне підключення. Щоб случашать на всіх IPv4 інтерфейси, вкажіть 0.0.0.0
- max_connections - як і в інших СУБД, це максимальна кількість одночасних підключення до сервера БД;
- temp_buffers - максимальний розмір тимчасових буферів;
- shared_buffers - обсяг пам'яті, що використовується сервером баз даних. Зазвичай виставляється в розмірі 25% від пам'яті, встановленої на сервері;
- effective_cache_size - параметр, який допомагає планувальником postgres визначити кількість доступної пам'яті для кешування на диск. Зазвичай параметр виставляється розміром в 50-75% від всієї ОЗУ на сервері;
- work_mem - обсяг пам'яті, який буде використовуватися внутрішніми операціями сортування СУБД - ORDER BY, DISTINCT і злиття;
- maintenance_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 і скиданням його на диск;
- synchronous_commit - параметр визначає, що результат про успішне завершення транзакції буде відправлений тоді, коли дані WAL фізично запишуться на диск.
Резервних копій та відновлення БД в PostgreSQL
Створити резервну копію в PostgreSQL БД можна декількома способами. Розглянемо найпростіший варіант.
Для початку перевіримо, які БД запущені на сервері:
postgres = # \ list
У нас є 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
Так само можна створити бекап в спеціальному форматі дампа і стислому із застосуванням 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
І це насправді не єдиний ресурс, на момент написання статті, були достпни аналогічні сервіси:
- Cybertec PostgreSQL Configurator
- PostgreSQL Configuration Tool
За допомогою подібних сервісів, можна швидко налаштувати основні параметри СУБД для вашого обладнання і виконуваних завдань. Надалі вже потрібно спиратися не тільки на ресурси сервера, але і аналізувати в цілому роботу БД, її розмір, кількість коннектов і на основі цього, виконувати подальшу тонку донастройку параметрів PostgreSQL.
