PostgreSQL — это свободная система управления базами данных с открытым исходным кодом, ориентированная на расширяемость и соответствие стандарту SQL. PostgreSQL — это передовая СУБД (система управления реляционными базами данных) корпоративного класса, поддерживающая как SQL (реляционные), так и JSON (нереляционные) запросы.
Это высокостабильная система управления базами данных корпоративного класса, которая предоставляет различные решения, позволяющие организовать высокую доступность и обход отказа. PostgreSQL используется в качестве основной базы данных для веб-приложений, мобильных приложений и аналитических приложений. Она имеет хорошую репутацию благодаря своей надежности, гибкости, функциональности и производительности.
В этом руководстве я установлю и настрою логическую репликацию сервера базы данных PostgreSQL с помощью pglogical2 на двух серверах Debian 11. Вы установите сервер PostgreSQL с пакетом расширения pglogical, а затем настроите репликацию между двумя или более серверами PostgreSQL.
pglogical — это расширение PostgreSQL, которое позволяет настроить логическую потоковую репликацию на уровне базы данных. Оно использует модель публикации/подписки для репликации. С помощью расширения pglogical вместо репликации доступных данных в PostgreSQL вы будете реплицировать и синхронизировать определенные таблицы в вашей доступной базе данных с другими серверами PostgreSQL.
Предварительные условия
Чтобы выполнить это руководство, вы должны обладать следующими требованиями:
- Два или более сервера Debian 11.
- Пользователь, не являющийся root, с привилегиями администратора sudo/root.
В этом примере используются два сервера Debian со следующей информацией:
Hostname IP Address Used as ------------------------------------------ pgdg01 192.168.5.25 Publisher Node pgdg02 192.168.5.26 Subscriber Node
Когда серверы готовы, вы можете начать установку и настройку PostgreSQL Replication через pglogical.
Настройка /etc/hosts и репозитория PostgreSQL
В этом примере предполагается, что вы используете свежие серверы Debian 11, поэтому первое, что вы сделаете, это настроите репозиторий PostgreSQL и установите PostgreSQL на все ваши системы Debian.
Вы должны выполнить следующие шаги на всех ваших серверах Debian.
Для начала откройте файл конфигурации ‘/etc/hosts‘ с помощью следующей команды редактора nano.
sudo nano /etc/hosts
Добавьте в файл следующие строки и не забудьте изменить детализацию IP-адресов и имен хостов.
192.168.5.25 pgdg01 192.168.5.26 pgdg02
Сохраните и закройте файл ‘/etc/hosts’, когда закончите.
Далее введите следующую команду apt для установки некоторых основных зависимостей. Введите y, когда появится запрос, и нажмите ENTER, чтобы продолжить.
sudo apt install wget gnupg2 lsb-release curl apt-transport-https ca-certificates
Теперь выполните следующую команду, чтобы добавить ключ PostgreSQL GPG и репозиторий для серверов Debian.
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/pgdg.gpg > /dev/null 2>&1 sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Затем обновите индекс пакетов, чтобы применить изменения.
sudo apt update
После добавления вы должны увидеть репозиторий PostgreSQL в списке.
Добавив репозиторий PostgreSQL, вы установите сервер PostgreSQL и расширение pglogical.
Установка PostgreSQL и расширения pglogical
По умолчанию официальный репозиторий PostgreSQL предоставляет несколько версий PostgreSQL, таких как PostgreSQL 13, 14 и 15. В этом примере вы установите и настроите репликацию PostgreSQL с последней версией PostgreSQL 15.
Введите следующую команду ‘apt install‘, чтобы установить сервер PostgreSQL. Введите y, чтобы подтвердить установку, и нажмите ENTER, чтобы продолжить.
sudo apt install postgresql
После завершения установки введите следующую команду systemctl, чтобы проверить сервер PostgreSQL и убедиться, что служба включена и работает.
sudo systemctl is-enabled postgresql sudo systemctl status postgresql
Вывод ‘включено‘ подтверждает, что служба PostgreSQL включена и будет автоматически запускаться при старте системы. А вывод ‘активна (запущена)‘ подтверждает, что текущая служба PostgreSQL запущена.
Далее введите следующую команду apt для установки пакета расширения pglogical. Если вы используете другую версию PostgreSQL, вам следует заменить номер пакета, например ‘postgresql-version-pglogical’.
sudo apt install postgresql-15-pglogical
Введите y при появлении запроса и нажмите ENTER, чтобы продолжить.
PostgreSQL запущен, расширение pglogical установлено. Вы готовы приступить к настройке логической потоковой репликации с помощью pglogical.
Настройка брандмауэра UFW
Перед настройкой серверов PostgreSQL необходимо настроить брандмауэр ufw, который будет защищать и ограничивать подключение к серверу PostgreSQL. С его помощью вы сможете подключаться только к доверенным сетям, которые защищены вашим развертыванием PostgreSQL.
Чтобы установить ufw, введите следующую команду apt. Когда появится запрос, введите y для подтверждения и нажмите ENTER, чтобы продолжить.
sudo apt install ufw
Далее введите следующую команду ufw, чтобы открыть службу OpenSSH. Вывод ‘Rule added’ подтверждает, что новое правило добавлено в ufw.
sudo ufw allow OpenSSH
Теперь введите следующую команду ufw, чтобы разрешить вашим доверенным сетям доступ к серверу PostgreSQL 5432. Обязательно измените IP-адрес в следующей команде.
sudo ufw allow from 192.168.5.0/24 to any port 5432
Далее введите следующую команду, чтобы запустить и включить брандмауэр ufw. Когда появится запрос на подтверждение, введите y и нажмите кнопку ENTER для продолжения.
sudo ufw enable
Выход ‘Брандмауэр запущен и включенd’ подтверждает, что ufw запущен, а также включен, что означает, что брандмауэр ufw будет запускаться автоматически при загрузке системы.
Теперь вы можете проверить состояние и список включенных портов ufw, введя следующую команду.
sudo ufw status
Вы должны увидеть брандмауэр ufw ‘Status: Активный» со службой OpenSSH и портом сервера PostgreSQL ‘5432‘ добавлен и доступен на брандмауэре ufw.
Настройка PostgreSQL для включения репликации
WAL или Write-Ahead Logging — это метод, обеспечивающий целостность данных на сервере PostgreSQL. Любые изменения в таблицах и индексах должны быть записаны только после того, как эти изменения были зарегистрированы.
В PostgreSQL существует три метода WAL, которые можно использовать для обеспечения репликации.
- По умолчанию для WAL используется ‘реплика‘, который позволяет настроить архивирование и репликацию, а также позволяет выполнять запросы только для чтения на резервном сервере.
- Минимальный позволяет удалить все записи в журнал, кроме некоторых важных сведений, которые понадобятся для восстановления после сбоя или немедленного отключения.
- [логический метод позволяет добавить в WAL некоторую информацию, которая поддерживает логическое декодирование.
В этом разделе вы настроите PostgreSQL на запуск по IP-адресу локальной сети, настроите ‘логический‘ wal level через ‘пглогика‘ расширение, затем включите репликацию пользователей с помощью pg_hba.conf файл.
Для начала войдите в оболочку PostgreSQL, введя следующую команду.
sudo -u postgres psql
Затем проверьте значение по умолчанию ‘wal_level’ конфигурация на сервере PostgreSQL.
select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');
Приведенный ниже вывод подтверждает, что уровень wal по умолчанию на сервере PostgreSQL равен ‘реплика‘, которая является стандартной для современных версий PostgreSQL.
Теперь введите ‘\q’ для выхода из оболочки PostgreSQL.
Далее откройте стандартный файл конфигурации PostgreSQL ‘/etc/postgresql/15/main/postgresql.conf’ с помощью следующей команды редактора nano.
sudo nano /etc/postgresql/15/main/postgresql.conf
Отмените параметр ‘listen_addresses’ и добавьте в него IP-адрес вашего внутреннего сервера.
Ниже приведена конфигурация, которая будет использоваться на ‘pgdg01‘ сервер.
listen_addresses = 'localhost, 192.168.5.25'
Ниже приведена конфигурация, которая будет использоваться на сервере ‘pgdg02‘ сервер.
listen_addresses = 'localhost, 192.168.5.26'
Теперь добавьте следующие параметры, чтобы включить ‘логический‘ wal level через расширение ‘pglogical’. Оба сервера имеют схожую конфигурацию для настроек уровня wal. Параметр ‘shared_preload_libraries=»pglogical«» включит уровень логического кошелька через ‘pglogical‘ расширение.
max_worker_processes = 10 shared_preload_libraries = 'pglogical' track_commit_timestamp = 'on' wal_level = 'logical'
Сохраните и закройте файл ‘/etc/postgresql/15/main/postgresql.conf‘ файл после завершения.
Далее откройте файл аутентификации PostgreSQL ‘/etc/postgresql/15/main/pg_hba.conf’ с помощью следующей команды редактора nano.
sudo nano /etc/postgresql/15/main/pg_hba.conf
Добавьте следующие строки в конец строки. Это позволит аутентификацию для любых пользователей с хоста ‘192.168.5.1/24‘ как для обычных пользователей PostgreSQL, так и для пользователей репликации.
# IPv4 local connections: host all all 192.168.5.1/24 scram-sha-256 # replication privilege. host replication all 192.168.5.1/24 scram-sha-256
Сохраните и закройте файл ‘/etc/postgresql/15/main/pg_hba.conf‘ после завершения.
Когда конфигурация ‘/etc/postgresql/15/main/postgresql.conf‘ и ‘/etc/postgresql/15/main/pg_hba.conf‘ завершена, введите следующую команду systemctl, чтобы перезапустить службу PostgreSQL и применить изменения.
sudo systemctl restart postgresql
Теперь введите следующую команду ss, чтобы убедиться, что PostgreSQL запущен на вашем локальном IP-адресе.
ss -tulpn | grep 5432
Вы должны получить следующее сообщение — На сервере pgdg01 сервер PostgreSQL должен быть запущен по адресу ‘192.168.5.25:5432‘. На pgdg02 сервер, сервер PostgreSQL должен быть запущен по адресу ‘192.168.5.26:5432‘.
Наконец, введите следующую команду, чтобы войти в оболочку PostgreSQL и проверить уровень включенного wal на вашем сервере PostgreSQL.
sudo -u postgres psql
Введите следующий запрос PostgreSQL, чтобы проверить уровень разрешенного wal на сервере PostgreSQL.
select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');
Вывод ‘wal_level — logical’ подтверждает, что уровень wal ‘логический‘ включен, что происходит через ‘shared_preload_library — pglogical‘.
На данный момент вы настроили серверы PostgreSQL с уровнем wal ‘логический‘ для логической потоковой репликации через ‘pglogical‘. Кроме того, оба сервера PostgreSQL работают на IP-адресах внутренней сети. На следующем этапе вы создадите нового пользователя PostgreSQL и базу данных, а затем включите опцию ‘pglogical‘ расширение.
Настройка пользователя и базы данных
pglogical — это расширение PostgreSQL, которое работает на уровне базы данных. С его помощью вы не копируете всю систему PostgreSQL, а копируете определенные таблицы или базы данных, доступные в PostgreSQL.
Чтобы настроить pglogical, вы должны иметь привилегию/роль PostgreSQL с правами SUPERUSER. Кроме того, необходимо включить опцию ‘pglogical‘ расширение на целевой базе данных, которую вы хотите реплицировать.
Войдите в оболочку PostgreSQL, введя следующую команду.
sudo -u postgres psql
Создайте нового пользователя и базу данных с помощью следующих запросов PostgreSQL.
В этом примере вы создадите нового пользователя ‘appuser‘ с привилегией ‘SUPERUSER’. На момент написания этой статьи для работы pglogical требовалась привилегия пользователя SUPER. Вы также создадите новую базу данных ‘appdb‘, которая является целевой базой данных, поверх которой вы хотите включить репликацию.
CREATE ROLE appuser WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'Str0ngP4ssw0rd'; CREATE DATABASE appdb WITH OWNER appuser;
Проверьте список пользователей и баз данных на сервере PostgreSQL с помощью следующих запросов. Вы должны получить нового пользователя ‘appuser‘ с привилегией SUPERUSER и новой базой данных ‘appdb‘.
\du \l
Далее подключитесь к новой базе данных ‘appdb’, используя команду ‘\c’ или ‘\connect’ запрос. После подключения приглашение оболочки PostgreSQL примет вид ‘appdb=#’
\c appdb
Введите следующий запрос, чтобы включить ‘pglogical‘ расширение на ‘appdb‘ база данных.
CREATE EXTENSION pglogical;
Затем проверьте список включенных расширений на текущей ‘appdb‘ базы данных с помощью следующего запроса. Вы должны увидеть ‘pglogical‘ расширение включено на ‘appdb‘ база данных.
\dx
После создания пользователя и базы данных и включения pglogical вы начнете настраивать pglogical для включения репликации базы данных между двумя серверами PostgreSQL.
Включение репликации таблиц через pglogical
В этом разделе вы включите логическую репликацию для определенной таблицы, которую вы будете создавать в базе данных ‘appdb’.
### Настройте pgdg01 в качестве узла-издателя
Введите следующие запросы к PostgreSQL на ‘узел издателя‘ — ‘pgdg01 server’ для создания новой таблицы с именем ‘учителя‘.
CREATE TABLE teachers ( id INT PRIMARY KEY, first_name VARCHAR, last_name VARCHAR, subject VARCHAR );
Вставьте новые данные в таблицу «Учителя», введя следующие запросы.
INSERT INTO teachers(id, first_name, last_name, subject) VALUES (1, 'Alice', 'Wonderland', 'Math'), (2, 'Bob', 'Rista', 'Physics'), (3, 'John', 'Bonas', 'English');
Затем получите новые данные, чтобы убедиться, что они доступны на узле издателя pgdg01 сервер.
select count(*) from teachers; select * from teachers;
Приведенный ниже вывод подтверждает, что новые данные были добавлены в таблицу ‘учителя‘.
Далее, чтобы настроить репликацию базы данных/таблицы PostgreSQL с помощью pglogical, необходимо выполнить следующие шаги:
- Создайте узел провайдера на сервере Publisher pgdg01.
- Создайте набор репликации, позволяющий контролировать, какие таблицы в базе данных будут реплицироваться.
- Добавьте таблицы в набор репликации.
Введите следующую команду для создания нового узла провайдера под названием ‘провайдер1‘. Не забудьте изменить сведения о PostgreSQL с помощью параметра pgdg01 сервер.
select pglogical.create_node(node_name := 'provider1', dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser');
Далее введите следующий запрос для создания нового набора реплик под названием ‘replication_set‘.
select pglogical.create_replication_set('replication_set');
Затем добавьте таблицу ‘учителя‘ к набору реплик ‘replication_set‘, введя следующий запрос.
select pglogical.replication_set_add_table(set_name := 'replication_set', relation := 'teachers', synchronize_data := true);
Теперь, когда вы создали узел провайдера с сервером pgdg01 PostgreSQL, создайте набор реплик ‘replication_set‘, и добавили таблицу ‘учителя‘ к ‘replication_set‘. Далее вы создадите и настроите подписчика узел на ‘pgdg02‘ сервер.
Настройка pgdg02 в качестве узла Subsciber
Перейдите на сервер ‘pgdg02’ и введите следующую команду редактора nano, чтобы создать новый файл паролей PostgreSQL ‘/var/lib/postgresql/.pgpass’. Этот файл паролей будет использоваться для вашего
sudo -u postgres nano /var/lib/postgresql/.pgpass
Добавьте в файл следующие строки и не забудьте изменить имя пользователя и пароль на свои данные.
pgdg01:5432:appdb:appuser:Str0ngP4ssw0rd pgdg01:5432:replication:appuser:Str0ngP4ssw0rd pgdg02:5432:appdb:appuser:Str0ngP4ssw0rd pgdg02:5432:replication:appuser:Str0ngP4ssw0rd
Сохраните и закройте файл после завершения работы.
Теперь введите следующую команду, чтобы изменить разрешение файла ‘/var/lib/postgresql/.pgpass’ в режим «только для чтения».
sudo chmod 0600 /var/lib/postgresql/.pgpass
Затем войдите в PostgreSQL с помощью следующей команды.
sudo -u postgres psql
После входа в систему введите следующий запрос, чтобы перезагрузить конфигурацию сервера PostgreSQL, а затем выйдите из системы, чтобы убедиться, что изменения применены.
select pg_reload_conf(); \q
Теперь снова войдите в оболочку PostgreSQL на сервере pgdg02.
sudo -u postgres psql
Подключитесь к созданной вами базе данных ‘appdb’.
\c appdb
Затем введите следующий запрос PostgreSQL для создания новой таблицы ‘учителя‘, которая должна быть сопоставлена с таблицей, имеющейся на узле провайдера ‘pgdg01‘.
CREATE TABLE teachers ( id INT PRIMARY KEY, first_name VARCHAR, last_name VARCHAR, subject VARCHAR );
Если вы проверите доступные данные, то получите пустую таблицу.
select count(*) from teachers; select * from teachers;
После создания файла паролей PostgreSQL и таблицы ‘teachers’, вы должны настроить PostgreSQL на ‘pgdg02‘ сервер в качестве подписчика, выполнив следующие действия:
- Создайте узел Subscriber на сервере pgdg02.
- Создайте подписку, которая запустит процесс синхронизации и репликации в фоновом режиме.
- Проверьте процесс репликации.
Введите следующий запрос для создания нового узла подписчика на сервере PostgreSQL pgdg02. Обязательно измените имя хоста, имя базы данных и пользователя.
select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=pgdg02 port=5432 dbname=appdb user=appuser');
Далее создайте новую подписку под названием ‘subscription1’. Обязательно введите набор реплик с именем ‘replication_set’ , а провайдером является PostgreSQL на pgdg01 сервер. Таким образом, репликация таблицы ‘учителя‘ будет запускаться и работать в фоновом режиме.
select pglogical.create_subscription(subscription_name := 'subscription1', replication_sets := array['replication_set'], provider_dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser' );
Введите следующий запрос, чтобы проверить состояние подписки.
select * FROM pglogical.show_subscription_status();
Вы должны получить следующее сообщение — Статус подписки ‘подписка1‘ реплицируется на провайдера ‘провайдер1‘ и набор реплик ‘replication_set’.
Теперь введите следующий запрос, чтобы дождаться асинхронного запуска репликации подписки и синхронизации данных.
SELECT pglogical.wait_for_subscription_sync_complete('subscription1');
Наконец, вы можете проверить данные в таблице ‘учителя‘, которая доступна на сайте pgdg2 Сервер PostgreSQL.
select count(*) from teachers; select * from teachers;
Если репликация прошла успешно, ваши данные теперь должны быть доступны и реплицированы на сервер PostgreSQL. pgdg02 сервер.
Тестирование репликации
В этом разделе вы проверите репликацию на сервере PostgreSQL, вставив новые данные из узла Publisher узла pgdg01. Затем вы проверите данные из узла Subscriber, чтобы убедиться, что данные реплицируются.
Сначала подключитесь к базе данных ‘appdb‘ на сервере pgdg01, введя следующую команду.
sudo -u postgres psql -U appuser -h pgdg01 -d appdb
Введите следующие запросы для вставки новых данных в таблицу ‘учителя‘.
INSERT INTO teachers(id, first_name, last_name, subject) VALUES (4, 'Ian', 'Gibson', 'Geography'), (5, 'Tom', 'Riddle', 'Biology'), (6, 'Jared', 'Dunn', 'Chemistry');
Проверьте ‘учителя‘ таблица, чтобы убедиться, что новые данные добавлены.
select count(*) from teachers; select * from teachers;
Далее перейдите в раздел pgdg02 сервера и введите следующую команду для подключения к базе данных ‘appdb‘.
sudo -u postgres psql -U appuser -h pgdg02 -d appdb
Введите следующий запрос, чтобы проверить доступные данные в таблице ‘учителя‘. Если репликация прошла успешно, вы должны увидеть обновленные данные на сервере pgdg02.
select count(*) from teachers; select * from teachers;
На этом настройка репликации логических потоков через pglogical на серверах PostgreSQL завершена.
Заключение
В этом руководстве вы установили и настроили репликацию логических потоков с помощью pglogical на серверах Debian 11. Кроме того, вы узнали о базовом использовании расширения pglogical для создания и управления репликацией PostgreSQL на уровне базы данных.
Кроме того, вы завершили установку последней версии PostgreSQL 15 на сервер Debian через официальный репозиторий PostgreSQL и защитили сервер PostgreSQL с помощью брандмауэра UFW.
Теперь вы можете добавить еще один сервер PostgreSQL и настроить расширение pglogical, чтобы обеспечить репликацию на несколько серверов. Вы также можете настроить балансировку нагрузки через HAPROXY или настроить обход отказа серверов PostgreSQL. Для получения более подробной информации посетите официальную документацию PostgreSQL.