Skip to content

Latest commit

 

History

History
402 lines (297 loc) · 29.7 KB

10003.postgres_setup.md

File metadata and controls

402 lines (297 loc) · 29.7 KB

Установка и настройка PostgreSQL на CentOS 7.x

Переводит SELinux в менее агрессивный режим:

sed -i 's/^SELINUX=.*/SELINUX=permissive/g' /etc/selinux/config
setenforce 0
sestatus

Устанавливаем дополнительное ПО и обновляем систему:

yum install epel-release -y
yum install curl wget yum-utils -y
yum update -y
reboot

Установка репозитория для PostreSQL 11 Генератор линка для репозитория PSQL:

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

Инициализируем БД:

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

Автозагрузка и запуск сервиса

systemctl enable postgresql-11 && systemctl start postgresql-11

Меняем пароль для пользователя postgres:

su - postgres -c psql
SQL> \password

Определяем параметры в файле конфигурации – /var/lib/pgsql/11/data/postgresql.conf

vi /var/lib/pgsql/11/data/postgresql.conf

listen_addresses = '*' #На каком адресе будет висеть PostgreSQL (* - на всех)

Добавляем параметры в конфиге аутентификации клиентов – /var/lib/pgsql/11/data/pg_hba.conf:

vi /var/lib/pgsql/11/data/pg_hba.conf

база пользователь IP-адрес/IP-маска метод-аутентификации:

IPv4 local connections:
host	all	all			172.0.0.0/8		md5
host	all	all			10.0.0.0/8		md5

Перезапускаем сервис:

systemctl restart postgresql-11

Открытие порт в мир:

firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload

Проверка наличия листнера

netstat -tulpn | grep 5432

Как завести пользователя при помощи консоли

sudo su postgres -
createuser --interactive asu

Сменить пользователю пароль:

psql
SQL> \password asu

Настройка потоковой репликации в PostgreSQL 11

  • Master server - 10.0.9.47
  • Slave server - 10.0.9.48

Имеется возможность настроить асинхронную (по умолчанию) или синхронную репликации. Данные, в первую очередь, попадают в оперативный журнал WAL (Write-Ahead Log) на уровень и объём журналирования влияет wal_level = replica (параметр в "postgresql.conf"), он может быть minimal, replica или logical

  • minimal - пишется информация необходимая для восстановления после сбоя или аварийного отключения и не более этого;
  • replica - (по умолчанию), в журнал записываются данные, необходимые для поддержки архивирования WAL и репликации, включая запросы только на чтение на ведомом сервере;
  • logical - добавляет информацию, требующуюся для поддержки логического декодирования. Каждый последующий уровень включает информацию, записываемую на всех уровнях ниже

Типы репликации:

  • Асинхронная - данные попадают в WAL -> Master, потом из WAL -> Slave
  • Синхронная - данные попадают в WAL -> Master/Slave (одновременно и Master ждёт пока данные не попадут в Slave, при георазнесенных серверах Master будет тормозить, зависит от объёма данных)

Настройка Master-Slave асинхронной репликации

Выполняем все действия на Master-сервере в нашем случае (10.0.9.47) Создаем нового пользователя для репликации

su - postgres -
createuser --replication -P repluser
  • --replication – создаваемый пользователь будет наделён правом REPLICATION
  • -P – будет запрошен пароль для создаваемого пользователя

Определяем параметры в файле конфигурации /var/lib/pgsql/11/data/postgresql.conf:

vi /var/lib/pgsql/11/data/postgresql.conf

wal_level			= replica
max_wal_senders		= 10
max_replication_slots		= 10
wal_keep_segments		= 32
hot_standby			= on
hot_standby_feedback		= on

Где:

  • wal_level - Параметр wal_level определяет, как много информации записывается в WAL
  • replica - (по умолчанию), в журнал записываются данные, необходимые для поддержки архивирования WAL и репликации, включая запросы только на чтение на ведомом сервере

