Міграція з MariaDB до PostgreSQL: без простоїв і без сюрпризів

Виберіть правильний формат міграції (і чому “just dump/restore” бреше)

Є три загальні підходи до переміщення з MariaDB до PostgreSQL:

1. Big-bang (дамп/відновлення): зупинити запис, зробити знімок, відновити в Postgres, перемкнути додаток. Це чисто, легко зрозуміти, але вимагає простою. Навіть якщо ви називаєте це «режим обслуговування», користувачі називають це «лажа».
2. Shadow read: перенести дані й поступово спрямовувати read‑трафік на PostgreSQL, поки записи досі йдуть у MariaDB. Корисно для валідації продуктивності. Недостатньо для повного cutover без стратегії для записів.
3. Zero-downtime (backfill + CDC + контрольований cutover): тримати MariaDB джерелом істини, виконати backfill історичних даних у PostgreSQL, потім застосовувати поточні зміни через change data capture (CDC) або dual writes. Переключати читання й записи плановано та з можливістю відкату.

Для «без простоїв і без сюрпризів» вам підходить варіант №3. Це більше роботи спочатку, але вона мінімізує паніку. Ви отримуєте:

• Повторюваний пайплайн, який можна запускати в staging і prod.
• Квантифікований лаг (секунди позаду, а не відчуття).
• План відкату, що не включає молитви.

Операційна істина: якщо ви не можете виміряти replication lag і розбіжності даних, у вас немає міграції. У вас є надія.

Факти й історичний контекст, які справді важливі

• MariaDB з’явилася через кризу довіри. Вона була створена оригінальними розробниками MySQL після придбання MySQL компанією Oracle, з обіцянкою залишатися відкритим і під контролем спільноти.
• Родовід PostgreSQL має академічні корені. Він розвинувся з проєкту POSTGRES в UC Berkeley, тому система дуже приділяє увагу коректності.
• InnoDB став стандартним рушієм MySQL/MariaDB не випадково. Обмеження MyISAM щодо блокувань на рівні таблиці й відновлення після збоїв навчили інженерів жорстких уроків.
• MVCC у PostgreSQL — це «завжди увімкнено». Воно впливає на vacuuming, bloat та поведінку індексів; це не перемикач, який можна ігнорувати.
• Реплікація в MariaDB історично розвивалася навколо statement/binlog підходів. Інструменти CDC часто покладаються на цю екосистему; ви успадковуєте її крайні випадки (DDL, недетермінованість, часові зони).
• PostgreSQL по‑іншому трактує ідентифікатори. Неоковані ідентифікатори зводяться до lowercase, що тихо ламає міграції з екосистем, які покладаються на регіонезалежну поведінку.
• JSONB у PostgreSQL — це не просто «JSON, але швидше». Він зберігає розпарене представлення з іншою поведінкою індексування і тонкими відмінностями в семантиці рівності.
• UTF-8 не завжди був нормою. Колачії та набори символів залишаються міграційною мінною полем, бо «та сама строка» може означати «інший порядок».
• «Нульові» дати в MariaDB були сумісницьким костилем. PostgreSQL їх відкидає. Ваш код додатку має стати дорослішим.

MariaDB vs PostgreSQL: відмінності, що болять у продакшні

SQL‑діалект і типова система: суворість — це не баг

MariaDB (як і MySQL) історично лояльна: неявні приведення, мовчазне обрізання залежно від SQL mode, «допоміжні» конверсії. PostgreSQL суворіший і більш явний. Це добре для коректності, але воно виявляє ті частини додатку, що користувалися невизначеною поведінкою.

Приклади, що часто ламаються:

• Неявні приведення рядок→int, які раніше працювали, тепер викликають помилки.
• Відмінності в поведінці GROUP BY при виборі неагрегованих стовпців.
• За замовчуванням timestamp і робота з часовими зонами.
• Порядок сортування NULL і колації, що впливають на пагінацію.

Транзакції й блокування: ті самі слова, інші наслідки

Обидві СУБД мають транзакції, рівні ізоляції й можуть мати deadlock’и. Але форма інша:

• PostgreSQL використовує MVCC плюс блокування на рівні рядка; читачі не блокують писачів і навпаки, але довгі транзакції перешкоджають прибиранню й створюють bloat.
• MariaDB/InnoDB теж використовує MVCC, але патерни блокувань навколо gap‑locks, next‑key locks і перевірок унікальності проявляються інакше, особливо під REPEATABLE READ.
• DDL‑блокування відрізняються. PostgreSQL може виконувати багато операцій конкурентно, але не всі; MariaDB має власні правила «онлайн DDL», що залежать від версії й рушія.

AUTO_INCREMENT vs sequences: не можна ігнорувати відображення

MariaDB’s AUTO_INCREMENT прив’язаний до метаданих таблиці. PostgreSQL зазвичай використовує SEQUENCE об’єкти, часто через GENERATED ... AS IDENTITY. Під час міграції потрібно правильно встановити значення sequence, інакше після cutover будуть колізії первинних ключів.

Кодування та колації: тихий вбивця коректності

