Что такое репликация базы данных postgresql

Записки программиста

Потоковая репликация в PostgreSQL и пример фейловера

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

Перекрестная ссылка: Вас также может заинтересовать заметка Начало работы с PostgreSQL. В частности, в ней рассказывается, для чего нужны файлы pg_hba.conf и postgresql.conf, как пользоваться утилитой psql, а также как производится резервное копирование и восстановление PostgreSQL. Далее предполагается, что все это вы уже знаете.

Коротко о главном

Когда вы изменяете данные в базе, все изменения пишутся во Write-Ahead Log, или WAL. После записи в WAL СУБД делает системный вызов fsync, благодаря чему данные попадают сразу на диск, а не висят в где-то в кэше файловой системы. Таким образом, если взять и обесточить сервер, при следующей загрузке СУБД прочитает последние записи из WAL и применит к базе данных соответствующие изменения.

Потоковая репликация (streaming replication) в сущности является передачей записей из WAL от мастера к репликам. Писать при этом можно только в мастер, но читать можно как с мастера, так и с реплик. Если с реплики разрешено читать, она называется hot standby, иначе — warm standby. Поскольку во многих приложениях 90% запросов являются запросами на чтение, репликация позволяет масштабировать базу данных горизонтально. Потоковая репликация бывает двух видов — синхронная и асинхронная.

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

При использовании синхронной репликации данные сначала записываются в WAL как минимум одной реплики, после чего транзакция выполняется уже на мастере. Запросы на запись выполняются медленнее в результате возникающих сетевых задержек (которые, однако, внутри одного ДЦ обычно меньше типичного времени планирования запроса). Кроме того, чтобы запросы на запись не встали колом в результате падения одной из реплик, при использовании синхронной репликации рекомендуется использовать по крайней мере две реплики. Зато потерять данные становится намного сложнее.

Заметьте, что синхронная репликация не предотвращает возможности считать с реплики старые данные, так как потоковая репликация — она только про передачу WAL, а не то, что видно в базе с точки зрения пользователя. По крайней мере, так синхронная репликация работает конкретно в PostgreSQL.

В контексте репликации нельзя также не отметить еще один интересный термин. Если одна из реплик в свою очередь является мастером для другой реплики, такую конфигурацию называют каскадной репликацией.

Помимо потоковой репликации в последнее время выделяют еще и так называемую логическую репликацию (logical replication). Реализаций логической репликации в PostgreSQL существует несколько, например, slony и pglogical. Пожалуй, наиболее существенное отличие логической репликации от потоковой заключается в возможности реплицировать часть баз данных и таблиц на одни реплики, а часть — на другие. Платить за это приходится скоростью. И хотя pglogical в плане скорости выглядит многообещающе, на момент написания этих строк это очень молодое, сырое решение. В рамках этой заметки логическая репликация не рассматривается.

Дополнение: В PostgreSQL 10 добавили логическую репликацию, теперь она есть из коробки.

Fun fact! Потоковая репликация в PostgreSQL не работает между разными версиями PostgreSQL, а также если на серверах используется разная архитектура CPU, например, x86 и x64. В частности, это означает, что обновить PostgreSQL до следующей версии при использовании потоковой репликации без даунтайма нельзя. Отсюда еще больший интерес к логический репликации, лишенной этого недостатка. Согласно этой статье, типичное время упомянутого даунтайма составляет пару минут.

Дополнение: С другой стороны, на практике это может быть не такой уж большой проблемой. Дело в том, что при падении мастера факт его падения, а затем и промоутинг релпики до мастера, происходят далеко не моментально. Так что в общем случае условие «мы в любой момент времени можем писать» все равно не выполняется. Читать при обновлении мы можем, так как пару реплик можно оставить работать, пока все остальное обновляется.

Быстрая установка PostgreSQL

При написании этой заметки я использовал Ubuntu 14.04 LTS и PostgreSQL 9.5. Скорее всего, многое из написанного будет также справедливо для других дистрибутивов Linux и версий PostgreSQL. Для эмуляции нескольких машин я использовал LXC контейнеры. Далее предполагается, что машины находятся в локальной сети 10.0.3/24.

