MySQLからPostgreSQLに移行する際に知っておくべきことは何ですか？

データウェアハウスをPostgreSQLに移行する理論と実践に関する記事の続きで、一般的なMySQL DBMSから移行するときに発生する可能性のある問題について説明します。 不必要なレトリックでみんなを退屈させないために、今日の話はより多くの論文と問題指向です。



以下はすべて、回路の適応、コードの処理、およびデータの転送のプロセスに影響を与える可能性のある典型的なMySQLの設計および操作エラーのリストです。 さまざまな陰湿な組み合わせでこれらすべての小さなものが存在することが、既存の「ユニバーサル」ツールがあなたのベースに特に対処する可能性が低い理由の1つです。



そのため、 前の記事で、銀の弾丸を探す時間を無駄にせず、自分の「膝の上に」何かを書くことをお勧めしました。 この記事は、そのようなツールの作成を容易にし、潜在的な欠陥を指摘することを目的としています。欠陥の存在は比較的迅速に確認できます。



ビジネスに取り掛かろう。



デフォルト値



MySQLは、開発者の作業を単純化および容易化しようとすることで有名です。 原則として、操作の開始時にストリクトモードがオフになり、テーブルにデータを挿入するときに正しいデフォルト値がないことを責任を持って報告します。



言い換えると、DEFAULTパラメーターなしでテーブルで宣言された列がある場合があります。 テーブルに貼り付ける場合、この列を指定することを忘れますが、MySQLは混乱せず、「デフォルト」を挿入します。 原則として、これは0、空の文字列、または対応するデータ型の同様の「ゼロ」値です。 PostgreSQLは常に制約違反をスローするため、アプリケーションコード内の対応する状況を修正する準備をしてください。



また、不適切なデフォルト値をDATE / DATETIME / TIMESTAMPタイプのフィールドに挿入するというMySQLの愛情にも注目したいと思います。 このようなフィールドでの不注意な作業により、データベースに「0000-00-00」または「00:00:00 0000-00-00」などの行が存在する可能性があります。 もちろん、PostgreSQLはこれを見逃しません。 元のデータベースのデータを「修正」するか、インポート時に強制的にNULLに置き換えて、アプリケーションコードと対応するテーブルの構造を適切に修正する必要があります。



重大度の制約、厳密モード



デフォルト値と制限のトピックを続けて、MySQLはそれらに違反することにも非常に忠実です。 たとえば、タイプVARCHAR2（255）のフィールドを宣言しました。 MySQLには、指定された次元に収まらない場合、行が自動的に切り捨てられるという不健康な傾向があります。 PostgreSQLは誓います。



この問題のコンテキストでは、STRICTモードがオフの場合、指定されたエンコーディングMySQLの行の不正なバイトシーケンスがサイレントにカットすることにも注意する必要があります。 PostgreSQLは常に不正なバイトシーケンスを誓います。 PostgreSQL用のアプリケーションコードを準備するときは、入力をさらにサニタイズする準備をしてください。



まあ、事前にバックアップを作成して、MySQLデータベース全体が一貫して同じエンコーディングになっていることを確認してください。また、phpMyAdminを使用して、スウェーデン語ロケールなどでVARCHAR2フィールドを持つテーブルを作成したプログラマはいません。



トランザクションと自動コミット



プログラマーは通常、トランザクションを使用することを恐れています。または、彼の能力がないために、トランザクションを「重く」「遅い」と見なします。 実際、トランザクションなしでリクエストを実行することは不可能です。 したがって、このような専門家にとって、MySQLはAUTOCOMMITモードで有効に機能します。このモードでは、「内部」でのトランザクション処理の悪質なプラクティスがすべて隠されます。 PostgreSQLでは、特別なフラグ（SET AUTOCOMMIT）を含めることにより、データベース操作に対するこのアプローチをシミュレートする必要があります。



特に興味深いのは、トランザクションデータ処理をサポートしないトランザクション内でテーブルエンジンを使用するMySQLの機能です。 ここでは、ドキュメントのみを引用できます（「トランザクション内でトランザクションテーブルと非トランザクションテーブルを意図的に混在させていない場合、このメッセージの原因として最も考えられるのは、トランザクションであると思ったテーブルが実際ではないことです」）。



可能であれば、移行前にエンジンを体系化してください。 そうでない場合は、自動コミットを使用して移動するか、そのような状況で少なくとも名目上のトランザクション処理を提供してください。 それは間違いなく悪くなることはありません。