Навіть якщо у вас «всі UTF-8», колації різняться. Порядок і приведення регістру відрізняються. Це ламає:

• Унікальні обмеження (дві строки в одній системі вважаються рівними, а в іншій — різними, або навпаки).
• Використання індексів (залежить від колації).
• Пагінацію при сортуванні по текстових колонках (користувачі бачать дублікати або пропущені рядки між сторінками).

Операційна модель: vacuum — ваша нова регулярна зустріч

PostgreSQL потребує vacuum для звільнення простору й підтримки visibility maps у здоровому стані. Autovacuum зазвичай підходить — доки ні. Якщо у вас багато оновлень та довгі транзакції, ви пізнаєте, що таке «bloat» у персональному вимірі.

Цитата, яку варто тримати на липкій нотатці:

«Надія — це не стратегія.» — генерал Gordon R. Sullivan

Жарт №1: Якщо ваш план міграції — «ми будемо моніторити», у вас немає плану — у вас хобі.

Архітектура без простоїв: backfill + CDC + контрольований cutover

Базовий патерн

Розумна стратегія без простого зазвичай виглядає так:

1. Підготуйте Postgres: схема, ролі, розширення, налаштування параметрів, видимість/моніторинг.
2. Початковий backfill: скопіюйте існуючі таблиці з MariaDB у Postgres, поки додаток залишається онлайн і записує в MariaDB.
3. CDC / реплікація в Postgres: стрімте поточні зміни (insert/update/delete) з binlog MariaDB у Postgres, щоб тримати його майже в реальному часі.
4. Валідація узгодженості: контрольні суми, підрахунки, вибіркові порівняння рядків і бізнес‑інваріанти.
5. Cutover: переключайте читання, потім записи (або зробіть обидва з короткою паузою на записи, що вимірюється секундами, не хвилинами).
6. Після‑cutover зміцнення: налаштування індексів, вакуума, виправлення запитів і вікно для відкату, поки MariaDB лишається доступною.

CDC vs dual-write: виберіть свою отруту уважно

CDC (рекомендовано) означає, що додаток продовжує писати лише в MariaDB до моменту cutover. Ви реплікуєте зміни в Postgres зовні. Це зменшує складність додатку й уникає помилок консистентності при dual‑write. Ціна — надійний CDC‑пайплайн і план для DDL‑змін під час синхронізації.

Dual-write означає, що ваш додаток певний час пише в обидві бази. Це може працювати, але ви тепер відповідаєте за проблему розподілених транзакцій. Якщо робите так, треба проектувати на випадки часткової відмови: один запис успішний, інший — ні, і система повинна це узгодити. Більшість команд переоцінюють свою готовність до цього.

Практична порада: якщо можна уникнути dual‑write — уникайте. CDC плюс контрольований cutover менш героїчні й більш відтворювані.

Механіка cutover, що не зіпсує вихідні

Найчистіший cutover — «спочатку читання, потім записи»:

• Перемістіть read‑тільки трафік (звітність, батч‑джоби) на Postgres раніше, щоб виявити різниці в запитах і прогалини в індексах.
• Тримайте OLTP‑записи в MariaDB, поки CDC тримає Postgres актуальним.
• Заплануйте коротку паузу на записи (секунди до кількох хвилин), щоб звести лаг до нуля, перевірити, а потім перемкнути записи.

Якщо продуктова команда наполягає на «буквально нульовій паузі для записів», це можна зробити, але ви заплатите складністю: идемпотентні записи, конфлікт‑резолюція й іноді тимчасова система логічного годинника. Для більшості бізнесів коротка пауза на записи — найдешевша чесність, яку ви можете купити.

DDL під час міграції: ворог простоти

Зміни схеми під час міграції — це місце, де графіки вмирають. Ведіть це свідомо:

• Заморозьте DDL під час фінального вікна синхронізації. Зробіть це політикою, не пропозицією.
• Якщо потрібно застосувати DDL, застосуйте його в обох системах і перевірте, що ваш CDC‑інструмент коректно це обробляє (багато інструментів цього не роблять або підтримують лише підмножину).
• Віддавайте перевагу сумісному зворотно еволюційному підходу (додавати nullable‑стовпці, робити backfill, а потім накладати обмеження пізніше).

Практичні завдання з командами: що ви запускаєте, що це означає, як вирішувати

Це завдання, що перетворюють «проєкт міграції» на «міграцію з підтвердженнями». Команди репрезентативні й придатні для запуску; адаптуйте креденшіали та хости.

Завдання 1: Перевірити бінлог MariaDB (потрібне для CDC)

cr0x@server:~$ mysql -h mariadb01 -u admin -p -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW VARIABLES LIKE 'server_id';"
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | ROW       |
+---------------+-----------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+

Що це означає: log_bin=ON і binlog_format=ROW — стандартна база для надійного CDC. server_id має бути встановлений і унікальний.

Рішення: Якщо бінлог вимкнений або не ROW — виправте це перш ніж щось робити. Реплікація на основі statement рано чи пізно створить момент «але в staging працювало».

Завдання 2: Перевірити GTID у MariaDB (допомагає відновлювати CDC)