Нам понадобится две машины — master и slave. На обеих говорим:

Источник

Репликация 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 apt install postgresql-9.6

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

systemctl start postgresql-9.6
systemctl enable postgresql-9.6

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

После того как Postgresql запущен, нам нужно настроить пароль для пользователя 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:

Дальше нам нужно отредактировать файл 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:

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

systemctl stop postgresql-9.6

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

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

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

Вам нужно будет ввести пароль и дождаться пока будут загружены данные. Дальше нужно исправить настройки /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. Сначала посмотрим параметры соединения:

Затем авторизуйтесь на сервере 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, но тоже можно быстро разобраться и настроить. Если у вас остались вопросы, спрашивайте в комментариях!

Источник

Sysadminium

База знаний системного администратора

Репликация в PostgreSQL

Разберем процесс репликации. Репликация в PostgreSQL – это процесс синхронизации нескольких копий кластера баз данных на разных серверах. Она бывает логической и физической.

Репликация PostgreSQL решает две задачи:

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

Физическая репликация PostgreSQL

Алгоритм создания такой репликации следующий:

Сервер репликации может принимать запросы, но только на чтение. Этот сервер не генерирует wal записи, а продолжает их получать из потока по протоколу репликации с основного сервера.

Журнальные записи можно передавать по протоколу репликации или можно использовать архив WAL. Но обычно применяют первое.

На главном сервере появляется процесс wal sender, который передаёт wal записи. На сервере репликации появляется wal receiver, который принимает эти записи.

Репликация может быть синхронной и асинхронной. При синхронной репликации основной сервер не только отправляет запись на реплику, но и дожидается подтверждения что запись туда дошла и была там применена. Но синхронная реплика сильно тормозит производительность кластера.

Переключение на реплику

Переключение на реплику осуществляется либо планово, либо аварийно. Но в любом случае оно происходит в ручном режиме. Хотя процесс можно автоматизировать, например с помощью скриптов.

Для восстановления основного сервера его обычно превращают в реплику и синхронизируют с бывшей репликой. После синхронизации сервера опять меняют местами.

Восстановление основного сервера проделывают следующими способами:

Сценарии использования физической репликации

Логическая репликация PostgreSQL

При репликации передаются wal записи, но для работы логической репликации нужно изменить формат этих записей. Для этого нужно поменять параметр кластера wal_level = logical.

Поставщик – передаёт логические wal записи. Но передаются не все команды, а только INSERT, UPDATE, DELETE и TRANSCATE. То есть CREATE не передаётся, поэтому нужна начальная физическая синхронизация. Также мы можем ограничить публикацию некоторыми командами, например передавать только INSERT, а UPDATE не передавать.

При такой репликации всегда используется слот логической репликации. Благодаря которому понятно, какие записи уже были переданы, а какие нет.

Подписчик – получает wal записи и применяет изменения без разбора, трансформаций и планирования.

Подписчик и поставщик это равноценные сервера, они оба доступны на чтение и запись для клиентов. Поэтому могут возникать конфликты. Например на поставщике выполняется INSERT, а на подписчике уже такая строка есть. Подобные конфликты решаются пока только в ручном режиме.

На поставщике работает уже знакомый процесс wal sender, а на подписчике logical replication worker который получает логические wal записи и применяет их от имени суперпользователя.

Сценарии использования логической репликации

Практика

Физическая репликация

После предыдущего урока у нас два каталога PGDATA. При этом второй кластер выключен.

Очистим каталог /usr/local/pgsql/data2/ и заново сделаем туда резервную копию с помощью pg_basebackup. Ключ -R подготовит архивную копию к дальнейшей репликации (создаст файл standby.signal):

Помимо standby.signal в postgresql.auto.conf были внесены параметры для последующей репликации:

Был добавлен параметр primary_conninfo, в котором указаны опции подключения к основному серверу:

Вот ещё пример, который не нужно выполнять!

Задаются параметры подключения к основному серверу с помощью опций pg_basebackup. Например мы подключались бы к другому серверу, а для репликации был бы отдельный пользователь, тогда команда выглядела бы так:

В команде выше мы используем следующие опции:

После такой команды, конфигурационный файл postgresql.auto.conf будет содержать такой параметр подключения к основному серверу:

Поменяем порт второго кластера, чтобы он мог работать одновременно с первым:

Файл standby.signal который появился во втором кластере означает настройку standby_mode=on в recovery.conf до PostgreSQL 12. То есть ничего делать не нужно для того чтобы перевести сервер в режим работы реплики.

Можем запустить второй кластер:

Посмотрим на процессы реплики. Процесс walreceiver streaming принимает поток wal записей, а процесс startup recovering применяет изменения:

В основном кластере появился процесс walsender postgres, который передаёт wal записи:

Проверить состояние репликации можно в представлении pg_stat_replication на главном сервере:

Внесём некоторые изменения на мастере:

Проверим, создались ли эти объекты на сервере репликации:

Сама реплика ничего менять не может:

Переведём реплику в обычный режим, то есть отключим репликацию. Это делается с помощью команды promote. При этом у нас пропадет файлик standby.signal.

После этого второй кластер может писать данные:

Логическая репликация

Поменяем параметр wal_level = logical для первого кластера и перезапустим этот его:

В первом кластере создадим публикацию (CREATE PUBLICATION) и посмотрим на неё с помощью команды \dRp+:

Во втором кластере подписываемся на эту публикацию (CREATE SUBSCRIPTION) и отключаем первоначальное копирование данных (copy_data = false):

Выше вы можете заметить предупреждение, что на публикующем сервере создался слот репликации (NOTICE: created replication slot “test_sub” on publisher)!

Теперь посмотрим на подписку с помощью команды \dRs:

Проверим настроенную логическую репликацию. Для этого в первом кластере, вставим новую строчку в опубликованной табличке:

Во втором кластере увидим эту строку:

Состояние подписки можно посмотреть в представлении pg_stat_subscription:

К процессам сервера подписчика добавился logical replication worker:

Если репликация больше не нужна, надо удалить подписку, иначе на публикующем сервере останется открытым слот репликации:

На этом этот курс подошёл к концу! За основу взяты видео с этого плейлиста на YouTube. Я просто проделал все на PostgreSQL 13 и лишь некоторые моменты связанные с репликацией отличаются от PostgreSQL 10. В дальнейшем, если вернусь к теме PostgreSQL постараюсь усовершенствовать этот курс, спасибо за внимание!

Источник

Простая настройка репликации в PostgreSQL

Возникла необходимость быстро и как можно проще организовать репликацию данных с сервера БД на резервный сервер. Простой и понятный способ на просторах Сети так и не нашелся, по этому пришлось по частям собрать информацию, которая и стала этой статьёй.

Решаемая задача. Исходные данные

Настраиваем основной сервер БД

В разделе «Роли входа», через PgAdmin создаем пользователя (роль) repl с правами «Может создавать потоковую репликацию и резервные копии». Дописываем в pg_hba или через «Конфигурацию сервера» в PgAdmin создаем строку, разрешающую пользователю repl подключение к БД.

host replication repl 192.168.1.2/32 trust

В файле postgresql.conf сделали следующие изменения:

Настраиваем резервный сервер

Останавливаем сервис Postgresql 9.2 на резервном сервере и очищаем папку с данными, которые создаются при установке PostgreSQL, например D:\database. После этого запускаем бекап из командной строки:

Настраиваем postgresql.conf в D:\database

Настраиваем recovery.conf в D:\database

Запускаем сервис Postgresql на резервном сервере. Должно стартовать без ошибок. Проверяем работу репликации: производим изменения в таблице на основном сервере, и проверяем — отразились ли они на резервном.

Настройка архивирования логов (если нужно)

В файле postgresql.conf на основном сервере включаем следующее:

В папку e:\Backup будут падать архивы логов. (Внимание, они могут забить все место на диске, нужно настроить очистку при переполнении)

Чтобы воспользоваться архивами логов, нужно cкопировать логи в папку из которой будем восстанавливаться и в recovery.conf дописать строчку

restore_command = ‘copy «e:\\Backup\\%f» «%p»‘

Источник