Clever Geek Handbook

Postgresスキーマ比較と関連ビューの問題

2つ以上の異なるPostgresデータベースのスキーマを比較することは一般的なタスクですが、これらのデータベースが異なるバージョンのPostgresで実行されている場合は、さらに注意が必要になります。 スキームを比較するための迅速で標準的な方法は、同じpg_dumpプログラムを使用して、 -schema-onlyパラメーターで各データベースと対話することです。 この方法はうまく機能しますが 、特にビューをコピーする場合、いくつかの落とし穴があります 。



画像

（ Philippe Vieux-Jeantonが撮影した写真 ）



前提



この問題がどのように発見されたかについて、いくつかの仮定から始めましょう。 Postgresのバージョンを9.2から9.6（執筆時点の最新バージョン）に更新中のコピーがあります。 データのチェックサムを含めるだけでなく、エンコーディングをUTF-8に変更することも計画されていたため、 pg_upgradeを使用することはできませんでした。 多くの要因、特にエンコーディングの変更により、一般的な更新プロセスは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ユーティリティが関係します。 （この記事の目的上、すべての2次行は出力から削除されます）。



前述したように、犯人はpg_get_viewdef（）関数です。 彼の仕事は、プレゼンテーションの内容を適切で読みやすい方法で提示することです。 彼女がこの結論で行った主な変更は2つあります。ブラケットの追加とスペースを含むインデントの追加です。 最近のバージョンでは、ドキュメントがほのめかしているという事実にもかかわらず、インデント（美しい出力）をオフにすることはできません。つまり、バージョン9.2のサーバーがデフォルトで行うように、バージョン9.6のサーバーにビューの違いを1行で表示する簡単な方法はありません。 さらに、pg_get_viewdef関数には5つのバージョンがあり、それぞれ異なる引数を取ります。



  1. ビュー名
  2. ビュー名とブール引数
  3. オイド
  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では、5つのバージョンの関数のどれを選択し、どの引数を渡すかに関係なく、常に「きれいな」表示に遭遇します。 バージョン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;


解決策



この問題に初めて遭遇したとき、3つの解決策が思い浮かびました。



  1. 回路の出力を変換して正規化するスクリプトを作成します
  2. Postgresのソースコードを変更してpg_get_viewdefの動作を変更します
  3. 同一の出力を取得するような方法でpg_dumpのpg_get_viewdef関数の呼び出しを取得します


最初は、簡単なPerlスクリプトが最も簡単な方法だと思いました。 そして、スクリプトの1つの作業バージョンを取得するまでに、出力を「美しい」から「ugい」、特にスペースとブラケットの使用に変更するのは大変な苦労でした。 単純にすべての括弧と角かっこ、余分なスペースをルールと表現の定義から削除するだけのブルートフォースアプローチはほとんど機能しましたが、結論は非常に読みにくく、余分なスペースにはまだ問題がありました。



2番目のアプローチ、Postgresソースコードの変更は、実際には非常に簡単です。 ある時点で、ギャップの挿入が強制的に「オン」状態になるようにソースコードが変更されました。 src / backend / utils / adt / ruleutils.cの単一の文字を変更することですべてが解決しました。 



 - #define PRETTYFLAG_INDENT 2 + #define PRETTYFLAG_INDENT 0


このソリューションはインデントと空白の問題を修正しましたが、括弧はまだ異なっており、これを解決するのはそれほど簡単ではありません。 一般的に、最善の解決策ではありません。



3番目のアプローチは、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にすべての重い作業を行わせることです。 1日の終わりには、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


おわりに



異なるバージョンのスキーマを比較しようとするのは非常に困難な場合があるため、試さないことをお勧めします。 スキームのコピーと復元は安価な操作なので、両方のスキームを1つのサーバーにコピーしてから比較します。


More articles:


All Articles