(https://postgrespro.ru/docs/enterprise/11/runtime-config-wal#RUNTIME-CONFIG-WAL-SETTINGS)

  • max_wal_senders - Задаёт максимально допустимое число одновременных подключений ведомых серверов или клиентов потокового копирования (т. е. максимальное количество одновременно работающих процессов передачи WAL). Значение по умолчанию — 10. Нулевое значение отключает репликацию. Процессы-передатчики WAL учитываются в общем числе соединений, так что значение этого параметра должно быть меньше чем max_connections минус superuser_reserved_connections. При неожиданном отключении клиента потоковой передачи слот его подключения может оставаться в подвисшем состоянии до достижения тайм-аута, так что этот параметр должен быть немного больше максимально допустимого числа клиентов, чтобы отключившиеся клиенты могли переподключиться немедленно. Задать этот параметр можно только при запуске сервера. Чтобы к данному серверу могли подключаться ведомые, нужно также установить в wal_level уровень replica или выше.

(https://postgrespro.ru/docs/enterprise/11/runtime-config-replication#GUC-MAX-WAL-SENDERS)

  • max_replication_slots - Задаёт максимальное число слотов репликации, которое сможет поддерживать сервер. Значение по умолчанию — 10. Этот параметр можно задать только при запуске сервера. Если заданное значение данного параметра будет меньше, чем число уже существующих слотов репликации, сервер не запустится. Чтобы слоты репликации можно было использовать, нужно также установить в wal_level уровень replica или выше.

(https://postgrespro.ru/docs/enterprise/11/runtime-config-replication#RUNTIME-CONFIG-REPLICATION-SENDER)

  • Слоты репликации - Слоты репликации автоматически обеспечивают механизм сохранения сегментов WAL, пока они не будут получены всеми резервными и главный сервер не будет удалять строки, находящиеся в статусе recovery conflict даже при отключении резервного.

  • От себя: для понимания, если ляжет Slave сервер (и за этим не следить), то эти сегменты WAL засрут всё место и будет жопа. Вместо этого параметра, можно использовать параметр - wal_keep_segments

(https://postgrespro.ru/docs/enterprise/11/warm-standby#STREAMING-REPLICATION-SLOTS)

  • wal_keep_segments - Задаёт минимальное число файлов прошлых сегментов журнала, которые будут сохраняться в каталоге pg_wal, чтобы ведомый сервер мог выбрать их при потоковой репликации. Обычно сегмент имеет размер 16 мегабайт. Если ведомый сервер, подключённый к передающему, отстаёт больше чем на wal_keep_segments сегментов, передающий удаляет сегменты WAL, всё ещё необходимые ведомому, и в этом случае соединение репликации прерывается. В результате этого затем также будут прерваны зависимые соединения. (Однако ведомый сервер сможет восстановиться, выбрав этот сегмент из архива, если осуществляется архивация WAL.)

(https://postgrespro.ru/docs/enterprise/11/runtime-config-replication#GUC-WAL-KEEP-SEGMENTS)

  • hot_standby - Определяет, можно ли будет подключаться к серверу и выполнять запросы в процессе восстановления. Данный параметр играет роль только в режиме ведомого сервера или при восстановлении архива.

(https://postgrespro.ru/docs/enterprise/11/runtime-config-replication#RUNTIME-CONFIG-REPLICATION-STANDBY)

  • hot_standby_feedback - Определяет, будет ли сервер горячего резерва сообщать ведущему или вышестоящему ведомому о запросах, которые он выполняет в данный момент. Это позволяет исключить необходимость отмены запросов, вызванную очисткой записей, но при некоторых типах нагрузки это может приводить к раздуванию базы данных на ведущем сервере. Эти сообщения о запросах будут отправляться не чаще, чем раз в интервал, задаваемый параметром wal_receiver_status_interval. Если используется каскадная репликация, сообщения о запросах передаются выше, пока в итоге не достигнут ведущего сервера. На промежуточных серверах эта информация больше никак не задействуется.

(https://postgrespro.ru/docs/enterprise/11/runtime-config-replication#RUNTIME-CONFIG-REPLICATION-STANDBY)

Редактируем конфигурационный файл pg_hba.conf:

vi /var/lib/pgsql/11/data/pg_hba.conf

\#replication privilege.
host    replication     repluser        127.0.0.1/32            md5
host    replication     repluser        10.0.9.47/32            md5 # master
host    replication     repluser        10.0.9.48/32            md5 # slave

** Мы разрешаем пользователю «repluser» подключаться и выполнять репликацию с указанных подсетей.

Для применения настроек перезапускаем сервис PSQL:

systemctl restart postgresql-11

SLAVE

Переходим к настройке на SALVE сервер (10.0.9.48): Останавливаем PostgreSQL службу и проверяем статут:

systemctl stop postgresql-11 && systemctl status postgresql-11

Удаляем все данные на резервном сервере, если они необходимы, предварительно сотворить бекап:

rm -rf /var/lib/pgsql/11/data/*

Закачиваем данные с MASTER сервера:

su - postgres -c "pg_basebackup --progress --write-recovery-conf --wal-method=stream --checkpoint=fast --host 10.0.9.47 --username repluser --pgdata=/var/lib/pgsql/11/data"

Где:

  • pg_basebackup - утилита создающая бинарную копию файлов кластера (прим. если нет кластер, то просто сервака), контролируя режим создания копии автоматически. Резервные копии всегда создаются для кластера целиком и невозможно создать копию для какой-либо сущности базы отдельно. Для этой цели можно использовать, например, утилиту pg_dump.

  • --progress - показывает прогресс процесса

  • --write-recovery-conf - записывает минимальный файл recovery.conf в каталог вывода (или базовый архивный файл при использовании формата tar) для упрощения настройки ведомого сервера. В файл recovery.conf будут записаны параметры соединения и, если указан, слот репликации, который использует pg_basebackup, так что впоследствии при потоковой репликации будут использоваться те же параметры.

  • --wal-method - Включает все необходимые файлы журналов предзаписи (файлы WAL) в резервную копию. В том числе включаются все журналы предзаписи, сгенерированные в процессе создания резервной копии. Если только не выбран метод none, главный процесс БД может быть запущен непосредственно с восстановленным каталогом, без обращения к дополнительному архиву журналов; таким образом будет получена полностью самодостаточная резервная копия.

  • none - не включать журнал предзаписи в резервную копию.

  • fetch - файлы журнала предзаписи собираются в конце процесса копирования. Таким образом необходимо установить достаточно большое значение параметра wal_keep_segments, чтобы избежать преждевременного удаления файлов журнала. В случае удаления файлов до завершения процесса копирования возникнет ошибка, а копия будет непригодной к использованию. Когда используется формат tar, файлы журнала предзаписи записываются в файл base.tar**. (прим. минимальная нагрузка на Master при создании резерва)**

  • stream - передавать журнал предзаписи в процессе создания резервной копии. При этом открывается второе соединение к серверу, по которому будет передаваться журнал предзаписи, одновременно с созданием резервной копии. Таким образом будут использоваться два подключения из разрешённых параметром max_wal_senders. И если клиент будет успевать получать журнал предзаписи, ведущему серверу не потребуется хранить дополнительные файлы журнала. (прим. быстро - но с максимально нагрйзкой на Master)

Настраиваем, созданный автоматически, revocery.conf:

vi /var/lib/postgresql/11/base/recovery.conf (/var/lib/pgsql/11/data/recovery.conf)

standby_mode = 'on'
primary_conninfo = 'user=repluser password=SECRET host=10.0.9.47 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
recovery_target_timeline = 'latest'

  • standby_mode - Указывает, является ли сервер Postgres резервным или нет. Если параметр установлен в on, то сервер не прекратит восстановление по окончании последнего архивного файла WAL, а продолжит попытки извлечения новых сегментов WAL посредством команды restore_command и/или через подключение к ведущему, как указано в параметре primary_conninfo. (https://postgrespro.ru/docs/postgrespro/11/standby-settings)

  • Пароль можно указать как в строке подключения primary_conninfo, так и в файле ~/.pgpass ведомого сервера

  • sslmode - Этот параметр определяет, будет ли согласовываться с сервером защищённое SSL-соединение по протоколу TCP/IP, и если да, то в какой очередности. Всего предусмотрено шесть режимов:

  • disable - следует пытаться установить только соединение без использования SSL

  • allow - сначала следует попытаться установить соединение без использования SSL; если попытка будет неудачной, нужно попытаться установить SSL-соединение

  • prefer - (по умолчанию) сначала следует попытаться установить SSL-соединение; если попытка будет неудачной, нужно попытаться установить соединение без использования SSL

  • require - следует попытаться установить только SSL-соединение. Если присутствует файл корневого центра сертификации, то нужно верифицировать сертификат таким же способом, как будто был указан параметр verify-ca

  • verify-ca - следует попытаться установить только SSL-соединение, при этом проконтролировать, чтобы сертификат сервера был выпущен доверенным центром сертификации (CA)

  • verify-full - следует попытаться установить только SSL-соединение, при этом проконтролировать, чтобы сертификат сервера был выпущен доверенным центром сертификации (CA) и чтобы имя запрошенного сервера соответствовало имени в сертификате

(https://postgrespro.ru/docs/enterprise/11/libpq-connect#LIBPQ-CONNECT-SSLMODE)

  • sslcompression - Если установлено значение 1, данные, передаваемые через SSL-соединения, будут сжиматься. Если установлено значение 0 (по умолчанию), сжатие будет отключено. Этот параметр игнорируется, если установлено подключение без SSL.

  • **Сжатие SSL в настоящее время считается небезопасным, и использовать его уже не рекомендуется.**

(https://postgrespro.ru/docs/enterprise/11/libpq-connect#LIBPQ-CONNECT-SSLCOMPRESSION)

  • krbsrvname - Имя сервиса Kerberos, предназначенное для использования при аутентификации на основе GSSAPI. Оно должно соответствовать имени сервиса, указанному в конфигурации сервера, чтобы аутентификация на основе Kerberos прошла успешно.

  • target_session_attrs - Если этот параметр равен read-write или master, по умолчанию будут приемлемы только подключения, допускающие транзакции на чтение/запись. При успешном подключении будет отправлен запрос SHOW transaction_read_only; если он вернёт on, соединение будет закрыто. Если в строке подключения указано несколько серверов, будут перебираться остальные серверы, как и при неудачной попытке подключения. Со значением по умолчанию (any) приемлемыми будут все подключения.

(https://postgrespro.ru/docs/enterprise/11/libpq-connect#LIBPQ-CONNECT-TARGET-SESSION-ATTRS)

  • recovery_target_timeline - Указывает линию времени для восстановления. По умолчанию производится восстановление той же линии времени, которая была текущей в момент создания базовой резервной копии. Со значением latest восстанавливаться будет последняя линия времени, найденная в архиве, что полезно для резервного сервера. Иное значение параметра может потребоваться в более сложной ситуации повторного восстановления, когда необходимо вернуться к состоянию, которое само было достигнуто после восстановления на момент времени

(https://postgrespro.ru/docs/postgrespro/11/recovery-target-settings)

Запускаем сервис PostrgreSQL:

systemctl start postgresql-11

Осталось проверить работоспособность связки Master-Slave Выполняем запрос на MASTER (10.0.9.47)

su - postgres -c "psql"
SQL> select \* from pg_stat_replication;

Выполняем запрос на SLAVE (10.0.9.48)

su - postgres -c "psql"
SQL> select \* from pg_stat_wal_receiver;

Должна возвратиться информация, если результат пустой - всё плохо «Давай по новой, Миша, все х*я!»

Ещё один простой вариант проверки:

Выполняем запрос на MASTER (10.0.9.47)

su - postgres -
psql -c 'CREATE DATABASE TEST_101;' -c '\l'

Выполняем запрос на SLAVE (10.0.9.48)

su - postgres -
psql -c '\l'

БД созданная на Master – появилась на Slave

Переход с основного сервера (Master) на резервный (Slave)

(!) Реплика не заменяет бекап (!)

Вводные: Master сервер потерял диски и БД более не консистентна (Master – ты низложен!) Допущения: Названия Master и Slave останутся постоянными, их роли в конкретный момент будут описаны в скобках. Например - Master (дохлый), Slave (наше всё) Рассматриваем вариант перехода на резервный сервер:

Работы проводим на Slave: В данный момент Slave находится в состоянии Read Only, проверяем:

sudo su postgres -
psql -c ' CREATE DATABASE SLAVE_N1_001000;'

Продвижение (promote) со Standby до Master сервера

sudo -u postgres /usr/pgsql-11/bin/pg_ctl promote --pgdata=/var/lib/pgsql/11/data

Видим – «Database system is ready to accept connection»

Проверяем, перешел ли сервер в состояние RW:

sudo su postgres -
psql -c 'CREATE DATABASE SLAVE_N1_001000;' -c '\l'

БД «slave_n1_001000» создана.

Уже можно подключаться к Slave (теперь он Master), но PosygreSQL лучше перзепустить, т.к. могут оатстаься коннекты или некий софт который всё считает, что БД работает в состоянии RO

systemctl restart postgresql-11

На данный момент мы получили Master (OFF), Slave (Master) Следующий этап, возвращение Master сервера в строй.

Восстанавливаем Master сервер до состояния, что на нем имеется ОС и установлени PostgreSQL 11 Останавливаем PostgreSQL:

systemctl stop postgresql-11

Удаляем все данные, если таковые имеются:

rm -rf /var/lib/pgsql/11/data/\*

Закачиваем данные со Slave (в данный момент Master)

su - postgres -c "pg_basebackup --progress --write-recovery-conf --wal-method=stream --checkpoint=fast --host 10.0.9.48 --username repluser --pgdata=/var/lib/pgsql/11/data"

(!) Не забываем указать верный источник (10.0.9.48) (!) Удаляем файл recovey.done, это бывший recovery.conf. он переименовался на Slave, в момент повышения рли сервера

rm -rf /var/lib/pgsql/11/data/recovery.done

Добавляем в созданный, на сервере Master, файл recovery.conf строку recovery_target_timeline = 'latest'

Запускаем PSQL:

service postgresql-11 start

Проверить работоспособность можно разными способами, как было описано ранее (создать БД на Slave ио посмотреть, появится ли она на Master)

На данный момент мы получили Master (стал Slave), Slave (стал Master) Возпращаем всё как было до проблем с Master-ом. На Slave (10.0.9.48) - останавливаем PostreSQL на и смотрим статус

systemctl stop postgresql-11 && systemctl status postgresql-11

На Slave (10.0.9.48) – возвращаем обратно, ранее переименованный файл recovery.done

mv /var/lib/pgsql/11/data/recovery.done /var/lib/pgsql/11/data/recovery.conf

Master (10.0.9.47) находится в состоянии RO Master (10.0.9.47) – повышаем его роль

sudo -u postgres /usr/pgsql-11/bin/pg_ctl promote --pgdata=/var/lib/pgsql/11/data

В логах: Slave (10.0.9.48), запускаем службу PostgreSQL

systemctl start postgresql-11

Проверка, создаём на Master (10.0.9.47) БД

sudo su postgres -
psql -c 'CREATE DATABASE TEST_2020;' -c '\l'

Проверяем на Slave (10.0.9.47)

sudo su postgres -
psql -c '\l'

Изменения на Master применились на Slave На данный момент мы получили Master, Slave в первоначальном виде