cr0x@server:~$ mysql -h mariadb01 -u admin -p -e "SHOW VARIABLES LIKE 'gtid_strict_mode'; SHOW VARIABLES LIKE 'gtid_binlog_pos';"
Enter password:
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| gtid_strict_mode | ON    |
+------------------+-------+
+-----------------+------------------------------------------+
| Variable_name   | Value                                    |
+-----------------+------------------------------------------+
| gtid_binlog_pos | 0-101-9876543                            |
+-----------------+------------------------------------------+

Що це означає: GTID полегшує перезапуск CDC‑пайплайну без вгадування файлу/позиції binlog.

Рішення: Якщо GTID не ввімкнено, ви все ще можете робити CDC, але оперативне навантаження зростає. Вирішіть рано, чи стандартизуєтесь на GTID.

Завдання 3: Інвентаризація фіч схеми, що не транслюються чисто

cr0x@server:~$ mysql -h mariadb01 -u admin -p -N -e "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys') ORDER BY TABLE_SCHEMA, TABLE_NAME LIMIT 10;"
Enter password:
appdb	users	InnoDB	utf8mb4_unicode_ci
appdb	orders	InnoDB	utf8mb4_unicode_ci
appdb	order_items	InnoDB	utf8mb4_unicode_ci
appdb	audit_log	InnoDB	utf8mb4_unicode_ci
appdb	sessions	InnoDB	utf8mb4_unicode_ci
appdb	settings	InnoDB	utf8mb4_unicode_ci
appdb	products	InnoDB	utf8mb4_unicode_ci
appdb	inventory	InnoDB	utf8mb4_unicode_ci
appdb	payments	InnoDB	utf8mb4_unicode_ci
appdb	refunds	InnoDB	utf8mb4_unicode_ci

Що це означає: Ви перевіряєте рушії та колації. Не‑InnoDB таблиці, дивні колації й застарілі кодування множать ризики міграції.

Рішення: Якщо знайдете MyISAM/Aria або змішані колації — заплануйте час на нормалізацію або явне відображення поведінки в Postgres (включно з очікуваннями на рівні додатку).

Завдання 4: Зчитати SQL mode MariaDB (скаже, скільки у вас «поганих» даних)

cr0x@server:~$ mysql -h mariadb01 -u admin -p -e "SELECT @@sql_mode;"
Enter password:
+----------------------------------------------------------------------------------+
| @@sql_mode                                                                       |
+----------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------+

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

Рішення: Якщо strictness слабкий, запустіть перевірки якості даних до міграції. Postgres відкине деякі значення, які MariaDB приймала.

Завдання 5: Виявити «нульові дати» й недійсні timestamp’и

cr0x@server:~$ mysql -h mariadb01 -u admin -p -e "SELECT COUNT(*) AS zero_dates FROM appdb.orders WHERE created_at='0000-00-00 00:00:00';"
Enter password:
+-----------+
| zero_dates|
+-----------+
| 42        |
+-----------+

Що це означає: PostgreSQL не підтримує timestamp з роком 0000. Такі рядки не завантажаться без трансформації.

Рішення: Визначте політику: конвертувати в NULL, у epoch або тимчасово зберігати як текст. Задокументуйте це; аудитори згодом питатимуть.

Завдання 6: Розгорнути Postgres з базовими налаштуваннями й перевірити

cr0x@server:~$ psql -h pg01 -U postgres -c "SHOW server_version; SHOW wal_level; SHOW max_wal_senders;"
 server_version
----------------
 16.3
(1 row)

 wal_level
-----------
 replica
(1 row)

 max_wal_senders
-----------------
 10
(1 row)

Що це означає: Ви підтверджуєте цільовий рушій і налаштування реплікації (навіть якщо не використовуєте PG‑реплікацію, поведінка WAL впливає на операції).

Рішення: Якщо плануєте логічну реплікацію або інтенсивне приймання даних — свідомо налаштуйте WAL та чекпоінти. Значення за замовчуванням — не план продуктивності.

Завдання 7: Створити ролі й застосувати принцип найменших привілеїв рано

cr0x@server:~$ psql -h pg01 -U postgres -c "CREATE ROLE app_user LOGIN PASSWORD 'REDACTED'; GRANT CONNECT ON DATABASE appdb TO app_user;"
CREATE ROLE
GRANT

Що це означає: Ви уникаєте класичної брехні «ми виправимо права після cutover».

Рішення: Якщо ваш додаток зараз покладається на широкі привілеї (наприклад, DDL) — вирішіть, чи зберігати це (не рекомендовано), чи рефакторити зараз.

Завдання 8: Конвертувати схему (приклад з pgloader) й перевірити результати

cr0x@server:~$ pgloader mysql://migrate:REDACTED@mariadb01/appdb postgresql://postgres@pg01/appdb
2025-12-30T10:01:12.000000Z LOG pgloader version "3.6.9"
2025-12-30T10:01:13.000000Z LOG Migrating from #<MYSQL-CONNECTION mysql://migrate@.../appdb>
2025-12-30T10:01:13.000000Z LOG Migrating into #<PGSQL-CONNECTION postgresql://postgres@pg01/appdb>
2025-12-30T10:05:44.000000Z LOG Data errors in table "orders": 0
2025-12-30T10:05:44.000000Z LOG Total import time: 00:04:31

