Вступление: Почему я вообще это пишу?

Представьте: нужно обновить базу данных размером с небольшое озеро — целых 10 ТБ. Классические методы тут не работают. Сделать дамп и восстановить? Это ж на неделю бизнес встанет. Обновлять «в лоб» с 11-й до 17-й версии? Это рискованно и надолго всё упадёт.

Мне самому недавно пришлось это проделать, и я быстро понял, что логическая репликация — это единственный способ не стать самым ненавистным человеком в компании. Она позволяет сделать почти всё «на живую», а простой свести к «нескольким минутам».

Пока я готовился, я наступил на все возможные грабли — от устаревших расширений до разницы в ОС. Чтобы вы не повторяли моих ошибок, я решил написать этот неформальный гайд. Здесь — только суровая практика, без воды и заумных терминов.

Что будем двигать и куда:

• Откуда: Старый Master — наш «динозавр» Postgres 11 на CentOS 7 (имеет 1 Slave). IP 192.168.99.152

• Куда: Новый Master — свежий Postgres 17 на современной AlmaLinux 9. IP 192.168.99.156

• Размер данных: Около 10 ТБ (да-да, вы не ослышались), несколько  табличных пространств, схем...

• Главная цель: Сделать всё так, чтобы никто из пользователей ничего не заметил.

Почему только логическая репликация?

Когда у вас 10 ТБ данных, другие варианты даже не рассматривайте. Логическая репликация — это наш спасательный круг. Пока она работает, ваша основная база спокойно трудится, принимая данные. Всё, что вам нужно в день Х — это на несколько минут остановить запись на старом сервере и быстро переключиться на новый . Не чета многодневному простою с дампом!

Где я споткнулся (мои грабли, ваша памятка):

Самый неприятный сюрприз ждал в переходе с Python 2 на Python 3. В новых версиях Postgres (с 15-й) поддержку plpython2 просто выкинули. А в AlmaLinux 9 его и в помине нет! Так что если у вас, как и у меня, были функции на plpython2u, будьте готовы их переписать под plpython3u до того, как переключите трафик.

Что нужно для старта (минимум требований):

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

• Новый сервер: Готовая машина с AlmaLinux 9 и Postgres 17.

• Сеть: Старый и новый серверы должны «видеть» друг друга и быстро обмениваться данными.

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

• Ресурсы: на старом и новом сервере у меня 1 в 1 (по факту виртуалки).

Выделю 4 основных этапа.

I Подготовительные моменты:

1.       Меняем настройки Postgres на “Старый Master”, для поддержки логической репликации

2.       Меняем настройки Postgres на “Новый Master”, здесь готовим его по аналогии со “Старый Master”

3.       Создать табличные пространства на “Новый Master”.

4.       Перенести метаданные через дамп на “Новый Master”.

5.       Проблемы, какие я словил на шаге 4, и их решение

 

II Настройка публикации для логической репликации

1.       Получаем OID схем.

2.       Формируем список таблиц для публикации и создаем публикацию

3.       Настройка уникальности: Помогите базе данных понять, что делает строку особенной

 

III Создаем  подписку, следим за репликацией

1.       Создание публикации

2.       Диагностика

 

IV Включение логической репликации: Точка невозврата.

1.       Проверяем что пора

2.   Останавливаем приложения для финального переключения.»

3. Если нужно адаптируем функции под новое окружение

4. Актуализируем последовательности

5. Завершающие шаги

I Этап. Подготовительные моменты.

Здесь не спешим, подготовимся к II и III этапу заранее.

1. Меняем настройки Postgres на “Старый Master”, для поддержки логической репликации

vi /var/lib/pgsql/11/data/postgresql.conf

max_wal_size = 4GB # Мне хвавтило данной настройки, ее оставил как было. Но можно увеличить при необходимости до 32Gb

 wal_level = 'logical' # до этого стоял 'hot_standby' для потоковой репликации было достаточно, но для логической нужен 'logical', относительно  'hot_standby' он добавляет информацию, требуемую для поддержки логического декодирования.

 max_wal_senders = 10 # максимальное число подключений потоковой репликации (т.е. число процессов передачи WAL). С запасом , текущий мастер имееет  standby + бэкапа с Barman.

 max_replication_slots = 10 # максимальное число слотов репликации, (Внимание: если это число будет меньше чем текущее количество отслеживаемых источников репликации, то сервер не запустится). Параметр статический. Я установил, как у меня было изначально на мастере с запасом. Один слот для Standby, один на Barman и оставшиеся на логические реплики

 

