Порівняння схем Postgres і супутні проблеми з уявленнями

Порівняння схем двох або більше різних баз даних Postgres є поширеною завданням, але вона може стати більш каверзной, якщо ці бази працюють на різних версіях Postgres'а. Швидким і канонічним способом порівняти схеми є використання однієї і тієї ж програми pg_dump для взаємодії з кожною базою --schema-only параметром. Цей метод працює чудово, але є деякі підводні камені, особливо при копіюванні подань.

image
Фото зроблено Philippe Vieux-Jeanton

Передумова
Почнемо з деяких передумов, з того, як була виявлена ця проблема. У нас є примірник, який перебуває в процесі оновлення версії Postgres з 9.2 до 9.6 (остання версія на момент написання статті). Використання pg_upgrade було неможливо, так як планувалося не тільки включення контрольних сум даних, але і зміна кодування на UTF-8. Ряд чинників, особливо зміна кодування, що означав типових процес оновлення pg_dump old_database | psql new_database не можливий. Таким чином, ми маємо дуже специфічну програму, яка акуратно мігрує частини даних, виробляючи над ними дії з шляху.

Проблема
В якості остаточної оцінки осудності, ми хотіли переконатися в тому, що остаточна схема до оновленої версії 9.6 бази даних наскільки можливо ідентична нинішньою схемою продуктової бази даних версії 9.2. При порівнянні вихідних даних pg_dump, ми швидко виявили проблему шляхи відображення уявлень. Версія 9.2 використовує дуже убогий, однорядковий висновок, в той час як версія 9.6 використовує многострочную «красиво виведену» варіацію. Само собою зрозуміло, це означало, що ні одне з подань не збігалося при порівнянні вихідних даних pg_dump.

Проблема криється в системній функції pg_get_viewdef(), яка використовується pg_dump'ом для повернення людино-читається і Postgres-розпізнаваної версії подання. Для демонстрації проблеми і рішення, створимо простий подання на обох базах, після чого порівняємо їх допомогою pg_dump:

$ psql -p 5920 vtest -c \
'create view gregtest as select count(*) from pg_class where reltuples = 0'
CREATE VIEW
$ psql -p 5960 vtest -c \
'create view gregtest as select count(*) from pg_class where reltuples = 0'
CREATE VIEW
$ diff -u <(pg_dump vtest -x -p 5920 --schema-only) <(pg_dump vtest -x -p 5960 --schema-only)

--- /dev/fd/70 2016-09-29 12:34:56.019700912 -0400
+++ /dev/fd/72 2016-09-29 12:34:56.019720902 -0400
@@ -2,7 +2,7 @@
-- PostgreSQL database dump
--

--- Dumped from database version 9.2.18
+-- Dumped from database version 9.6.0
-- Dumped by pg_dump version 9.6.0

SET statement_timeout = 0;
@@ -35,22 +35,14 @@
--

CREATE VIEW AS gregtest
-SELECT count(*) AS count FROM pg_class WHERE (pg_class.reltuples = (0)::double precision);
+ SELECT count(*) AS count
+ FROM pg_class
+ WHERE (pg_class.reltuples = (0)::double precision);

Єдина відмінність крім версії сервера — це уявлення, яке не відповідає взагалі, чим і стурбована утиліта diff. (Для цілей цієї статті, на підставі висновку прибрані всі другорядні рядка).

Як говорилося раніше, винуватцем є функція pg_get_viewdef(). Його робота полягає в тому, щоб представити начинку подання в адекватному, читаному вигляді. Є два основні зміни, які вона робить з цим висновком: додавання дужок і додавання відступів за допомогою пробілів. В останніх версіях, незважаючи на те, що документи натякають, відступи (гарний висновок) не можуть бути відключені, а значить немає легкого способу примусити сервер з версії 9.6 віддавати різницю в уявленнях одним рядком, як робить сервер з версією 9.2 за замовчуванням. Більше того, є п'ять версій функції pg_get_viewdef, кожна з яких приймає різні аргументи:

  1. ім'я подання
  2. ім'я подання та логічний аргумент
  3. OID
  4. OID і логічний аргумент
  5. OID і цілочисельний аргумент
У Postgres версії 9.2, версія pg_get_viewdef(text,boolean) буде включати і вимикати відступи, більше того, можна побачити що за замовчуванням відступи не додаються:

$ psql vtest -p 5920 -Atc "select pg_get_viewdef('gregtest')"
SELECT count(*) AS count FROM pg_class WHERE (pg_class.reltuples = (0)::double precision);

$ psql vtest -p 5920 -Atc "select pg_get_viewdef('gregtest', 'false')"
SELECT count(*) AS count FROM pg_class WHERE (pg_class.reltuples = (0)::double precision);

$ psql vtest -p 5920 -Atc "select pg_get_viewdef('gregtest',true)"
SELECT count(*) AS count +
FROM pg_class +
WHERE pg_class.reltuples = 0::double precision;

