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

Введение

В этой лабораторной работе вы узнаете, как настроить потоковую репликацию PostgreSQL (PostgreSQL streaming replication). Этот механизм непрерывно отправляет и применяет записи журнала Write-Ahead Logging (WAL) с основного сервера на сервер-реплику, обеспечивая синхронизацию данных практически в реальном времени.

Вы настроите потоковую реплику, проверите задержку и статус репликации, запросите данные из реплики и безопасно завершите репликацию. Это включает в себя подготовку основного сервера путем изменения postgresql.conf, чтобы разрешить подключения от реплики, установку wal_level в replica и настройку max_wal_senders.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/db_status("Check Database Status") postgresql/PostgreSQLGroup -.-> postgresql/db_drop("Drop Entire Database") subgraph Lab Skills postgresql/db_setup -.-> lab-550962{{"Потоковая репликация PostgreSQL"}} postgresql/db_access -.-> lab-550962{{"Потоковая репликация PostgreSQL"}} postgresql/data_all -.-> lab-550962{{"Потоковая репликация PostgreSQL"}} postgresql/db_status -.-> lab-550962{{"Потоковая репликация PostgreSQL"}} postgresql/db_drop -.-> lab-550962{{"Потоковая репликация PostgreSQL"}} end

Настройка основного сервера (Primary Server)

На этом шаге вы настроите основной сервер PostgreSQL, чтобы разрешить потоковую репликацию (streaming replication). Это включает в себя изменение файлов postgresql.conf и pg_hba.conf.

Подключение к серверу PostgreSQL:

Откройте терминал и подключитесь к серверу PostgreSQL от имени пользователя postgres:
```
sudo -u postgres psql
```
Изменение postgresql.conf:

Выйдите из оболочки psql, набрав \q и нажав Enter.

Теперь откройте файл postgresql.conf с помощью nano:
```
sudo nano /etc/postgresql/14/main/postgresql.conf
```
Найдите следующие параметры и измените их следующим образом:
- listen_addresses: Измените его на '*', чтобы разрешить подключения с любого адреса. Для производственных сред рекомендуется ограничить это IP-адресом реплики.
```
listen_addresses = '*'
```
- wal_level: Установите его в replica. Это гарантирует, что в WAL будет записано достаточно информации для репликации.
```
wal_level = replica
```
- max_wal_senders: Установите его в 10. Это указывает максимальное количество одновременных подключений от резервных серверов (standby servers).
```
max_wal_senders = 10
```
Сохраните изменения и выйдите из nano.
Изменение pg_hba.conf:

Откройте файл pg_hba.conf:
```
sudo nano /etc/postgresql/14/main/pg_hba.conf
```
Добавьте следующую строку в конец файла, чтобы разрешить серверу-реплике подключаться для репликации. Поскольку это настройка на одной машине, мы будем использовать 127.0.0.1.
```
host    replication     all             127.0.0.1/32            trust
```
Сохраните изменения и выйдите из nano.
Перезапуск основного сервера:

Перезапустите сервер PostgreSQL, чтобы применить изменения:
```
sudo pg_ctlcluster 14 main restart
```
Вы должны увидеть вывод, похожий на:
```
Waiting for server to shut down.... done
server stopped
server starting
```

Настройка сервера-реплики (Replica Server)

На этом шаге вы настроите сервер-реплику для потоковой передачи записей WAL с основного сервера.

Остановка основного сервера:

Остановите основной сервер PostgreSQL:
```
sudo pg_ctlcluster 14 main stop
```
Создание базовой резервной копии (Base Backup):

Создайте базовую резервную копию каталога данных основного сервера с помощью pg_basebackup. Это скопирует файлы данных в каталог данных реплики (~/project/replica_data).
```
sudo pg_basebackup -h localhost -p 5432 -U postgres -D ~/project/replica_data -X stream -P -v
```
Вам будет предложено ввести пароль пользователя postgres. Введите labex.
Создание файла standby.signal:

Создайте файл standby.signal в каталоге данных реплики. Этот файл сигнализирует PostgreSQL, что этот экземпляр должен запуститься как горячий резерв (hot standby).
```
touch ~/project/replica_data/standby.signal
```
Запуск сервера-реплики:

Запустите сервер-реплику, указав каталог ~/project/replica_data:
```
sudo pg_ctlcluster 14 main start -D ~/project/replica_data
```