Разрешаем подключение по репликации на “старом Master” (здесь все индивидуально,  создать отдельного пользователя можно, но я для простоты разрешил подключаться под postgres):

vi /var/lib/pgsql/11/data/pg_hba.conf

добавляем:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

host    replication     replica_user    subscriber_ip/32       md5

host    dbname          replica_user    subscriber_ip/32       md5

По METHOD на ваш выбор из используемого вами, если ничего не боимся можно и trust =)

Пример с trust:

vi /var/lib/pgsql/11/data/pg_hba.conf

# For logical replication

host    replication     postgres        192.168.2.153/32          trust

В отличии от физической репликации, где достаточно replication для передачи WAL-логов.

Для логической необходимо прямое подключение к БД для управления подписками и начального копирования данных.

Если будет что то не так, то при “ create subscription” получим примерно такое:

ERROR:  could not connect to the publisher: connection to server at "192.168.99.152", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.99.156", user "postgres", SSL off

 

2. Меняем настройки Postgres на “Новый Master”, здесь готовим его по аналогии со “Старый Master”

vi /var/lib/pgsql/17/data/postgresql.conf

max_sync_workers_per_subscription = 2 – (2 значение по умолчанию) число параллельных процессов, которые выполняют первоначальную заливку данных таблиц, так называемый первичный снапшот. В данный момент один процесс это одна таблица, таблица не может заливаться несколькими процессами одновременно. Параметр статический.

 

max_logical_replication_workers = 10 # хватило бы и  7, например, чтобы запустить спокойно 2 репликации одновременно. Как пишет автор статьи на которую я ссылаюсь,  расчет , что  1 сессия для инстанца реплики и по три сессии каждая активная база/репликация в момент ее первоначальной заливки, и в режиме синхронизации по одной на базу. Рекомендации также в начальной синхронизации льем по одной базе, которая копирует первоначальный снимок данных и синхронизируются все уже залитые. В последнем моменте получается одна сессия это Replica DB, например 2 сессий это 2 базы/реплики уже залившиеся на приемник и еще 3 сессии это заливка последней базы. Если я заливаю.2 БД по очереди то = 1+1*1+1*3 = 5 сессей бы хватило. Если 2 одновременно = 1+ 2*3= 7.  Параметр статический.

 wal_level = 'hot_standby' # как у меня стояло на изначальном мастере, если в Вашем сценарии на старом Master другое значение копируйте его.

 max_replication_slots = 10 # по аналогнии со старым Master.

max_wal_senders = 10 # по аналогнии со старым Master.

Остальные настройки по пямяти и т.д. как на “старом Master”.

3. Создать табличные пространства на “Новый Master”.

 Старый Master:

postgres=#  \db

        List of tablespaces

    Name    |  Owner   |  Location

------------+----------+------------

 data1   | admin    | /mnt1/data

 data2   | admin    | /mnt2/data

 pg_default | postgres |

 pg_global  | postgres |

 tempspace  | admin    | /mnt/temp

(5 rows)

Новый Master, смотрим какие есть и создаем недостающее:

смотрим какие есть табличные пространства

postgres=#  \db

Создаем необходимые директории и меняем владельца на postgres:postgres (у меня mnt1 и mnt2 это пример куда я монтировал диски, например для горячего и холодного хранения)

mkdir -p  /mnt1/data

chown -R postgres:postgres  /mnt1/data

postgres=# CREATE TABLESPACE data1

    OWNER admin

    LOCATION '| /mnt1/data;

и т.д.

4. Перенести метаданные через дамп на “Новый Master”.

С этого момента давайте не менять состав таблиц в старой БД.

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

db – псевдо имя моей БД.

Старый Master:

Делаем дамп метаданных

pg_dump -d db -s -f /mnt/backup/db_dump_schema.sql

С машины мастер где сделали дамп перемещаем его (так или как вам удобно)

scp -P ssh_port /mnt/backup/db_dump_schema.sql  root@192.168.99.156:/mnt/backup/db_dump_schema.sql

