Postgresql не работает репликация

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

Репликация PostgreSQL представляет из себя способ реализации отказоустойчивого кластера. Инструкция написана на примере PostgreSQL 9.6 и 10, также она будет работать для PostgreSQL 9.2 (все нюансы будут отмечены отдельными комментариями).

В данном примере мы настроим потоковую (streaming) репликацию. Другой тип репликации (логическая) добавлена в PostgreSQL 10. Она позволяет реплицировать разные базы данных и таблицы на разные реплики.

Также, мы будем применять асинхронную репликацию — это вид репликации, при котором запросы выполняются сначала на мастере, затем попадают в журнал операций (WAL) и только после этого — на slave. При синхронной репликации запросы сначала попадают в WAL — после в мастер и слейв.

Используемые в данном руководстве команды, применимы для операционных систем Linux. Если Postgre работает под Windows, данную инструкцию можно использовать как шпаргалку для настройки конфигурационных файлов СУБД.

1. Подготовка серверов

Для начала, готовим наши серверы к настройке кластера.

PostgreSQL

На всех серверах баз данных должна быть установлена одна и та же версия PostgreSQL. Также, все серверы должны иметь одну и ту же архитектуру процессора.

Вот пример установки сервера PostgreSQL на CentOS 7.

Брандмауэр

При использовании брандмауэра, необходимо открыть TCP-порт 5432 — он используется сервером postgre.

а) Если управление выполняется с помощью Firewalld:

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

б) Если используем Iptables:

iptables -A INPUT -p tcp —dport 5432 -j ACCEPT

в) Если используем UFW:

ufw allow 5432/tcp

SELinux

Если активирована система безопасности SELinux (по умолчанию в системах Red Hat / CentOS / Fedora), отключаем ее:

sed -i ‘s/^SELINUX=.*/SELINUX=disabled/g’ /etc/selinux/config

Если необходимо, чтобы SELinux работал, настраиваем его.

2. Настройки на Master

В данной статье мы будем настраивать серверы с IP-адресами 192.168.1.10 (первичный или master) и 192.168.1.11 (вторичный или slave).

Переходим на сервер, с которого будем реплицировать данные (мастер) и выполняем следующие действия.

Создаем пользователя в PostgreSQL

Входим в систему под пользователем postgres:

Создаем нового пользователя для репликации:

createuser —replication -P repluser

* система запросит пароль — его нужно придумать и ввести дважды. В данном примере мы создаем пользователя repluser.

Выходим из оболочки пользователя postgres:

Настраиваем postgresql

Смотрим расположение конфигурационного файла postgresql.conf командой:

su — postgres -c «psql -c ‘SHOW config_file;'»

В моем случае система вернула строку:

* конфигурационный файл находится по пути /etc/postgresql/9.6/main/postgresql.conf.

Открываем конфигурационный файл postgresql.conf.

* мы открываем файл, который получили sql-командой SHOW config_file;.

Редактируем следующие параметры:

listen_addresses = ‘localhost, 192.168.1.10’
wal_level = replica
max_wal_senders = 2
max_replication_slots = 2
hot_standby = on
hot_standby_feedback = on

• 192.168.1.10 — IP-адрес сервера, на котором он будем слушать запросы Postgre;
• wal_level указывает, сколько информации записывается в WAL (журнал операций, который используется для репликации);
• max_wal_senders — количество планируемых слейвов;
• max_replication_slots — максимальное число слотов репликации (данный параметр не нужен для postgresql 9.2 — с ним сервер не запустится);
• hot_standby — определяет, можно или нет подключаться к postgresql для выполнения запросов в процессе восстановления;
• hot_standby_feedback — определяет, будет или нет сервер slave сообщать мастеру о запросах, которые он выполняет.

Открываем конфигурационный файл pg_hba.conf — он находитсяч в том же каталоге, что и файл postgresql.conf:

Добавляем следующие строки:

host replication repluser 127.0.0.1/32 md5
host replication repluser 192.168.1.10/32 md5
host replication repluser 192.168.1.11/32 md5

