Потоковая репликация PostgreSQL
Рассмотрим что такое потоковая реплиакция в PostgreSQL и как ее настроить.
Потоковая репликация (Streaming Replication) — это репликация, при которой от основного сервера PostgreSQL на реплики передается WAL (Write Ahead Log). И каждая реплика затем по этому журналу изменяет свои данные. Для настройки такой репликации все серверы должны быть одной версии, работать на одной ОС и архитектуре.
- Настройка master-сервера
- Настройка доп. сервера (slave)
- Тестирование репликации
Все действия в инструкции выполняются на PostgreSQL 14 и Ubuntu 20.04, но в целом инструкция актуальная для предыдущих (и возможно будущих) версий. Есть некоторые отличия версия PostgreSQL до 12 версии, о них смотрите в документации.
В нашем примере у нас будут два сервера:
- Основной (master) с адресом 192.168.233.140
- Дополнительный (slave) с адресом 192.168.233.141
Настройка master-сервера
Первым делом открываем “postgresql.conf” и изменяем в нем параметры.
Создадим пользователя replication, чтобы через него дополнительный сервер мог подключаться к основному.
Репликация 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.pro’); 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, но тоже можно быстро разобраться и настроить. Если у вас остались вопросы, спрашивайте в комментариях!
Репликации в PostgreSQL
Сейчас трудно себе представить «боевую» инсталляцию любой серьезной СУБД в виде единственного инстанса. Конечно, некоторые приложения требуют для своей работы использование локальных баз данных, но если мы говорим о сетевом многопользовательском режиме работы, то здесь использование только одной инсталляции это очень плохая идея.
Основной проблемой единственной инсталляции естественно является надежность. В случае падения сервера нам потребуется некоторое, возможно значительное, время на восстановление. Так восстановление террабайтной базы может занять несколько часов.
Да и исправный бэкап есть не всегда, но об этом мы уже говорили в предыдущей статье.
Кроме надежности, второй существенной проблемой единственной инсталляции является производительность. Даже при использовании виртуальной инфраструктуры мы не можем до бесконечности «одалживать» ресурсы у гипервизора. Что делать когда закончились память и ядра у сервера? Нужно горизонтальное масштабирование, то есть установка дополнительных инстансов. Также на производительность конкретного инстанса влияют ресурсоемкие операции, такие как построение отчетов, обработка данных и т. д. Да и бэкап тоже лучше делать с менее нагруженного инстанса.
Таким образом, мы приходим к тому, что для полноценного функционирования серьезной БД нам необходимо делать реплики. Наличие второго инстанса позволит использовать его в случае выхода из строя основного, то есть обеспечить отказоустойчивость. Кроме того, все ресурсоемкие операции можно перенести на реплику, чтобы максимально разгрузить мастер.
Но и при репликации не следует забывать о некоторых важных моментах. Так, если у вас произошло повреждение данных на логическом уровне (повреждены индексы, некорректные данные в таблицах) то все эти неприятные изменения сохраняться и на реплику. Поэтому репликация это хорошая защита от физических сбоев, а вот от логических сбоев нам поможет бэкап.
И завершая теоретическую часть этой статьи хотелось бы прояснить различия между понятиями резервирование и резервное копирование. Готовя материал к предыдущей статье я сталкивался с тем, что на некоторых ресурсах бэкап тоже называют термином резервирование. Это неверно, резервирование это обеспечение отказоустойчивости работы ресурсов в режиме реального времени. Резервирование не допускает простоев системы, по крайней мере таких масштабных как при восстановлении из бэкапов. Так что резервирование это репликации и кластеры, а резервное копирование это бэкап.
Ну а теперь перейдем уже непосредственно к репликациям в PostgreSQL.
Виды репликаций
Для начала напомню о WAL — журналом предзаписи транзакций. Во избежание нарушений целостности в структуре баз данных, PostgreSQL сначала записывает эти изменения в файлы журнала WAL и только потом в базу. Журналы WAL нужны для того, чтобы в случае сбоя сервера можно было восстановить незафиксированные данные. Ну а применительно к теме сегодняшней статьи, WAL используется и для репликации данных.
Репликация на серверах СУБД PostgreSQL бывает двух видов: физическая и логическая. При физической репликации у нас на сервер реплики передается поток WAL записей. Одним из основных достоинств физической репликации является простота в конфигурировании и использовании, так как используется простое побайтовое копирование с одного сервера на другой. Также из-за своей простоты, физическая репликация потребляет меньше ресурсов.
Но у физических репликаций есть и свои недостатки. По аналогии с физическим бэкапом здесь также требуются одинаковые версии PostgreSQL и операционной системы.
При этом, также должны быть идентичны в том числе и аппаратные компоненты, такие как архитектура процессора. Также при физических бэкапах возможна репликация только всего кластера, на подчиненном инстансе нельзя создать никакую отдельную таблицу, даже временную. Полная идентичность основному серверу.
В зависимости от архитектуры самой БД возможна существенная нагрузка на инфраструктуру, так как нужно передавать все изменения в файлах данных полностью.
Логическая репликация работает по принципу подписки. Мастер сервер выступает в роли поставщика, который публикует изменения, происходящие в базе, а серверы реплики, выступающие в роли подписчиков получают и применяют эти изменения у себя.
На программном уровне логические репликации используют репликационные идентификаторы (как правило, это первичный ключ). Снимок данных с таблицы на основном инстансе публикуется и передается подписчику. После этого, все изменения, происходящие с данными на стороне основного сервера также публикуются и передаются подписчику, как любят писать во многих источниках «в режиме реального времени». Хотя если быть занудным то термин «работа в реальном времени» применим только к операционным системам реального времени RTOS, таким как QNX. А для классических ОС Linux/Windows все-таки уместен термин «режим близкий к реальному времени».
Так или иначе, как только изменения происходят на мастере, они сразу же реплицируется на слейва. Соответственно, подчиненный сервер применяет изменения в той же последовательности, что и публикующий узел, тем самым гарантируется транзакционная целостность.
Логическая репликация также имеет ряд преимуществ перед физической. Прежде всего, это независимость от используемых непосредственно на серверах форматов хранения данных. То есть, мастер и слейв могут иметь различные представления данных на диске, разные ОС и архитектуры. Оба сервера участника репликации могут выступать для разных объектов, как в роли поставщика, так и в роли подписчика. Таким образом мы можем использовать двусторонний обмен, например одна таблица может реплицироваться с первого сервера на второй, а другая со второго сервера на первый. К преимуществам логической репликации можно отнести возможность использовать разные ОС на разных инстансах, а также возможность выборочной репликации отдельных объектов кластера. В результате мы можем снизить нагрузку на сеть, так как при логической репликации объем передаваемых данных меньше.
Сценарии использования
Для физических репликаций основное применение это создание отказоустойчивых кластеров, когда необходимо иметь точную копию БД на другом инстансе.
Логическая репликация предполагает больше различных вариантов использования. Например, мы можем объединить несколько баз в одну, для целей анализа, реплицировать данные между разными кофигурациями PostgreSQL, развернутыми как под Windows, так и под Linux. Можно настроить срабатывание триггеров для отдельных изменений, когда их получает подписчик и другие интересные манипуляции с реплицируемыми данными.
Перейдем к практике.
Для начала настроим физическую репликацию между двумя серверами Ubuntu 22.04.
Далее в примерах
Прежде всего необходимо установить две абсолютно одинаковые инсталляции OC. В моем примере это будет Ubuntu. Далее обновляемся и устанавливаем СУБД на обоих серверах:
Далее работаем в консоли сервера Master.
Под аккаунтом postgres необходимо создать пользователя для репликации:
В моем примере таким пользователем является rep_user. Далее, смотрим расположение conf файла:
Нам необходимо внести в этот файл некоторые правки:
В файл необходимо внести следующие правки:
Далее необходимо внести дополнения в файл pg_hba.conf, добавив имя пользователя для репликаций и IP адрес подчиненного сервера:
И в завершении настройки перезапускаем Postgresql:
На этом настройка сервера Master завершена. Теперь перейдем к настройке сервера Slave. Прежде всего правим файл postgresql.conf:
listen_addresses = ‘localhost, 192.168.1.136’
Для внесения дальнейших изменений нам необходимо остановить сервер:
Так как в режиме подчиненного сервера у нас все данные реплицируются с основного, нам необходимо удалить файлы из каталога main. Если у вас инсталляция не новая, можно предварительно сделать бэкап содержимого этого каталога.
Теперь проведем проверку работы процесса репликации. Для этого используем команду pg_basebackup с адресом основного сервера и именем пользователя для репликаций:
Теперь можно запустить сервис PosthreSQL на подчиненном сервере:
Логические репликации
В завершении статьи рассмотрим настройку логической репликации в PostgreSQL. В качестве примера я выполню репликацию базы Otus. Прежде всего необходимо в файле postgresql.conf сменить значение параметра wal_level:
В уже знакомом нам файле pg_hba.conf на мастере добавляем строку с IP адресом подчиненного сервера:
Далее делаем дамп всей БД и дамп схемы базы Otus.
Теперь необходимо создать публикацию на стороне сервера мастер:
И подписку на стороне подчиненного сервера:
Теперь все изменения в базе Otus будут реплицироваться на Slave И данный сервер уже можно будет использовать для выполнения резервного копирования, построения отчетов и других ресурсоемких задач.
Заключение
В этой статье мы рассмотрели такую важную тему как базовая настройка физических и логических репликаций в СУБД PostgreSQL. Дальше, с помощью репликаций уже можно строить более сложные схемы взаимодействия между серверами БД.
Также напоминаю про бесплатный вебинар курса «PostgreSQL для администраторов баз данных и разработчиков» посвященный маленьким хитростям GROUP BY. На вебинаре вспомним, как устроен GROUP BY и рассмотрим его на наглядных примерах, оптимизируем работу группировки в связке с индексами, разберемся с особенностями группировки строк в PostgreSQL, а также изучим несколько полезных приемов для работы с GROUP BY.
PostgreSQL Replication with Docker
There are so many ways to setup replication for a PostgreSQL master, but when it comes to docker, it could waste your time. In this article I will tell you how to setup a PostgreSQL master first, then we will add a slave for it using streaming replication method, all in docker containers.
Heads-Up !
Before going into the details I assume you are familiar with docker and docker-compose service, understand the basics and could work with terminal. Also it’s good to read my article, Tips on Using Docker, since I use them mostly in my configurations.
Let’s setup a PostgreSQL database
For setting up a postgreSQL database with docker, you could go to their official docker hub page, check the various versions and configurations. I recommend to use docker-compose since it’s easier to manage. Here is a simple docker-compose file for running it.
Let’s tune it for production
Now the container should be running and you could access it easily and start using it, but this is not good enough for production. There are some configurations and performance tweaks which needs to be applied based on your hardware ! it’s not essential, but good to have it. You could get all the details you need from https://pgtune.leopard.in.ua website. Then you need to create a postgresql.conf file and add those lines. You should know that these are not all the required configurations for running postgres, these are just the performance tweaks. Now you wonder what are the other configurations? They have answered this question in their docker hub page, first run the command below to get the sample file and then add the configurations you got from pgtune website into it.
Here is a final my-postgres.conf sample
Let’s setup a PostgreSQL master database !
There are couple of more configurations needed for a master database, first we need to add some lines to my-postgres.conf for replication.
Also we need to tell postgres to let our replication user connect to that database and trust it, I assume my replication user is called replicator . There is another configuration file called pg_hba.conf which handles the accesses to the database. Let’s create a custome my-pg_hba.conf with these lines.
Now that we have these files, we need to mount them into the container and let the postgres service use them. Let’s add some more lines to our docker-compose.yml file.
Let’s Get Ready For Slave !
We are almost there for setting up a replication, there are couple of steps which we need to take.
- Create the replicator user on master
2. Create the physical replication slot on master
To see that the physical replication slot has been created successfully, you could run this query $ SELECT * FROM pg_replication_slots; and you should see something like this.
You could see, since we are not running any slave for this slot, it’s not active yet.
3. We need to get a backup from our master database and restore it for the slave. The best way for doing this is to use pg_basebackup command. Here is the documentation for postgres version 13.
If you don’t want to read all the documentation to find out which flags you should use, just copy the command below and we will go through the flags in this command.
What are these flags.
-D directory
—pgdata=directory
Sets the target directory to write the output to. pg_basebackup will create this directory (and any missing parent directories) if it does not exist. If it already exists, it must be empty.
-S slotname
—slot=slotname
This option can only be used together with -X stream . It causes WAL streaming to use the specified replication slot. If the base backup is intended to be used as a streaming-replication standby using a replication slot, the standby should then use the same replication slot name as primary_slot_name. This ensures that the primary server does not remove any necessary WAL data in the time between the end of the base backup and the start of streaming replication on the new standby.
The specified replication slot has to exist unless the option -C is also used.
If this option is not specified and the server supports temporary replication slots (version 10 and later), then a temporary replication slot is automatically used for WAL streaming.
X method
—wal-method=method
Includes the required WAL (write-ahead log) files in the backup. This will include all write-ahead logs generated during the backup. Unless the method none is specified, it is possible to start a postmaster in the target directory without the need to consult the log archive, thus making the output a completely standalone backup.
Enables progress reporting.
-U username
—username=username
Specifies the user name to connect as.
-F format
—format=format
Selects the format for the output. format can be one of the following:
Write the output as plain files, with the same layout as the source server’s data directory and tablespaces. When the cluster has no additional tablespaces, the whole database will be placed in the target directory. If the cluster contains additional tablespaces, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the source server.
This is the default format.
Creates a standby.signal file and appends connection settings to the postgresql.auto.conf file in the target directory (or within the base archive file when using tar format). This eases setting up a standby server using the results of the backup.
The postgresql.auto.conf file will record the connection settings and, if specified, the replication slot that pg_basebackup is using, so that streaming replication will use the same settings later on.
After running this command, you could see there is postgresslave directory in the /tmp/ directory.
Let’s Setup the Slave !
Now we need to setup another container for the slave, we are going to use the same docker-compose.yml file and just change the container name and the port.
Now you just need to copy the /tmp/postgresslave directory from master, to data-slave directory in your host machine. Let’s see what’s the final step before firing up the slave container.
The Trick !
Since you have run the pg_basebackup inside a docker container and also asked for recovery config file, it has created a postgresql.auto.conf file inside the data-slave directory. In this file you should see something like this.
Now you could see the primary_conninfo which tells the slave how should connect to the master, but these configurations are not right. Let’s change the primy_conninfo and pass the correct information for connecting to master.
Also we need to add a restore command which tells slave how to deal with this backup, so add this line as well.
Now it’s finished, you could fire up the slave container as well.
You could go to master and run the $ SELECT * FROM pg_replication_slots; query again.
Now you could see the slot is activated. You could also test the replication by creating a dummy table on master and check it on slave