Новый Master:

Восстанавливаем

su - postgres

psql

Так как у меня не в дефолтной пространстве создаю с указанием табличного пространства

postgres=#  CREATE DATABASE db TABLESPACE data1;

Восстанавливаем

cat /mnt/backup/db_dump_schema.sql |psql -d db

 

Какие могут быть проблемы, ошибки: EXTENSION и Roles, если упустили

/usr/pgsql-17/share/extension/plpython2u.control": No such file or directory

ERROR:  role "admin" does not exist

5. Проблемы, какие я словил на шаге 4, и их решение

Здесь привыкаем перед выполнением команд переходить в нужную БД

\c db

5.1 Проблемы ролей

На старом (Master DB) смотрим пользователей:

\c db

 

\du+

db=#  \du+

                                                   List of roles

    Role name     |                         Attributes                         |      Member of      | Description

------------------+------------------------------------------------------------+---------------------+-------------

 admin                  | Superuser, Create role                                     | {pg_read_all_stats} |

next_user_ro       |                                                                            | {readonly}   |

 

Посмотреть у каких пользователей есть пароли

postgres=# SELECT * FROM pg_shadow;

 

Как можно перенести, На старом (Master DB) делаем дамп ролей:

pg_dumpall --roles-only -f roles_backup.sql

и выполнить получившиеся команды на новом.

 

На новом (Master DB),

Начиная с версии PostgreSQL 16 команда psql \du не показывает, в какие группы входит пользователь. Это связано с тем, что более подробную информацию о членстве в ролях выводит новая команда — psql \drg.

\c db

postgres=# \drg

    Role name  |     Member of     |   Options    | Grantor

-------------+-------------------+--------------+----------

 admin       | pg_read_all_stats | INHERIT, SET | postgres

 

Создание ролей  и назначение

CREATE ROLE readonly;

GRANT  readonly TO  next_user_ro;

И т.д. Пока списки не будут синхронизированы.

 

Что еще может быть, На старом (Master DB) было md5 и временно захотели оставить его. 

установите password_encryption = md5 в postgresql.conf

, измените метод аутентификации на md5 в pg_hba.conf, заменив scram-sha-256 на md5

 

И задаем пароли и шифрования, если пользователь уже создан.

ALTER USER your_user SET password_encryption = 'md5';

ALTER USER your_user WITH PASSWORD 'your_password';

5.2 Установка модулей Python

Наверняка вы знаете, что используете в Postgres. Как я вспоминал.

Смотрим что из модулей было поставлено на старом мастере и ставим на новом:

python -m pip freeze

Ставим на новом:

python -m pip install %Name_module%

Мне, например пришлось как минимум подготовить Python  - PL/Python на новом Master

Чтобы использовать PL/Python с Postgres, сначала необходимо установить Python с помощью установщиков с сайта python.org.

 

К сожалению, PostgreSQL может связываться только с определенной версией Python. Пожалуйста, установите правильную версию Python в зависимости от используемой версии PostgreSQL:

Скачиваем https://www.python.org/downloads/source/

Ставим postgresql-plpython3 в зависимости от ОС:

sudo apt -y install postgresql-plpython3-14

или

sudo dnf install -y postgresql17-plpython3

 

Пример текущей версии Python у меня.

python3 --version

Python 3.9.18

После этого можно установить EXTENSION см -  5.4 Не установленные EXTENSION

5.3 Заменил описание plpython2u на plpython3u

У меня были написаны функции на PL/Python2u, а начиная с Postgres 15 поддерживанется только PL/Python3u. Если у Вас такого нет, то смело пропускайте шаг.

Если сейчас произвести восстановление дампа, то мы получим ошибки, пример:

 "/usr/pgsql-17/share/extension/plpython2u.control": No such file or directory.

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

Но я для чистоты эксперимента решил их убрать. Проще будет не пропустить другие ошибки.

 

Я убрал упоминание plpython2u из дампа и заменил его на plpython3u.

Примеры упоминания: CREATE EXTENSION IF NOT EXISTS plpython2u WITH SCHEMA pg_catalog;

и в скрипте:   LANGUAGE plpython2u

 

Заменя прямо в дампе через команду sed:

sed 's/plpython2u/plpython3u/g' /mnt/backup/db_dump_schema.sql > /mnt/backup/central_db_dump_schema_new2.sql

5 .4 Не установленные EXTENSION

При восстановлении дампа мы можем получить ошибки про про пропущенные, не установленные EXTENSION, .. plpython2u.control": No such file or directory, или о нехватке ролей.

Смотрим на старом (Master DB) какие есть EXTENSION:

не забываем сначала перейти в нужную нам БД

postgres=# \с db

SELECT * FROM pg_extension;

 

Пример вывода:

   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition

--------------+----------+--------------+----------------+------------+-----------+--------------

 plpgsql      |       10 |           11 | f              | 1.0        |           |

plpython2u   |       10 |           11 | f              | 1.0        |           |

 

Ставим EXTENSION на новом Master:

postgres=# \с db

postgres=# CREATE EXTENSION plpython3u;

 

5.5 Возвращаемся к шагу 4, и повторяем восстановление дампа.

После того как восстановление дампа прошла успешно можно переходить к Этапу II

II. Настройка публикации для логической репликации

1.Получаем OID схем.

Так как у меня кроме схемы public используются и другие схемы, то сначала нужно узнать OID схем.

не забываем сначала перейти в нужную нам БД

postgres=# \с db

Получаем список схем, пример (по примеру у меня 2 схемы):

db=# \dn

       List of schemas

   Name   |       Owner

----------+-------------------

schema2 | admin

public   | pg_database_owner

 

Получаем OID, по имени схемы.

db=# SELECT to_regnamespace('public')::oid;

 to_regnamespace

-----------------

            2200

 

central_db=#  SELECT to_regnamespace(' schema2')::oid;

 to_regnamespace

-----------------

         1451154

2. Формируем список таблиц для публикации и создаем публикацию

на Старом Master:

Не забываем переключиться на нужную БД:

\c db

Можно сделать единый запрос по всем схемам, но я чтобы сверять кол-во сделал отдельные (Улучшенный запрос со схемой),

Для Public:

SELECT n.nspname '.' c.relname || ','

FROM pg_class c

JOIN pg_namespace n ON c.relnamespace = n.oid

WHERE c.relkind IN ('r','p')

AND c.relnamespace = 2200

ORDER BY c.relkind;

Пример вывода:

public.table1,

public.table2,

…

(111 rows) 97

 

Для schema2:

SELECT n.nspname '.' c.relname || ','

FROM pg_class c

JOIN pg_namespace n ON c.relnamespace = n.oid

WHERE c.relkind IN ('r','p')

AND c.relnamespace = 404429121

ORDER BY c.relkind;

Пример вывода:

schema2.table1,

schema2.table2,

..

(222 rows)

Кому интересно, список легко получить из notepad++ убрав переносы:

“How do I remove linebreaks in Notepad++?

Highlight the lines you want to join (or use Ctrl + A to select everything)

Choose Edit → Line Operations → Join Lines from the menu or press Ctrl + J.”

В итоге формируем конечный запрос по созданию  публикации, совместив эти 2 списка:

create publication pub_db for table public.table1, public.table2, schema2.table1, schema2.table2, ..

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

Выполняем запрос на старом Master.

 

Какие ошибки я получил:

ERROR:  "part_table" is a partitioned table

DETAIL:  Adding partitioned tables to publications is not supported.

HINT:  You can add the table partitions individually.

Остались такие пустые, после включения партиционирования.

Удалил из запроса их и записал себе их, и их кол-во.

Допустим у меня было 333 таблицы и 3 из них партиционированые.

Итого 300 таблиц ушло в запрос.

 

Проверяем нашу публикацию.

db=# select * from pg_publication;

    pubname     | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate

----------------+----------+--------------+-----------+-----------+-----------+-------------

 pub_db |       10 | f            | t         | t         | t         | t

(1 row)

3. Настройка уникальности: Помогите базе данных понять, что делает строку особенной

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

Иерархия выбора: от идеала к надежному запасному варианту

a)Золотой стандарт — PRIMARY KEY: Если у таблицы есть первичный ключ — это идеальный вариант. База будет использовать его для идентификации строк, это быстро и эффективно.