Що це означає: Інструмент зробив перший прохід схеми й даних. Рядок журналу про помилки даних — перший індикатор тривоги.

Рішення: Розглядайте це як підготовчу роботу, а не остаточний результат. Перевірте типи, обмеження, індекси й sequences; потім запустіть CDC для поточних змін.

Завдання 9: Підтвердити, що sequences встановлені правильно після backfill

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT pg_get_serial_sequence('orders','id') AS seq; SELECT MAX(id) FROM orders;"
           seq
--------------------------
 public.orders_id_seq
(1 row)

  max
--------
  984211
(1 row)

Що це означає: Ви знайшли ім’я sequence і поточний максимальний id у таблиці.

Рішення: Якщо sequence відстає — виправте це зараз, до cutover.

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT setval('public.orders_id_seq', (SELECT MAX(id) FROM orders));"
 setval
--------
 984211
(1 row)

Завдання 10: Виміряти CDC lag (приклад з Debezium‑подібними офсетами)

cr0x@server:~$ jq -r '.source.ts_ms' /var/lib/cdc/offsets/appdb.json
1735559142000

Що це означає: Той таймстемп (мс з epoch) — час події джерела останньої обробленої зміни binlog.

Рішення: Порівняйте з поточним часом; якщо лаг зростає — не робіть cutover. Виправте пропускну спроможність спочатку (мережа, швидкість apply, індекси, розмір батчів).

Завдання 11: Підтвердити кількість рядків між системами (швидко, неточно, але корисно)

cr0x@server:~$ mysql -h mariadb01 -u admin -p -N -e "SELECT COUNT(*) FROM appdb.orders;"
Enter password:
984211

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -t -c "SELECT COUNT(*) FROM orders;"
 984211

Що це означає: Підрахунки збігаються для цієї таблиці. Це не доводить тотожності даних, але виявляє відсутні шматки.

Рішення: Якщо підрахунки різняться — зупиніться і дізнайтесь чому: фільтри в лоадері, невдалі рядки, CDC не застосовує deletes або колізії PK.

Завдання 12: Валідувати контрольні суми для вибіркового діапазону (краще, ніж підрахунки)

cr0x@server:~$ mysql -h mariadb01 -u admin -p -N -e "SELECT MD5(GROUP_CONCAT(CONCAT(id,':',status,':',total_cents) ORDER BY id SEPARATOR '|')) FROM appdb.orders WHERE id BETWEEN 900000 AND 900500;"
Enter password:
2f1c7c0a9b0e0a2f8dd7d8a11d5f4b3a

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -t -c "SELECT md5(string_agg(id||':'||status||':'||total_cents, '|' ORDER BY id)) FROM orders WHERE id BETWEEN 900000 AND 900500;"
 2f1c7c0a9b0e0a2f8dd7d8a11d5f4b3a

Що це означає: Для цього діапазону вміст збігається. Цей метод чутливий до форматування/кастингу; це плюс, якщо ви хвилюєтесь щодо неявних конверсій.

Рішення: Запустіть це для кількох діапазонів і таблиць з високою активністю. Якщо є невідповідність — розбирайте трансформації, округлення, конверсії часових зон та нормалізацію тексту.

Завдання 13: Виявити повільні запити в PostgreSQL одразу після shadow reads

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT calls, mean_exec_time, rows, query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;"
 calls | mean_exec_time | rows |                     query
-------+----------------+------+------------------------------------------------
  1200 |         87.332 |    1 | SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2
   310 |         54.910 |   25 | SELECT * FROM products WHERE name ILIKE $1
    98 |         42.774 |    1 | UPDATE inventory SET qty = qty - $1 WHERE sku = $2

Що це означає: Ви дивитесь на патерни запитів, а не на окремі інциденти. Середній час виконання — ваш індикатор «що погіршилось?».

Рішення: Додайте індекси, перепишіть запити або змініть стратегію пагінації до cutover. Не «чекайте й подивимось» при клієнтоорієнтованій затримці.

Завдання 14: Explain найгіршого винуватця й вирішити: індекс або зміна запиту

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;"
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..152.12 rows=20 width=256) (actual time=24.112..24.145 rows=20 loops=1)
   Buffers: shared hit=12040 read=210
   ->  Index Scan Backward using orders_created_at_idx on orders  (cost=0.43..81234.55 rows=10680 width=256) (actual time=24.110..24.140 rows=20 loops=1)
         Filter: (user_id = 123)
         Rows Removed by Filter: 420000
         Buffers: shared hit=12040 read=210
 Planning Time: 0.231 ms
 Execution Time: 24.201 ms
(9 rows)

Що це означає: Він сканує за created_at і фільтрує по user_id. Це дорого: багато рядків видалено фільтром.

Рішення: Створіть комбінований індекс, який відповідає предикату й порядку, наприклад (user_id, created_at DESC), або перепишіть запит, щоб використовувати seek‑методи, якщо можливо.