Memoryというテーブルエンジンには、独自の段落が必要です。 このエンジンの特徴は、データをディスクに保存せず、メモリに完全に保存することです。 メモリの量は固定されており、データベースサーバーを再起動することによってのみ変更できる設定によって設定されます。 このエンジンの動作に関連する2つのニュアンスがあります。



サイズの制限を超えるテーブルは即座にディスクに「スワップ」され、キャリッジはカボチャになり、場合によってはデータベースと一緒になります（パフォーマンスを最適化するためにメモリエンジンが使用された場合）。

原則として、このようなテーブルには一時的なデータが含まれますが、これは失うものではありません。 最初の損失の後、データはミッションクリティカルであり、失うことはできないことが判明しました。 そして、データベースとアプリケーションのアーキテクチャはすでにこれらのテーブルにしっかりと「植え付けられ」ており、その結果、誰もがそれらのテーブルで祈るようになり、データベースを定期的に再起動すると、テーブルをディスク永続形式に変換し、メモリと同様のシャーマニズムに変換するという特定の儀式に変わります。



一般に、PostgreSQL自体はメモリ管理の点で単純に適切であり、データベースに十分な量が割り当てられている場合、そのようなテーブルをRAMに保持し、アプリケーションからの要求に応じて行を提供することは素晴らしいことです。 それでも、テーブルのワークロードが非常に大きい場合、十分なメモリがない場合、または永続テーブルのアプリケーションを処理する時間がまったくない場合は、キーワードUNLOGGED（CREATE UNLOGGED TABLE ...）を使用してPostgreSQLで同様のテーブルを作成することで、メモリテーブルの動作をエミュレートできます。 ドキュメントの引用：

指定した場合、テーブルはログなしのテーブルとして作成されます。 ログされていないテーブルに書き込まれたデータは、先行書き込みログ（第29章を参照）に書き込まれないため、通常のテーブルよりも大幅に高速になります。 ただし、クラッシュセーフではありません。ログに記録されていないテーブルは、クラッシュまたはクリーンシャットダウンの後に自動的に切り捨てられます。 ログに記録されていないテーブルの内容もスタンバイサーバーに複製されません。 ログに記録されていないテーブルに作成されたインデックスも自動的にログに記録されません。

マッチの節約



多くのMySQLアーキテクトは、主キーの正しい配置や関係関係の正確性の維持など、非常に重要なことを忘れがちですが、1つまたは別の整数に追加された数字のビット深度と符号を非常に正確に示すことが彼らの義務であると考えています。 MySQLは非常に多様なデータ型（int、smallint、tinyintなど）を提供し、それぞれに対して最大文字数と負の値の有無を指定できます。 たとえば、TINYINTは-128〜127の値を保持し、UNSIGNED TINYINTは0〜255の値を保持します。



前述の建築家が決して思いつかないのは未来です。 遅かれ早かれ、指定されたフレームワークに収まらない値がそのようなフィールドに到着し、MySQLは最も近い有効な値に切り捨てられ、デフォルトでは厳密モードが無効になります。 そのような愚かさのために、客観的に重要で実際の数のパフォーマンスインジケーターが、許容される最大値の高い値まで「カットオフ」され、システム全体の効率が低下するデータベースを繰り返し観察しました。



知識のある人は、同様に、お金などの重要な何かを伝統的に保存する10進数/数値タイプでも同様の状況が発生することをすでに推測しています。 そのような場合、実質利益の損失についてはすでに話をすることができます。 PostgreSQLは、足で（または頭でさえ）自分自身を撃つ機会のそのような動物園から免れ、指定された「定数」を超えることを常に誓います。



IGNORE + ON DUPLICATE KEYを挿入...更新



MySQL開発者に最も愛されている構造のいくつかは、PostgreSQLに直接類似していません。



INSERT IGNOREの継続的な使用は、データベーススキーマの設計上の問題を強く示唆しています。 ただし、INSERTを実行し、UNIQUE EXCEPTIONが発生した場合にそれをインターセプトし、サイレントに「消す」ストアドプロシージャを記述することで、この動作をエミュレートできます。



ON DUPLICATE KEY ... UPDATE、またはいわゆるUPSERT（UPdate + inSERT）は、PostgreSQLコミュニティで必要な「機能」として認識されていました。 そのリリースは、DBMS 9.5バージョンに割り当てられています。 それまでは、次の構成でこれをエミュレートできます。

--   ,      UPDATE existing_table ex SET field1 = ex.field1 + up.field1, field2 = up.field2, … FROM updates up WHERE ex.pkey_field1 = up.pkey_field1 AND ex.pkey_field2 = up.pkey_field2 …; --   INSERT INTO existing_table (columns, …) SELECT new.field1, new.field2, … FROM new_data new WHERE NOT EXISTS ( SELECT * FROM existing_table ex WHERE ex.pkey1 = new.pkey1 and ex.pkey2 = new.pkey2 and …; );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQLで時間を操作する