b)Серебряная пуля — UNIQUE индекс/ограничение: Нет первичного ключа, но есть колонка (или набор колонок) с уникальным индексом? Отлично! Можно указать его. Главное — убедиться, что он действительно UNIQUE NOT NULL.

c)Тяжелая артиллерия — FULL: Если в таблице нет ни первичного ключа, ни подходящего уникального индекса... что ж, заставляем репликацию считать, что вся строка целиком — это ее уникальный идентификатор. Способ надежный, но менее производительный, так как для поиска строки будет сравниваться содержимое всех колонок.

 

Матушка-лень в помощь: Генерируем скрипт автоматически

Вручную проверять сотни таблиц — занятие для мазохистов. На помощь приходит наша верная спутница — лень, которая, как известно, двигатель прогресса. Она шепчет: «Поручи это psql!»

3.1 Генерим запросы для таблиц с PRIMARY KEY на Старом Master

Если у Вас только Public схема, то хватит этого. (если есть другие схемы он добавит схему не только в имя таблицы но и индекса – что не работает =)

SELECT 'alter table '||i.indrelid::regclass||' REPLICA IDENTITY USING INDEX '||i.indexrelid::regclass||';' FROM (SELECT i.indexrelid, i.indrelid, i.indisprimary FROM pg_index i WHERE CAST(indrelid::regclass as text) !~ '^pg_' AND i.indisprimary=true ) i;

Доработанный запрос:

SELECT 'alter table '||i.indrelid::regclass||' REPLICA IDENTITY USING INDEX '||

       CASE

           WHEN i.indexrelid::regclass::text LIKE '%.%' THEN split_part(i.indexrelid::regclass::text, '.', 2)

           ELSE i.indexrelid::regclass::text

       END||';'

FROM (

    SELECT i.indexrelid, i.indrelid, i.indisprimary

    FROM pg_index i

    WHERE CAST(indrelid::regclass as text) !~ '^pg_' AND i.indisprimary=true

) i;

 

 

Пример вывода:

 alter table table_on_pubic REPLICA IDENTITY USING INDEX table_index_on_public;

 alter table schema1.table_on_schema1 REPLICA IDENTITY USING INDEX table_index_on_schema1;

…

(290 rows)

3.2. Генерим скрипт для таблиц с уникальным индексом

SELECT nmr, 'alter table '||i.indrelid::regclass||' REPLICA IDENTITY USING INDEX '||i.indexrelid::regclass||';', i.indisprimary PK, i.indisunique UNQ FROM (

 SELECT row_number() over (partition by i.indrelid) nmr, i.indexrelid, i.indrelid, i.indisprimary, i.indisunique

 FROM pg_index i WHERE cast(indrelid::regclass as text) !~ '^pg_' AND i.indisunique AND indrelid NOT IN (SELECT distinct indrelid FROM pg_index WHERE indisprimary)

 ) i WHERE nmr = 1;

Пример вывода:

ALTER TABLE schema1.atable REPLICA IDENTITY USING INDEX "atable$unique_prep_id_doctype_id";

(1 rows)

 

3.3 Для остальных таблиц генерим скрипт с опцией REPLICA IDENTITY FULL

Обратите внимание в примере у меня 2 schem-ы, public 2200 и еще одна используемая мной 404429121, список можно расширить при необходимости.

 

SELECT

    'ALTER TABLE ' n.nspname '.' c.relname ' REPLICA IDENTITY FULL;' AS alter_command

FROM

    pg_class c

JOIN

    pg_namespace n ON c.relnamespace = n.oid

WHERE

    c.relkind = 'r'

    AND c.relnamespace IN (404429121, 2200)

    AND NOT EXISTS (

        SELECT 1

        FROM pg_index i

        WHERE i.indrelid = c.oid

        AND (i.indisprimary OR i.indisunique)

    );

(12 rows)

Сумма таблиц отданных на публикацию и тех которым мы настроили уникальность должно совпадать. У меня не совпало из-за партиционированных таблиц. Я сделал списки, сравнил и именно только они послужили расхождением (см. Ошибку при создании публикации).

 

Способы узнать текущее значение REPLICA IDENTITY для таблицы:

SELECT relname, relreplident

FROM pg_class

WHERE relname = 'org_permission';

Результат:

d - DEFAULT (используется первичный ключ если есть)

n - NOTHING

f - FULL

i - USING INDEX

III Создаем  подписку, следим за репликацией

1. Создание подписки

Итак, все приготовления позади. На столе — третий кофе, на мониторах — десятки открытых ssh-сессий, а в календаре — долгожданные длинные выходные. Активность в базе упала до минимального фонового шума, будто сама система затаила дыхание в ожидании. Самое время начинать.

Прежде чем нажать красную кнопку, я пробежался по чек-листу:

• Бэкапы: Актуальная резервная копия старого мастера? Есть!

• Мониторинг: Графики репликации и лага открыты на отдельном мониторе? Запускаю!

• Роллбек-план: Чёткий сценарий отката на случай ЧП? Лежит на видном месте!

• Настроен Slave для нового Master если есть ресурсы, или план как его быстро поднять, как только освободим ресурсы из-под старого Slave.

Публикация сделана на старом Master. Пора включать подписку на новом Master.

Подключаемся в первую базу и в ней создаем подписку. (на новом Сервере!)

192.168.99.156 - новый Master

192.168.99.152 - старый Master

create subscription pub_ db1 CONNECTION 'dbname=central_db host=192.168.99.152 user=postgres' PUBLICATION pub_db;

NOTICE:  created replication slot "sub_ux1" on publisher

CREATE SUBSCRIPTION

Если плохо настроили  pg_hba.conf, можно получить подобную ошибку

ERROR:  could not connect to the publisher: connection to server at "192.168.99.152", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.99.156", user "postgres", SSL off

Про настройку писал выше, пример #

 vi /var/lib/pgsql/11/data/pg_hba.conf

Добавил:

# For logical replication

host    replication     postgres        192.168.99.156/32          trust"

 

На старом (Master DB). Переходим в интересующую нас БД.

Проверить текущую активность:

SELECT * FROM pg_stat_replication;

Пример вывода:

31940 |       10 | postgres | pg_2897818_sync_2896605_7448579472542451702 | 192.168.99.156 |              

31939 |       10 | postgres | pg_2897818_sync_2896533_7448579472542451702 | 192.168.99.156

31934 |       10 | postgres | pub_central_db1                             | 192.168.99.156 |       

На новом (Master DB). Переходим в интересующую нас БД.

\c db

SELECT * FROM pg_stat_subscription;

Прежде чем уйти пить чай и ждать несколько дней, пока 10 ТБ данных перетекут на новый сервер, стоит сделать контрольный снимок прогресса.

2. Диагностика

 Узнать какие таблицы синхронизируются:

SELECT

    s.pid,

    s.relid,

    c.relname,

    n.nspname as schema_name

FROM pg_stat_subscription s

JOIN pg_class c ON c.oid = s.relid

JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE s.worker_type = 'table synchronization';

 

Из запроса получаем: pid  |  relid

Проверить прогресс синхронизации

SELECT
a.pid,
a.query,
a.state,
a.wait_event_type,
a.wait_event
FROM pg_stat_activity a
WHERE a.pid IN (30298, 30299 );

А теперь по relid увидим как таблицы растут (увидим таблицы и текущий их размер):

SELECT

    c.oid,

    n.nspname,

    c.relname,

    pg_size_pretty(pg_total_relation_size(c.oid)) as size

FROM pg_class c

JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE c.oid IN (2896533 ,2896605 );

Еще можно посмотреть по всем таблицам кол-во строк, но метод не точный если таблица большая  будет разница +- (но для успокоения души, посмотреть сколько осталось, все ли таблицы наполнились)

SELECT

    schemaname AS schema_name,

    relname AS table_name,

    n_live_tup AS row_count

FROM

    pg_stat_user_tables

ORDER BY

    schema_name, table_name;

Более точно посмотреть:

select count(1) from       table;

Основная работа сделана, репликация запущена. Теперь данные будут тихо и методично перетекать с одного сервера на другой. Этот процесс может занять несколько дней, и это абсолютно нормально.

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

 

Проверка прогресса по таблицам:

-- На ПОДПИСЧИКЕ - сравниваем примерное количество строк На новом (Master DB).

WITH source_counts AS (

    SELECT schemaname, relname, n_live_tup

    FROM pg_stat_user_tables

)

