Сравнение схем двух или более различных баз данных Postgres является распространенной задачей, но она может стать более каверзной, если эти базы работают на разных версиях Postgres'а. Быстрым и каноническим способом сравнить схемы является использование одной и той же программы pg_dump для взаимодействия с каждой базой со --schema-only параметром. Этот метод работает великолепно, но имеются некоторые подводные камни, особенно при копировании представлений.


(Фото сделано 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 gregtest AS
-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

Вывод

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