[Перевод] Сравнение схем Postgres и сопутствующие проблемы с представлениями
Сравнение схем двух или более различных баз данных Postgres является распространенной задачей, но она может стать более каверзной, если эти базы работают на разных версиях Postgres’а. Быстрым и каноническим способом сравнить схемы является использование одной и той же программы pg_dump для взаимодействия с каждой базой со --schema-only параметром. Этот метод работает великолепно, но имеются некоторые подводные камни, особенно при копировании представлений.
Начнем с некоторых предпосылок, с того как была обнаружена эта проблема. У нас имеется экземпляр, который пребывает в процессе обновления версии 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.
Единственное отличие кроме версии сервера — это представление, которое не соответствует вообще, чем и обеспокоена утилита diff. (Для целей этой статьи, из вывода убраны все второстепенные строки).
В Postgres версии 9.2, версия pg_get_viewdef (text, boolean) будет включать и выключать отступы, более того, можно увидеть что по умолчанию отступы не добавляются:
В Postgres версии 9.6, однако, вы всегда сталкиваетесь с «красивым» отображением, независимо от того, какую из пяти версий функции вы выберете и какие аргументы вы им передадите! Вот вызов той же функции, что и в примере выше на версии 9.6:
Когда я впервые столкнулся с этой проблемой, три решения всплыли в моем уме:
Изначально я посчитал что быстрый скрипт на Perl будет самым легким путем. И к тому моменту, как я получил один рабочий вариант скрипта, доставило много боли превратить вывод из «красивого» в «некрасивый», особенно пробелы и использование скобок. Подход грубой силы, путем простого удаления всех круглых и квадратных скобок, лишних пробелов из правил и определений представлений почти сработал, но полученный вывод был довольно уродливтрудно читаем, кроме того, оставались проблемы с лишними пробелами.
Хотя это решение и устранило проблему с отступами и пробелами, скобки все-равно еще отличаются и это не так легко решить. В целом, не лучшее решение.
Эта измененная версия воспроизведет такую же схему в нашей тестовой базе данных:
Лучшее решение, по мнению моего коллеги Дэвида Кристенсена, просто сделать так, чтобы Postgres делал сам все тяжелые операции с помощью волшебства импорта/экспорта. К концу дня, вывод pg_dump’а не только человекочитаем, но и разработан таким образом, чтобы его мог распознавать Postgres. Таким образом, мы можем скормить старую схему версии 9.2 временной базе версии 9.6, затем развернуться и скопировать ее. В итоге мы имеем идентичные вызовы pg_get_viewdef () для обеих схем. Вот он на наших тестовых базах:
Попытки сравнить схемы разных версий могут быть довольно трудными, так что лучше даже не пытаться. Копирование и восстановление схем — дешевая операция, так что просто скопируйте обе схемы на один сервер, после чего производите сравнение.
(Фото сделано 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, каждая из которых принимает различные аргументы:
- имя представления
- имя представления и логический аргумент
- OID
- OID и логический аргумент
- 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;
Решения
Когда я впервые столкнулся с этой проблемой, три решения всплыли в моем уме:
- Написать скрипт, который будет трансформировать и нормализовывать вывод схемы
- Изменить исходный код Postgres’а для изменения поведения pg_get_viewdef
- Получить вызов 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
Вывод
Попытки сравнить схемы разных версий могут быть довольно трудными, так что лучше даже не пытаться. Копирование и восстановление схем — дешевая операция, так что просто скопируйте обе схемы на один сервер, после чего производите сравнение.