Завдання 15: Перевірити здоров’я vacuum і кількість мертвих рядків (щоб уникнути «вчора було швидко»)

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
   relname   | n_dead_tup |        last_autovacuum        |       last_autoanalyze
-------------+------------+-------------------------------+-------------------------------
 audit_log   |    1983321 | 2025-12-30 09:11:02+00        | 2025-12-30 09:12:40+00
 sessions    |     822110 | 2025-12-30 09:08:14+00        | 2025-12-30 09:10:05+00
 orders      |     110422 | 2025-12-30 09:00:01+00        | 2025-12-30 09:03:33+00
 products    |       9221 | 2025-12-30 08:55:40+00        | 2025-12-30 08:56:10+00
 inventory   |       1087 | 2025-12-30 08:54:11+00        | 2025-12-30 08:54:55+00

Що це означає: Велика кількість dead tuples означає високу зміну; vacuum працює, але може не встигати, особливо якщо транзакції довготривалі.

Рішення: Налаштуйте autovacuum для гарячих таблиць, скоротіть довгі транзакції і стежте за bloat, поки він не перетворився на потік інцидентів.

Завдання 16: Підтвердити поведінку з’єднань і пулера перед cutover

cr0x@server:~$ psql -h pg01 -U postgres -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count(*) DESC;"
  state  | count
---------+-------
 idle    |   180
 active  |    35
(2 rows)

Що це означає: Кількість з’єднань і їх стани скажуть, чи потрібен пулер. PostgreSQL не любить 2000 idle з’єднань від додатку, що тримають пам’ять.

Рішення: Якщо бачите шторм з’єднань або багато idle — розгорніть pooler або виправте пулінг додатку перед днем, коли вам потрібний спокій.

Жарт №2: Cutover бази даних схожий на стрибок з парашутом — відкриття того, що ви неправильно його складаєте, запам’ятовується надовго.

Три міні‑історії з корпоративного життя (болючий досвід)

Міні‑історія №1: Інцидент через помилкове припущення

Компанія: середній SaaS, кілька регіонів, MariaDB primary з репліками. Вони хотіли PostgreSQL для кращих можливостей запитів та екосистеми розширень. Команда міграції зробила важливі речі правильно: backfill, CDC, shadow reads. Все виглядало зеленим. Вікно для cutover забукували.

Мале неправильне припущення було в тому, що «сортування рядків майже однакове». Додаток використовував курсорну пагінацію для списку користувачів, сортування по стовпцю name і WHERE name > last_seen_name. У MariaDB з обраною колацією порядок і правила порівняння відповідали UI. У PostgreSQL колація та локалізовані правила дали інший порядок для акцентованих символів і деяких змішаних регістрів.

Після cutover симптом був тонким: клієнти скаржилися на «відсутніх користувачів» у списках. Дані не зникли. Пагінація пропускала записи, бо логіка курсора більше не відповідала порядку сортування. Support отримав скріншоти; інженери прибули з недовірою. Це зазвичай в такому порядку відбувається.

Виправлення не було героїчним. Вони зробили сортування детермінованим, додавши вторинний ключ сортування (стабільний унікальний id), змінили курсор так, щоб він містив обидва поля, і звели колації до узгодженого стану там, де це можливо. Також додали регресійний тест, який генерує випадкові Unicode‑імена й перевіряє пагінацію між рушіями. Було нудно, але працювало.

Міні‑історія №2: Оптимізація, що відгукнулась бумерангом

Компанія: e‑commerce платформа з великими таблицями й вибухоподібним трафіком. У них був backfill‑пайплайн, що завантажував в PostgreSQL. Хтось помітив, що інгест повільніший за очікуване й вирішив пришвидшити, відключивши індекси й обмеження під час завантаження, а потім відтворити все в кінці. Це не автоматично неправильно.

Бумеранг вдарив з двох боків. По‑перше, CDC‑пайплайн уже застосовував зміни. Без обмежень дублі й сирі рядки просочувалися під час завантаження, бо логіка аплайу покладалася на базу для забезпечення унікальності й FK. По‑друге, після відтворення індексів їхнє створення наситило I/O і CPU, й аплаєр CDC опинився позаду, що збільшило лаг. Збільшений лаг підвищив ризик cutover. Ніхто не спав добре.

Вони стабілізувалися, змінивши послідовність: завантажувати шматками з базовими необхідними обмеженнями (PK, не всі FK), тримати продуктивність apply передбачуваною і планувати побудову індексів CREATE INDEX CONCURRENTLY у контрольованих пачках. Також додали dead‑letter обробку в CDC‑пайплайні для рядків, що порушують обмеження, щоб одна погана подія не блокувала все.

Мораль: оптимізація — це не «зробити числа більшими». Це «зменшити режими відмови».

Міні‑історія №3: Нудна, але правильна практика, що врятувала день

Компанія: enterprise B2B, багато інтеграцій і захоплення аудит‑трейлами. Команда зробила нудну річ: прогнала весь міграційний пайплайн у staging, імітуючи prod‑дані й трафік. Не юніт‑тести — генеральна репетиція.