この目的のためにMySQLで利用可能なデータのタイプの時間値を丸める問題についてはすでに述べました。 それとは別に、このような情報を保存するためのさまざまな可能なオプションを検討します。 これ自体はカテゴリー的な問題ではありません。すべての問題は、開発者が型の違いを知らないことに起因し、その結果、目に見えるロジックなしに一度​​に軽薄に使用されることに起因します。 DATETIME、TIMESTAMP、およびBIGINT（Unixタイムスタンプ値用）を使用して時刻を格納する状況は非常に規則的であり、ほぼ1秒おきに観察されます。



DATETIMEは、タイムゾーンオフセットを格納する点でTIMESTAMPと異なります。 これに基づいて、PostgreSQLの適切なデータ型（タイムゾーンのある/ないタイムスタンプ）を計画し、オンザフライでインポートするときにタイムゾーンの修正をインポート/減算する準備をします（データベースが特定の「タイムゾーン」にハードコーディングされている場合、およびデータタイプこれは無差別に使用されます）。



同様に重要なのは、bigintなどのフィールドにUNIXタイムスタンプを保存する問題です。 MySQLには、タイムゾーンオフセットに対応する秒数をこの値に追加するという不快な慣行がありますが、これはイデオロギー的にはUTCのままにする必要があります。 この動作は標準ではないため、バージョン9.2以降、PostgreSQLは不快な回避策を使用してEXTRACT SQLコンストラクト（EPOCH FROM TZVALUE :: TIMESTAMP WITH TIME ZONE）の形式でタイムゾーンのエポックを調整する必要があります。指定されたタイムスタンプからのエポック値。タイムゾーンストレージを使用する形式に明示的に変換します。



タイムゾーンの操作は、エラーの存在が許される操作の1つです。これは、タスクが本当に重要であるためです。 PostgreSQLでタイムゾーンを操作するための正しいアプローチの説明は、 www.depesz.com / 2014/04/04 / how-to-deal-with-timestampsにある素晴らしい記事に記載されています 。



ロック



多くの場合、プログラマはデータベースロックを手動で管理することを好みます。 共有ロックと排他ロックを明示的に設定するか、さらに悪いことに、デフォルトのトランザクション分離レベルを変更します。 厳密に言えば、これを行うことは厳密に禁忌です。 それにもかかわらず、実際には、アーキテクトが更新されたテーブルの「聴覚障害者」ロックによって操作の整合性を実装することを決定した状況がありました。 同時に、ユーザーインターフェイスからのOLTPロードが同じテーブルに送られることを心配する人はほとんどいませんでした。 多くの場合、このような衝動は、非トランザクションテーブルエンジンの存在またはデータベースを支配する設定に関する一般的な狂気のために、MySQLトランザクションが適用できないという事実から生じます。 たとえば、「タイムアウト」が制限まで緩められ、通常のトランザクションが適用されないという長いリクエストがあります。



移行中のこの問題に対する最善の解決策は、PostgreSQLで正直なトランザクションを使用してみることです。これは、同時実行性の高い状況に対処する可能性が高いです。 結局のところ、ロックがロジックの不可欠な部分である場合は、アドバイザリーロックメカニズムを検討してください。 いわゆるアドバイザリロックは仮想的なものであり、物理データのブロックにはつながりません。 トランザクション内でそれらを適切に使用すると、リスクが大幅に低下し、DBMSのリソースとパフォーマンスがより節約されます。 しかし、もちろん、誰もあなたの頭で考えて、アプリケーションコードでロックを適用するすべての「ケース」を注意深く監視する必要性をキャンセルしません。



おわりに



MySQLデータベースサーバーで発生する可能性のある驚くべきことのほんの一部のみを調べました。 MySQLの複雑なレプリケーションと、PGを使用して複製する方法について、非常に長い間議論することができます。たとえば、次のPG Day'15 RussiaでOleg Tsarevが行います。 最良の場合、問題はホットスタンバイメカニズムを使用して解決されます。 最悪の場合、サポートと操作においてSlonyのような重要なツールを習得する必要があります！



これらの例が、移行のタスクが既存のツールを使用した複雑で実質的に解決不可能なプロセスである理由を説明してくれることを願っています。 思慮深い、手動のアプローチのみで動作することが保証され、古い軍事の知恵は「あなたのデータを知っています」。