* данной настройкой мы разрешаем подключение к базе данных replication пользователю repluser с локального сервера (localhost и 192.168.1.10) и сервера 192.168.1.11.

Перезапускаем службу postgresql:

systemctl restart postgresql

* обратите внимание, что название для сервиса в системах Linux может различаться.

3. Настройки на Slave

Смотрим путь до конфигурационного файла postgresql:

su — postgres -c «psql -c ‘SHOW data_directory;'»

В моем случае путь был:

Также смотрим путь до конфигурационного файла postgresql.conf (нам это понадобиться ниже):

su — postgres -c «psql -c ‘SHOW config_file;'»

Останавливаем сервис postgresql:

systemctl stop postgresql

На всякий случай, создаем архив базы:

tar -czvf /tmp/data_pgsql.tar.gz /var/lib/pgsql/9.6/data

* в данном примере мы сохраним все содержимое каталога /var/lib/pgsql/9.6/data в виде архива /tmp/data_pgsql.tar.gz.

Удаляем содержимое каталога с данными:

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

И реплицируем данные с master сервера.

а) Если у нас postgresql 9:

su -u postgres -с «pg_basebackup -h 192.168.1.10 -U repluser -D /var/lib/pgsql/9.6/data —xlog-method=stream —write-recovery-conf»

* где 192.168.1.10 — IP-адрес мастера; /var/lib/pgsql/9.6/data — путь до каталога с данными.

б) Если у нас postgresql 10:

su — postgres -c «pg_basebackup —host=192.168.1.10 —username=repluser —pgdata=/var/lib/pgsql/10/data —wal-method=stream —write-recovery-conf»

* где 192.168.1.10 — IP-адрес мастера; /var/lib/pgsql/10/data — путь до каталога с данными.

После ввода команды система запросит пароль для созданной ранее учетной записи repluser — вводим его. Начнется процесс клонирования данных.

Открываем конфигурационный файл postgresql.conf на слейве:

И редактируем следующие параметры:

listen_addresses = ‘localhost, 192.168.1.11’

* где 192.168.1.11 — IP-адрес нашего вторичного сервера.

Снова запускаем сервис postgresql:

systemctl start postgresql

4. Проверка репликации

Посмотреть статус

Статус работы репликации можно посмотреть следующими командами.

=# select * from pg_stat_replication;

=# select * from pg_stat_wal_receiver;

Создать тестовую базу

На мастере заходим в командную оболочку Postgre:

su — postgres -c «psql»

Создаем новую базу данных:

=# CREATE DATABASE repltest ENCODING=’UTF8′;

Теперь на вторичном сервере смотрим список баз:

su — postgres -c «psql»

Мы должны увидеть среди баз ту, которую создали на первичном сервере:

Источник

Postgresql не принимает репликационное соединение

Обычная старая потоковая репликация. PostgreSQL: 9.2.7 Windows 8.1 64 бит

Мой основной и дополнительный кластеры находятся на одной машине Windows. Я уже сделал pg_start_backup () и все, поэтому оба узла имеют точно такие же данные.

Теперь проблема с репликацией заключается в том, что «подключение репликации» от подчиненного сервера не подключается к основному серверу, но я могу подключиться с использованием тех же параметров, используя оболочку psql. Я думаю, что виновником является строка подключения в recovery.conf ведомого:

Я пробовал localhost, 0.0.0.0, LAN IP все, но журнал pg говорит:

Теперь посмотрите на моего Мастера pg_hba.conf:

Как будто я разрешил все возможные соединения, но ведомый не может подключиться. Можете ли вы помочь?

Имя базы данных должно быть, replication поскольку all не распространяется на репликационные соединения.

Документация PostgreSQL далее гласит:

Значение replication указывает, что запись соответствует, если запрашивается подключение репликации (обратите внимание, что подключения репликации не указывают какую-либо конкретную базу данных). В противном случае это имя конкретной базы данных PostgreSQL. Несколько имен базы данных могут быть предоставлены, разделяя их запятыми. Отдельный файл, содержащий имена баз данных, можно указать, поставив перед именем файла символ @ .

Источник

Репликация PostgreSQL

