Установка и настройка PostgreSQL в CentOS
Установка 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
Чтобы убедиться, что сервер запустился и никаких проблем нет, проверим его статус:
[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
Если включен 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.
Открылась консоль 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=# select * from pg_stat_activity where datname='dbname'
Сбросить все подключения к базе:
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname'
Информацию о текущей сессии можно получить так:
Для завершения работой с консолью psql, выполните:
Как вы уже заметили, синтаксис не отличается от той же 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 БД можно несколькими способами. Рассмотрим самый простой вариант.
Для начала проверим, какие БД запущены на сервере:
У нас имеются 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 psqlman pg_dumpman 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
И это на самом деле не единственный ресурс, на момент написания статьи, были достпны аналогичные сервисы:
С помощью подобных сервисов, можно быстро настроить начальные параметры СУБД для вашего оборудования и выполняемых задач. В дальнейшем уже нужно опираться не только на ресурсы сервера, но и анализировать в целом работу БД, ее размер, количество коннектов и на основе этого, выполнять дальнейшую тонкую донастройку параметров PostgreSQL.