Під час репетиції вони виявили, що одна таблиця мала стовпець TEXT зі збереженими JSON‑блобами з несумісним кодуванням. MariaDB це приймала. Postgres відкидав деякі рядки при приведенні до jsonb. Оскільки це було в репетиції, в них було час реалізувати трансформацію: зберігати сирий текст у колонці json_raw, заповнити перевірений jsonb там, де можливо, і додати асинхронний job для нормалізації старих рядків.

Вони також практикували відкат. Не просто «ми можемо відкотитися», а реально перемикали додаток назад на MariaDB у staging з кешованими з’єднаннями, пулерами й TTL DNS. Вони заміряли, скільки часу потрібно кожному сервісу для повторного підключення. Це число стало вимогою для runbook‑а cutover у проді.

На реальному cutover день зміна ACL мережі сповільнила трафік до Postgres з одного підмережі додатку. Оскільки вони репетирували, перші кроки runbook‑а швидко виявили проблему, а готовність до відкату знизила нервозність. Виправили ACL і продовжили. Ніхто не писав постмортем «ми припускали, що мережа в порядку». Нудність врятувала день.

Плейбук швидкої діагностики

Це для моменту одразу після того, як ви почали shadow reads, наростили трафік або зробили cutover — і латентність зросла. Не сперечайтесь з графіками. Тріаж.

Перше: це база даних, мережа чи додаток?

1. Перевірте помилки й таймаути на стороні додатку: чи не насичуємо пулли з’єднань або не отримуємо нових помилок через суворіший SQL?
2. Перевірте RTT і втрати пакетів між додатком і Postgres (особливо якщо Postgres у іншому підмережеві/регіоні).
3. Перевірте стани з’єднань в Postgres: active vs idle, waiting vs running.

Друге: знайдіть головний wait, а не головний запит

1. Шукайте блокування: чи транзакції чекають на блоки через довгі задачі або DDL?
2. Шукайте I/O‑тиск: чи читання йдуть з диска, бо індекси не використовуються або кеші не нагріті?
3. Шукайте CPU‑тиск: чи відсутній індекс перетворює прості фільтри в повні скани?

Третє: переконайтесь, що Postgres не б’ється сам із собою

1. Насичення autovacuum: забагато dead tuples або blocked vacuum через довгі транзакції.
2. Шторми чекпоінтів: налаштування WAL/checkpoint спричиняє періодичні латентні спалахи.
3. Невідповідний work_mem: сорти й хеші вистривають на диск.

Практичні команди «швидкої діагностики»

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE wait_event IS NOT NULL GROUP BY 1,2 ORDER BY count(*) DESC;"
 wait_event_type |     wait_event     | count
-----------------+--------------------+-------
 Lock            | transactionid       |    12
 IO              | DataFileRead        |     9
 LWLock          | WALWrite            |     4
(3 rows)

Що це означає: Ви бачите, на що сесії чекають. Блокування й I/O‑чекання поширені під час cutover, якщо індекси неправильні або транзакції довгі.

Рішення: Якщо домінують Lock waits — знайдіть блокувальників і змініть поведінку транзакцій. Якщо I/O waits — додавайте індекси або коригуйте запити; не «налаштовуйте Postgres» до того, як виправите шляхи доступу.

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, state, wait_event, left(query,120) AS query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC LIMIT 5;"
 pid  | xact_age | state  | wait_event |                         query
------+----------+--------+------------+---------------------------------------------------------
 4412 | 00:34:51 | active |            | UPDATE inventory SET qty = qty - $1 WHERE sku = $2
 3201 | 00:12:03 | idle in transaction | ClientRead | SELECT * FROM orders WHERE id = $1
(2 rows)

Що це означає: «idle in transaction» — тихий вбивця vacuum і каталізатор блокувань.

Рішення: Скоротіть область транзакцій в додатку; якщо потрібно, вбийте патологічні сесії. Postgres не врятує вас від повільних транзакцій.

Типові помилки: симптом → корінна причина → виправлення

1) «Postgres повільніший» одразу після cutover

Симптом: латентність зростає, CPU піднімається, і ви бачите sequential scans на гарячих шляхах.

Корінна причина: Відсутні комбіновані індекси й різні припущення оптимізатора. MariaDB часто «відпускала» іншу поведінку індексів, і ваші запити могли на це покладатися.

Виправлення: Використовуйте pg_stat_statements + EXPLAIN (ANALYZE, BUFFERS). Додайте індекси, що відповідають фільтрам + сортуванню. Перевіряйте на реальних параметрах, а не на тестових прикладах.

2) Колізії первинних ключів після ввімкнення записів у Postgres

Симптом: Вставки падають з duplicate key на таблицях, що використовували AUTO_INCREMENT.

Корінна причина: Sequences не просунуті до максимального існуючого id після backfill/CDC.

Виправлення: Виконайте setval() для кожної sequence принаймні до поточного max id, потім перевірте. Автоматизуйте це в runbook‑і cutover.

3) CDC наздоганяє вічно й ніколи не встигає