Проверка статуса репликации и создание тестовой таблицы

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

Подключение к основному серверу:

Подключитесь к основному серверу PostgreSQL:
```
sudo -u postgres psql
```
Проверка статуса репликации:

Выполните следующий SQL-запрос, чтобы просмотреть статус репликации:
```
SELECT
    pid,
    client_addr,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS write_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;
```
Этот запрос показывает информацию о соединении репликации. Значения write_lag (задержка записи) и replay_lag (задержка воспроизведения), близкие к 0, указывают на то, что реплика синхронизирована.
Создание тестовой таблицы:

Создайте таблицу с именем test_table:
```
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);
```
Вставьте некоторые данные в таблицу:
```
INSERT INTO test_table (name) VALUES ('Replica Test Data 1');
INSERT INTO test_table (name) VALUES ('Replica Test Data 2');
```
Выйдите из psql на основном сервере:
```
\q
```

Запрос данных с сервера-реплики

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

Подключение к серверу-реплике:

Подключитесь к серверу PostgreSQL-реплике:
```
sudo -u postgres psql -h localhost -p 5432 -d postgres -W
```
Вам будет предложено ввести пароль для пользователя postgres. Введите labex.
Запрос к таблице test_table:

Выполните следующий запрос, чтобы выбрать все данные из таблицы test_table:
```
SELECT * FROM test_table;
```
Вы должны увидеть данные, которые вы вставили на основном сервере:
```
 id |       name
----+-----------------------
  1 | Replica Test Data 1
  2 | Replica Test Data 2
(2 rows)
```
Если вы видите данные, репликация работает правильно.
Вставка дополнительных данных на основном сервере:

Вернитесь в терминал, подключенный к основному серверу, и вставьте новую строку:
```
sudo -u postgres psql -c "INSERT INTO test_table (name) VALUES ('Replica Test Data 3');"
```
Повторный запрос к серверу-реплике:

Вернитесь в терминал, подключенный к серверу-реплике, и снова выполните запрос SELECT:
```
SELECT * FROM test_table;
```
Теперь вы должны увидеть новые данные:
```
 id |       name
----+-----------------------
  1 | Replica Test Data 1
  2 | Replica Test Data 2
  3 | Replica Test Data 3
(3 rows)
```
Выход с сервера-реплики:

Выйдите из psql на сервере-реплике:
```
\q
```

Безопасное завершение репликации

На этом шаге вы безопасно завершите настройку репликации.

Остановка сервера-реплики:

Остановите сервер-реплику:
```
sudo pg_ctlcluster 14 main stop -D ~/project/replica_data
```
Остановка основного сервера:

Остановите основной сервер:
```
sudo pg_ctlcluster 14 main stop
```
Очистка (необязательно):

Если вы хотите удалить настройку репликации, вы можете удалить каталог данных реплики. Будьте очень осторожны при этом, так как это удалит все данные на реплике.
```
sudo rm -rf ~/project/replica_data
```
Кроме того, верните изменения, внесенные в postgresql.conf и pg_hba.conf на основном сервере, если вам больше не нужна репликация.
- Возврат postgresql.conf:
```
sudo nano /etc/postgresql/14/main/postgresql.conf
```
  Верните wal_level обратно к minimal.
```
wal_level = minimal
```
  Верните max_wal_senders обратно к 0.
```
max_wal_senders = 0
```
  Верните listen_addresses обратно к localhost.
```
listen_addresses = 'localhost'
```
  Сохраните изменения и выйдите из редактора.
- Возврат pg_hba.conf:
```
sudo nano /etc/postgresql/14/main/pg_hba.conf
```
  Удалите строку, которую вы добавили для репликации.
  
  Сохраните изменения и выйдите из редактора.
- Перезапуск основного сервера:
  
  Перезапустите основной сервер, чтобы применить изменения конфигурации.
```
sudo pg_ctlcluster 14 main restart
```

Итог

В этой лабораторной работе вы настроили потоковую реплику (streaming replica) базы данных PostgreSQL. Это включало изменение файлов postgresql.conf и pg_hba.conf на основном сервере, создание базовой резервной копии (base backup), настройку сервера-реплики и проверку правильности работы репликации. Вы также узнали, как безопасно завершить настройку репликации.