SELECT

    s.schemaname,

    s.relname,

    s.n_live_tup AS source_rows,

    d.n_live_tup AS dest_rows,

    CASE

        WHEN d.n_live_tup = 0 THEN 0

        ELSE (s.n_live_tup::float / d.n_live_tup::float * 100)

    END AS sync_percent

FROM source_counts s

JOIN pg_stat_user_tables d ON d.schemaname = s.schemaname AND d.relname = s.relname

WHERE s.n_live_tup > 0

ORDER BY sync_percent ASC;

Увидим таблицы и процент синхронизации.

-- На ПОДПИСЧИКЕ - получаем текущий LSN

SELECT pg_current_wal_lsn();

 

-- На ИЗДАТЕЛЕ - сравниваем LSN

SELECT

    slot_name,

    confirmed_flush_lsn,

    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes,

    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag_pretty

FROM pg_replication_slots

WHERE slot_type = 'logical';

Видим какой сейчас лаг.

Еще можно посмотреть размер таблиц, но по кол-ву строк надежнее:

SELECT nspname '.' relname AS "relation",

    pg_size_pretty(pg_relation_size(C.oid)) AS "size"

  FROM pg_class C

  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

  WHERE nspname NOT IN ('pg_catalog', 'information_schema')

  ORDER BY pg_relation_size(C.oid) DESC;

Размер может отличаться, в новой версии данные хранятся оптимальнее.

IV Переключение нагрузки.

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

• Стабилизация размера БД: Объем базы-приемника перестает резко увеличиваться и почти сравнивается с источником.

• Завершение массового копирования: В системном представлении pg_replication_slots пропадают временные процессы, отвечавшие за выгрузку данных каждой таблицы.

• Статус 'ready': В представлении pg_subscription_rel все таблицы получают статус r (ready), что означает успешное завершение их первоначального копирования.

1.       Проверяем что пора

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

Запрос 1

-- На ПОДПИСЧИКЕ (новый мастер)

SELECT

    sr.srsubid,

    s.subname,

    sr.srrelid::regclass AS table_name,

    sr.srsubstate,

    CASE sr.srsubstate

        WHEN 'i' THEN 'initializing'

        WHEN 'd' THEN 'data is being copied'

        WHEN 'f' THEN 'finished table copy'

        WHEN 's' THEN 'synchronized'

        WHEN 'r' THEN 'ready'

        ELSE 'unknown'

    END AS state_description,

    sr.srsublsn

FROM pg_subscription_rel sr

JOIN pg_subscription s ON s.oid = sr.srsubid

ORDER BY sr.srsubstate, sr.srrelid::regclass;

 

Что смотрим:

• 'd' - данные копируются (этот процесс идет)

• 'r' - готово (все таблицы должны быть в этом статусе по окончании)

• initializing  - еще в очереди

 

 

 

Мониторинг слотов репликации на издателе

Запрос 2

-- На ИЗДАТЕЛЕ (старый мастер)

SELECT

    slot_name,

    slot_type,

    active,

    confirmed_flush_lsn,

    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag_size,

    database,

    temporary  -- Временные слоты для копирования данных

FROM pg_replication_slots

WHERE slot_type = 'logical'

ORDER BY active DESC, slot_name;

Что смотрим:

• temporary = true - временные слоты для копирования (должны исчезнуть)

• active = true/false - активность слота

 

Сравнение размеров баз данных

Запрос 3

-- На ИЗДАТЕЛЕ - размер исходной БД

SELECT

    pg_database.datname,

    pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database

WHERE datname = 'your_database_name';

 

-- На ПОДПИСЧИКЕ - размер новой БД 

SELECT

    pg_database.datname,

    pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database

WHERE datname = 'your_database_name';

 

Детальный мониторинг прогресса по отдельным таблицам

Запрос 4

-- На ПОДПИСЧИКЕ - прогресс копирования таблиц

SELECT

    s.subname,

    sr.srrelid::regclass AS table_name,

    sr.srsubstate,

    CASE sr.srsubstate

        WHEN 'd' THEN 'COPYING DATA'

        WHEN 'r' THEN 'READY ✓'

        ELSE sr.srsubstate

    END AS status,

    (SELECT n_live_tup FROM pg_stat_user_tables WHERE relid = sr.srrelid) AS rows_on_subscriber