Симптом: Лаг зростає під час пікового трафіку; аплай не справляється.

Корінна причина: Аплай виконує важке індексне обслуговування під час backfill, недостатньо батчів або обмеження мережі/дисків.

Виправлення: Робіть backfill шматками, створюйте лише необхідні індекси на початку, решту — пізніше. Налаштуйте розмір батчів CDC. Переконайтесь, що Postgres має достатньо IOPS, і WAL‑параметри не стримують інжест.

4) «У staging працювало», але в production лоадер відкидає рядки

Симптом: Лоадер відкидає рядки — часто навколо дат, точності чисел або недійсного UTF-8.

Корінна причина: В production є історичне сміття: нульові дати, вихід за діапазон інтов, недійсні байти.

Виправлення: Запустіть запити якості даних у MariaDB раніше. Визначте правила трансформації явно й реалізуйте їх однаково в backfill і CDC.

5) Дублікати/пропуски в пагінації

Симптом: Користувачі бачать один і той же елемент двічі на сторінках або елементи зникають.

Корінна причина: Нестабільний порядок сортування, відмінності колацій або сортування по неунікальних колонках.

Виправлення: Додайте стабільний tie‑breaker (унікальний id) до ORDER BY і курсор‑токену. Уникайте offset‑пагінації для списків з великою активністю.

6) Раптові deadlocks у Postgres

Симптом: Помилки про deadlock на таблицях з інтенсивними оновленнями; кількість повторних спроб зростає.

Корінна причина: Різний порядок захоплення блокувань у запитах і нові індекси, що змінюють плани виконання.

Виправлення: Стандартизувати порядок захоплення блокувань у логіці додатку. Тримати транзакції малими. Додати повтори з jitter. Аналізувати deadlock‑графи в логах сервера.

7) Диск росте швидше, ніж очікували

Симптом: Використання сховища щоденно зростає; продуктивність погіршується з часом.

Корінна причина: Autovacuum не встигає, довгі транзакції перешкоджають прибиранню або занадто низькі пороги autovacuum для гарячих таблиць.

Виправлення: Налаштуйте autovacuum по таблицях; усуньте idle‑in‑transaction; розгляньте партиціюцію для журналів з додаванням; моніторьте bloat і лаг вакуума.

Чеклісти / покроковий план

Фаза 0: Визначте незаперечні речі

• Визначте прийнятну паузу для записів (якщо така є) в секундах.
• Визначте вікно відкату (як довго MariaDB залишається готовою).
• Заморозьте політику DDL під час фінальної синхронізації.
• Виберіть стратегію міграції: CDC‑перший (рекомендовано) або dual‑write (тільки якщо дуже треба).

Фаза 1: Передпольотна інвентаризація (де народжуються сюрпризи)

• Перелічіть таблиці, розміри й швидкість зміни; відмітьте «гарячі» таблиці.
• Перелічіть типи даних, що погано мапляться: JSON як текст, нульові дати, unsigned int, enum, колації.
• Перелічіть критичні запити та їх SLO по латентності.
• Визначте бекґраундні джоби, що виконують довгі транзакції.

Фаза 2: Розгорніть PostgreSQL так, як треба

• Виберіть версію Postgres і тримайте її однаковою в усіх середовищах.
• Увімкніть pg_stat_statements і базову видимість/моніторинг.
• Налаштуйте ролі, пайплайн міграцій і управління схемами.
• Визначте стратегію партиціювання для великих append‑only таблиць (audit logs, events).

Фаза 3: Backfill безпечно

1. Робіть backfill шматками по PK‑діапазонах або часових вікнах.
2. Тримайте PK і необхідні індекси доступними для CDC‑apply.
3. Трекінгуйте відхилені рядки й виправляйте upstream або трансформаційні правила.
4. Записуйте контрольні точки backfill, щоб повторні запуски були детерміновані.

Фаза 4: Вікно синхронізації CDC

• Запустіть CDC з відомої позиції binlog/GTID.
• Постійно моніторьте лаг і помилки аплайу.
• Зупиніть дрейф схеми: ніяких некординированих DDL.
• Запускайте щоденні перевірки: підрахунки + вибіркові контрольні суми + бізнес‑інваріанти.

Фаза 5: Shadow reads і налаштування продуктивності

• Маршрутизуйте невеликий відсоток read‑трафіку на Postgres і порівнюйте результати.
• Виправляйте SQL‑несумісності й регресії продуктивності зараз, а не під час cutover.
• Перевіряйте функції, чутливі до колації (пошук, сортування, пагінація).
• Підігрійте кеші й перевірте розміри пулінгу з’єднань.

Фаза 6: Runbook cutover (послідовність, яку можна виконати під стресом)

1. Оголосіть freeze змін і підтвердіть on‑call покриття.
2. Підтвердьте, що CDC lag низький і стабільний; визначте поточний лаг в секундах.
3. Увімкніть коротку паузу на записи (або жорстке обмеження швидкості) на рівні додатку.
4. Чекайте, доки CDC не дійде до нульового lag; підтвердіть останній GTID/позицію.
5. Запустіть фінальні перевірки цілісності на критичних таблицях.
6. Перемкніть записи на Postgres (feature flag / config / service discovery).
7. Переключіть читання на Postgres по всій інфраструктурі.
8. Моніторьте помилки, p95 латентність, блокування, реплікацію й сховище.
9. Тримайте MariaDB в режимі тільки для читання й доступною для відкату протягом погодженого вікна.