У Postgres версії 9.6, однак, ви завжди стикаєтеся з «гарним» відображенням, незалежно від того, яку з п'яти версій функції ви виберете і які аргументи ви їм передасте! Ось виклик тієї ж функції, що і в прикладі вище на версії 9.6:

$ psql vtest -p 5960 -Atc "select pg_get_viewdef('gregtest')"
SELECT count(*) AS count
FROM pg_class
WHERE (pg_class.reltuples = (0)::double precision);

$ psql vtest -p 5960 -Atc "select pg_get_viewdef('gregtest', 'false')"
SELECT count(*) AS count
FROM pg_class
WHERE (pg_class.reltuples = (0)::double precision);

$ psql vtest -p 5960 -Atc "select pg_get_viewdef('gregtest',true)"
SELECT count(*) AS count
FROM pg_class
WHERE pg_class.reltuples = 0::double precision;

Рішення
Коли я вперше зіткнувся з цією проблемою, три рішення спливли в моєму розумі:

  1. Написати скрипт, який буде трансформувати і нормалізувати висновок схеми
  2. Змінити вихідний код Postgres'а для зміни поведінки pg_get_viewdef
  3. Отримати виклик pg_dump'ом функції pg_get_viewdef таким чином, щоб отримати ідентичний висновок
Спочатку я вважав що швидкий скрипт на Perl буде найлегшим шляхом. І до того моменту, як я отримав один робочий варіант скрипта, принесло багато болю перетворити виведення з «красивого» в «некрасивий», особливо прогалини і використання дужок. Підхід грубої сили, шляхом простого видалення всіх круглих і квадратних дужок, зайвих пробілів з правил і визначень уявлень майже спрацював, але отриманий висновок був досить потворнийважко читаємо, крім того, залишалися проблеми з зайві пробіли.

Підхід номер два, зміна вихідного коду Postgres'а, насправді досить простий. У якийсь момент вихідний код був змінений таким чином, що вставка пропусків була вимушено приведена в стан «включено». Зміна єдиного символу у файлі src/backend/utils/adt/ruleutils.c все вирішило:

- #define PRETTYFLAG_INDENT 2
+ #define PRETTYFLAG_INDENT 0

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

Третій підхід полягав у зміні вихідного коду pg_dump'а. Зокрема, він використовує pg_get_viewdef(oid) формат функції. Шляхом зміни даного формату pg_get_viewdef(oid,integer) формат функції та подачею на вхід аргументу 0, і версія 9.2, і версія 9.5 виводять одне і те ж:

$ psql vtest -p 5920 -tc "select pg_get_viewdef('gregtest'::regclass, 0)"
SELECT count(*) AS count +
FROM pg_class +
WHERE pg_class.reltuples > 0::double precision;

$ psql vtest -p 5960 -tc "select pg_get_viewdef('gregtest'::regclass, 0)"
SELECT count(*) AS count +
FROM pg_class +
WHERE pg_class.reltuples > 0::double precision;

Ця змінена версія прочитає таку ж схему в нашій тестовій базі даних:

$ diff -u <(pg_dump vtest -x -p 5920 --schema-only) <(pg_dump vtest -x -p 5960 --schema-only)

--- /dev/fd/80 2016-09-29 12:34:56.019801980 -0400
+++ /dev/fd/88 2016-09-29 12:34:56.019881988 -0400
@@ -2,7 +2,7 @@
-- PostgreSQL database dump
--

--- Dumped from database version 9.2.18
+-- Dumped from database version 9.6.0
-- Dumped by pg_dump version 9.6.0

SET statement_timeout = 0;

Найкраще рішення, на думку мого колеги Девіда Крістенсена, просто зробити так, щоб Postgres робив сам все важкі операції за допомогою чарівництва імпорту/експорту. До кінця дня, висновок pg_dump'а не тільки человекочитаем, але і розроблений таким чином, щоб його міг розпізнавати Postgres. Таким чином, ми можемо згодувати стару схему версії 9.2 тимчасової базі версії 9.6, потім розвернутися і скопіювати її. У результаті ми маємо ідентичні виклики pg_get_viewdef() для обох схем. Ось він на наших тестових базах:

$ createdb -p 5960 vtest92

$ pg_dump vtest -p 5920 | psql -q -p 5960 vtest92

$ diff -s -u <(pg_dump vtest92 -x -p 5960 --schema-only) <(pg_dump vtest -x -p 5960 --schema-only)
Files /dev/fd/63 and /dev/fd/62 are identical

Висновок
Спроби порівняти схеми різних версій можуть бути досить важкими, так що краще навіть не намагатися. Копіювання і відновлення схем — дешева операція, так що просто скопіюйте обидві схеми на один сервер, після чого робите порівняння.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.