PostgreSQL или Postgres — это объектно-реляционная система управления базами данных с открытым исходным кодом, которая активно разрабатывается уже более чем 15 лет. Сервер баз данных может использоваться для работы высоко нагруженных систем и решения сложных промышленных задач. PostgreSQL может использоваться в Linux, Unix, BSD и Windows.

Репликация баз данных методом Master-Salve — это процесс копирования (синхронизации) данных из базы данных на одном сервере (Master), в базу данных на другом сервере (Salve). В этой статье мы рассмотрим как настраивается репликация PostgreSQL в Ubuntu.

ПРЕИМУЩЕСТВА РЕПЛИКАЦИИ

Основное преимущество — распределение базы данных между несколькими машинами. Если с основным сервером что-то происходит и он перестает работать, то данные все еще доступны на резервном сервере и могут быть без труда получены или восстановлены. Работа проекта продолжится без каких-либо трудностей.

В PostgreSQL доступно несколько способов репликации базы данных в зависимости от цели репликации. Можно настраивать репликацию только для резервного копирования или для организации отказоустойчивого сервера баз данных. Мы будем использовать репликацию типа Master-Salve. Она более подходит для резервного копирования. Для реализации будет использоваться модуль standby.

УСТАНОВКА И НАСТРОЙКА POSTGRESQL

Мы уже подробно рассматривали как установить Postgresql в Ubuntu в одной из предыдущих статей. Но в этой статье повторим эти команды более кратко. Установить и выполнить первоначальную настройку сервера нужно на обоих машинах. Если вы используете последние версии Ubuntu — 17.04 или 17.10, то версия PostgreSQL 9.6 уже есть в официальных репозиториях. Для более старых систем можно использовать PPA:

sudo add-apt-repository «deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main»
wget —quiet -O — https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add —
sudo apt update
sudo apt install postgresql-9.6

В более новых версиях просто установите программу из репозиториев:

sudo apt install postgresql-9.6

Затем запустите службу и добавьте ее в автозагрузку:

systemctl start postgresql-9.6
systemctl enable postgresql-9.6

По умолчанию PostgreSQL запускается на порту 5432. Вы можете убедиться, что этот порт имеет состояние LISTEN выполнив команду netstat:

После того как Postgresql запущен, нам нужно настроить пароль для пользователя Postgres. Но для этого вам нужно авторизоваться под этим пользователем в системе:

sudo su — postgres

Затем, войдите в консоль управления:

Осталось выполнить такую команду, чтобы задать пароль:

Осталось разрешить общение компьютеров между собой по сети на порту 5432 в брандмауэре:

sudo ufw allow postgresql/tcp
sudo ufw allow 5432/tcp
sudo ufw allow 5433/tcp

Напоминаю, что эти действия нужно проделать на обоих машинах.

НАСТОЙКА РЕПЛИКАЦИИ POSTGRESQL

Сначала настроем мастер-сервер. Это основной сервер, который будет выполнять основные действия записи и рассылать данные на сервера Salve. Приложения могут не только читать, но и записывать данные взаимодействуя с этим сервером. Для его настройки нам нужно изменить содержимое файла postgresql.conf в папке /etc/postgresql/9.6/main/:

Сначала расскоментируйте строчку listen_address и пропишите в ней ip адрес вашего сервера. Порт должен быть 5433 иначе не заработает:

listen_address = ‘192.168.56.101’
port=5433

Расскоментируйте строчку wal_level и установите значение standby, она отвечает за способ репликации:

Мы будем использовать локальную синхронизацию:

Включите режим архивирования и укажите команду для создания архива:

archive_mode = on
archive_command = ‘cp %p /var/lib/postgresql/9.6/archive/%f’

Теперь настроем куда именно будет выполняться синхронизация. В нашей инструкции мы будем использовать только два сервера — Master и Salve. Поэтому в строке max_wal_senders поставьте значение 2:

max_wal_senders = 2
wal_keep_segments = 10

Установите имя нашего сервера синхронизации:

Теперь конфигурационный файл можно закрыть. Поскольку мы включили режим архивирования, нужно создать папку для архивов и отдать ее пользователю postgres:

mkdir -p /var/lib/postgresql/9.6/archive/
chmod 700 /var/lib/postgresql/9.6/archive/
chown -R postgres:postgres /var/lib/postgresql/9.6/archive/

Дальше нам нужно отредактировать файл pg_hba.conf, он отвечает за аутентификацию пользователей. Здесь нужно прописать каждый сервер, базу данных, адрес и метод аутентификации. Синтаксис файла такой:

host база_данных пользователь ip_адрес метод опции

sudo vi /etc/postgresql/9.6/main/pg_hba.conf

# Localhost
host replication replica 127.0.0.1/32 md5
# PostgreSQL Master IP address
host replication replica 192.168.56.101/32 md5
# PostgreSQL SLave IP address
host replication replica 192.168.56.102/32 md5

После всех настроек нужно перезапустить службу:

systemctl restart postgresql-9.6

Дальше нам нужно создать нового пользователя, у которого будут права на репликацию. Назовите его replica:

su — postgres
createuser —replication -P replica

После всех этих действий настройка репликации postgresql на сервере Master завершена и он готов к работе. Дальше настроем сервер Salve. Тут все проще. Мы собираемся заменить директорию data этого сервера, на эту же директорию из сервера master и поддерживать их синхронизацию. Сначала остановите службу:

systemctl stop postgresql-9.6

Затем сделайте резервную копию текущей директории, если там есть важные данные и вы боитесь их потерять. Удалите текущую папку с данными:

sudo rm /var/lib/postgresql/9.6/main

Затем авторизуйтесь от имени пользователя postgres и скопируйте все данные из сервера Master:

su — postgres
pg_basebackup -h 192.168.56.101 -U replica -D /var/lib/postgresql/9.6/main -P —xlog -p 5433

Вам нужно будет ввести пароль и дождаться пока будут загружены данные. Дальше нужно исправить настройки /etc/postgresql/9.6/main/postgresql.conf:

sudo vi /etc/postgresql/9.6/main/postgresql.conf

И укажите ip адрес этого сервера в строке listen_address:

Это все, можете сохранить изменения и закрыть файл. Затем создайте файл /etc/postgresql/9.6/main/recovery.conf:

sudo vi /etc/postgresql/9.6/main/recovery.conf

standby_mode = ‘on’
primary_conninfo = ‘host=192.168.56.101 port=5432 user=replica password=password application_name=pgslave01’
trigger_file = ‘/tmp/postgresql.trigger.5433’

Эти настройки нужны для восстановления базы данных в случае возникновения проблем. Осталось запустить службу postgresql на другой машине:

systemctl start postgresql-9.6

Дальше осталось только протестировать как работает потоковая репликация postgresql.

ТЕСТИРОВАНИЕ РЕПЛИКАЦИИ

Чтобы посмотреть как работает репликация вы можете проверить состояния потока репликации, а также просто проверить передаются ли данные от Master на Salve. Сначала посмотрим параметры соединения:

psql -c «select application_name, state, sync_priority, sync_state from pg_stat_replication;»
psql -x -c «select * from pg_stat_replication;»

Затем авторизуйтесь на сервере Master и войдите в консоль управления:

sudo su postgres
psql

Создайте новую таблицу replica_test и вставьте в нее некоторые данные:

CREATE TABLE replica_test (test varchar(100));
INSERT INTO replica_test VALUES (‘losst.ru’);
INSERT INTO replica_test VALUES (‘This is from Master’);

Затем перейдите на сервер Salve и проверьте действительно есть ли там эта табилца:

select * from replica_test;

Дальше вы можете попытаться выполнить запись на сервере Salve:

INSERT INTO replica_test VALUES (‘this is SLAVE’);

Но получите ошибку, так как из этого сервера можно только читать данные.

ВЫВОДЫ

В этой статье мы рассмотрели как работает репликация PostgreSQL типа Master — Salve. Как видите, все это немного сложнее, чем репликация MySQL, но тоже можно быстро разобраться и настроить. Если у вас остались вопросы, спрашивайте в комментариях!

Источник