Фаза 7: Після‑cutover зміцнення

• Увімкніть суворіше оброблення помилок у додатку для помилок запитів; не ховайте виключення.
• Переглядайте pg_stat_statements щотижня впродовж першого місяця.
• Налаштовуйте autovacuum для гарячих таблиць.
• Виведіть CDC‑пайплайн лише після закінчення вікна відкату й валідації даних.

FAQ

1) Чи можна зробити міграцію MariaDB → PostgreSQL з буквальним нульовим простоєм?

Так, але «нульовий простій» зазвичай приховує проблему консистентності записів. Прагматичний підхід — коротка пауза на записи, щоб злити CDC‑lag до нуля. Якщо ви маєте уникати цього, знадобиться dual‑write плюс інструменти для примирення та идемпотентність — очікуйте складності й більше крайових випадків.

2) Що краще: CDC чи dual‑write?

Віддавайте перевагу CDC. Воно спрощує додаток і локалізує складність у пайплайні, який ви можете спостерігати й перезапустити. Dual‑write підходить, якщо ви вже маєте идемпотентні записи, сильні retry‑семантики й готовність створювати інструменти примирення.

3) Що найчастіше ламається на SQL‑рівні?

Неявні приведення, семантика GROUP BY, робота з датами/часовими зонами і чутливість ідентифікаторів до регістру. Суворість PostgreSQL — це перевага; вона виявляє помилки, які вже були.

4) Як обробляти UNSIGNED інти з MariaDB?

Вирішуйте по стовпцю: відобразіть у більший signed тип у Postgres (наприклад, unsigned int → bigint) або запровадьте обмеження й прийміть менший діапазон. Не мапте бездумно — переповнення діапазону проявиться пізніше як нелогічні баги.

5) А як щодо ENUM типів?

В Postgres можна використовувати рідні ENUM або lookup‑таблицю з foreign key. Натівні enum підходять, якщо значення стабільні; lookup‑таблиці кращі, якщо значення часто змінюються і потрібна аудит‑історія.

6) Як валідувати коректність даних без порівняння кожного рядка?

Комбінуйте техніки: підрахунки рядків для охоплення, вибіркові контрольні суми для вмісту й бізнес‑інваріанти (наприклад, суми замовлень відповідають сумам позицій). Також валідуйте «крайові» зрізи: найновіші, найстаріші і діапазони з високою зміною.

7) Чому диск Postgres ріс швидше, ніж очікували?

MVCC означає, що оновлення створюють dead tuples, поки vacuum їх не прибирає. Якщо у вас довгі транзакції або autovacuum не встигає — bloat росте. Виправляйте довжину транзакцій і налаштовуйте autovacuum; не лише кидайте більше диску.

8) Чи потрібен connection pooler для Postgres?

Часто — так. З’єднання PostgreSQL важчі, ніж багато команд очікує, і «одне з’єднання на потік» може стати дорогим. Якщо у вас багато інстансів додатку — пулінг зазвичай спокійний вибір.

9) Як планувати відкат?

Тримайте MariaDB доступною і, бажано, у режимі тільки для читання після cutover. Щоб чисто відкотити записи, потрібне або (a) відсутність записів у Postgres (рідкість), або (b) план зворотної синхронізації (складно). На практиці відкат — це швидке перемикання назад, поки ви розбираєтесь, а потім повторний cutover. Саме тому важливо мінімізувати час, коли системи розходяться.

10) Чи вистачить pgloader?

Це потужний інструмент для початкової схеми й backfill, але не повна історія zero‑downtime сам по собі. Розглядайте його як крок масового копіювання, далі використовуйте CDC і дисциплінований cutover.

Висновок: наступні кроки, які ви можете виконати

Якщо ви хочете міграцію без простоїв і без сюрпризів — зробіть нудну інженерію: вимірюйте все, обмежуйте зміни і репетируйте cutover. Системи, що виживають, — це ті, де ви в реальному часі можете відповісти: «наскільки ми відстаємо?» і «чи можемо відкотитися за п’ять хвилин?»

Наступні кроки, які ви можете зробити цього тижня:

• Інвентар MariaDB: формат binlog, GTID, колації, SQL mode і «брудні» дані (нулеві дати, недійсний UTF-8).
• Розгорнути Postgres з видимістю: pg_stat_statements, моніторинг з’єднань, видимість vacuum.
• Зробити одну таблицю енд‑ту‑енд: backfill, CDC, контрольні суми й перевірка продуктивності на shadow reads.
• Написати runbook для cutover і відрепетирувати його з відтворенням трафіку та тренуванням відкату.

Робіть міграцію так, як ви керуєте продакшном: контрольовані зміни, жорсткі докази й план, що не потребує оптимізму для роботи.