FROM pg_subscription_rel sr

JOIN pg_subscription s ON s.oid = sr.srsubid

WHERE sr.srsubstate IN ('d', 'f')  -- Показываем только активные процессы

ORDER BY sr.srsubstate;

Автоматическая проверка всех признаков завершения

Запрос 5

-- На ПОДПИСЧИКЕ - комплексная проверка

WITH subscription_status AS (

    SELECT

        COUNT(*) AS total_tables,

        COUNT(CASE WHEN srsubstate = 'r' THEN 1 END) AS ready_tables,

        COUNT(CASE WHEN srsubstate = 'd' THEN 1 END) AS copying_tables

    FROM pg_subscription_rel

)

SELECT

    total_tables,

    ready_tables,

    copying_tables,

    CASE

        WHEN copying_tables = 0 AND ready_tables = total_tables THEN 'INITIAL SYNC COMPLETED ✓'

        WHEN copying_tables > 0 THEN 'INITIAL SYNC IN PROGRESS...'

        ELSE 'CHECK STATUS'

    END AS sync_status

FROM subscription_status;

 

Как интерпретировать результаты:

Процесс идет:

• В pg_subscription_rel есть таблицы со статусом 'd' (data is being copied)

• В pg_replication_slots есть временные (temporary = true) активные слоты

Процесс завершен:

• Все таблицы в pg_subscription_rel имеют статус 'r' (ready)

• Временные слоты репликации исчезли

• Размеры баз данных практически одинаковы

Рекомендую запускать запрос №1 и №5 для быстрого контроля прогресса!

2.  Останавливаем приложения для финального переключения.

Данные успешно перетекли! Все 10 ТБ уютно устроились на новом сервере. Лаг репликации практически нулевой. Пора гасить старичка и запускать обновленную инфраструктуру — финальный аккорд!»

 

Сначала останавливаем приложения взаимодействующие с базой, проверяем, что лишних соединений к БД нет.

На новом Master.

Останавливаем подписку и удаляем.

# ALTER SUBSCRIPTION sub_ux1 DISABLE;

# DROP SUBSCRIPTION sub_ux1

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

# SELECT * from pg_replication_slots;

 

Что за Проблема осталась : После восстановления из дампа все последовательности (sequence) сбросились и начинаются с 1. Но в основной базе они уже давно ушли далеко вперёд. Если мы сейчас переключим приложение, оно попытается вставить запись с ID, который уже существует, и получит ошибку конфликта. Новые данные просто не смогут записываться.

Решение: Нужно «подкрутить» все последовательности на новом сервере до актуальных значений. Сгенерируем для этого скрипт.

Запускаем на старом Master:

SELECT 'SELECT setval('''n.nspname'.'c.relname''','nextval((n.nspname'.'c.relname)::regclass)');'
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind='S';

Будет список команд такого плана SELECT setval (‘app_seq’,10111); - на каждую последовательность в вашей базе.

Выполняем это в каждой базе! Один раз! новом Master:

SELECT setval (‘app_seq’,10111);

...

3. Если нужно адаптируем функции под новое окружение

Напомню о проблеме с переходом с Python 2 на 3. Функции, использующие plpython2u, нужно переписать.

-- Обновляем функцию для работы с HTTP-запросами

CREATE OR REPLACE FUNCTION public.http_get_bytea(uri character varying)

RETURNS bytea

LANGUAGE plpython3u  -- Меняем движок на plpython3u

AS $$

from urllib.request import urlopen  # Используем новый API Python 3

data = urlopen(uri)

return data.read()

$$;

return data.read()

$$;

Завершение

Чтобы случайно приложения не подключились я убрал VIP-адрес со старого Master.

Теперь можно данный VIP-адрес переместить на новый Master и начать запускать приложения.

К ночи, когда низкая активность не забудьте скинуть статистику и запустить vacuum analyze;

P.S.

«На просторах Habr я нашел отличную статью-родственницу. Автору отдельный респект — его материал стал для меня отличной отправной точкой. Однако в реальном бою с промышленной базой данных я столкнулся с целым рядом подводных камней, которые в статье, увы, освещены не были.»