いくつかのレコードをスキップしてWALバックアップからPostgreSQLデータベースを復元する

入門

PostgreSQLにはこのような興味深い技術的解決策があります-データベース自体のファイル内の何かを実際に変更する前に、DBMSはすでに内部形式に変換されたコマンドを特別なジャーナルに書き込みます-ログを先書きし、トランザクションが正常に完了した後、このログにメモします。 これは障害から回復するために行われましたが、最終的に、開発者の探究心は、このジャーナルをバックアップとレプリケーションに使用するというアイデアに思いつきました。 原則として、すべての動きがそこに記録されることは論理的です、さらに、バックアップからデータを復元するだけでなく、特定の時点でデータベースの状態を復元し、適切なタイミングでWALログエントリの再生を中断することもできます。



しかし、そのようなシナリオを見てみましょう-月曜日に基本的なバックアップを作成してWALログのアーカイブを開始し、水曜日に誤ったマスクで削除リクエストを実行し、マネージャーが必要なレコードの消失を金曜日に発表したとしましょう。 この状況では、バックアップから水曜日までしか回復できず、木曜日と金曜日にマネージャーの作業がすべて失われました。



論理的な疑問が生じます。「間違った」リクエストを除外しながら、月曜日から金曜日までWALログを再生することは可能ですか？



通常の状況では、フォーラムの質問に限定されますが、2つのFreeBSDディストリビューション、異なるバージョンのPostgreSQLソースコードを含む10のtarball、10 GBの空き容量、gcc、2つの比較的アンロードされた週、テキーラ、ラム酒、ビールの箱、断片的な記憶がありましたC構文。ソリューションに必要なものではなかったが、ソースコードを調べたので、止めるのは難しい...



そのため、実験のために、FreeBSD 10とPostgreSQL 9.2.8をポートから取得しました。 対応するバージョンのクライアントはpkgを使用して配信できますが、変更する必要はありません。 キャプテンとしての可能性を事前に謝罪しますが、テキストは初心者向けと、必要に応じて頭のすべてをすばやく更新するために作成されたため、すべてのチームが詳細に描かれています。

インストールと基本的なサーバーのセットアップ

root@leninzhiv> cd /usr/ports/databases/postgresql92-server root@leninzhiv> make fetch root@leninzhiv> make extract
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ダウンロードしたソースファイルは、ポートディレクトリの作業フォルダーに展開されます。 私は正直に、変更後にソースを再構築する方法を理解していませんでした。make再構築、make cleanはありませんでしたが、すべての変更でこのフォルダを単純に破壊します。 そこで、作業フォルダーをホームディレクトリにコピーし、そこで変更を加えてから、ポートフォルダーにコピーしてmake installを実行しました。



まだ何も変更していません。postgresを設定するだけです：

 root@leninzhiv> make install
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

アーカイブ用のフォルダーを作成します。

 root@leninzhiv> mkdir -p /usr/db_archive/wal root@leninzhiv> mkdir -p /usr/db_archive/data root@leninzhiv> chown -R pgsql:wheel /usr/pg_archive
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Postgresでは、データディレクトリにアクセスできるのはユーザーのみである必要があるため、権限を変更します。

 root@leninzhiv> chmod 0700 /usr/pg_archive/data
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

プリミティブなセットアップを行います。 ここでは、pgsql postgresアカウントに切り替えて、ファイルのアクセス権に関する面倒を少なくするのが理にかなっています。

 root@leninzhiv> su - pgsql pgsql@leninzhiv> initdb -D /usr/local/pgsql/data
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

/usr/local/pgsql/data/postgresql.confのWALログアーカイブパラメーターのコメントを外して編集します。

archive_mode = on

wal_level =アーカイブ

archive_command = 'テスト！ -f / usr / db_archive / wal /％f && cp％p / usr / db_archive / wal /％f '

（例はkamentyの近くにあります）

max_wal_senders = 1



/usr/local/pgsql/data/pg_hba.confで行のコメントを解除します

ローカルレプリケーションpgsql trust



サーバーを起動します

 pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql start
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

基本的なバックアップを作成する

 pgsql@leninzhiv> pg_basebackup -D /usr/db_archive/data/
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

フォルダー/ usr / db_archive / data /にデータディレクトリのコピーがあること、/ usr / db_archive / wal /に約000000010000000000000003という形式のWALファイルがあることを確認します。



構成ファイルを回復用にデータディレクトリのバックアップフォルダーにコピーします

 cp /usr/local/share/postgresql/recovery.conf.sample /usr/db_archive/data/recovery.conf
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

コメントを解除して、復元コマンドを編集します（例はコメントの近くにあります）。

restore_command = 'cp / usr / db_archive / data /％f％p'



エントリを作成します。

 pgsql@leninzhiv> psql -U pgsql -d postgres
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 postgres=# CREATE TABLE z (z_id serial, z_text character(50)); postgres=# INSERT INTO z (z_text) VALUES ('Karlin'); postgres=# INSERT INTO z (z_text) VALUES ('Petrov'); postgres=# INSERT INTO z (z_text) VALUES ('Ivanov'); postgres=# INSERT INTO z (z_text) VALUES ('Kaplan'); postgres=# INSERT INTO z (z_text) VALUES ('Karas'); postgres=# INSERT INTO z (z_text) VALUES ('Bukova'); postgres=# INSERT INTO z (z_text) VALUES ('Sidorova'); postgres=# INSERT INTO z (z_text) VALUES ('Karman'); postgres=# INSERT INTO z (z_text) VALUES ('Nikolaev');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

レコードを削除する：

 postgres=# DELETE FROM z WHERE z_text ILIKE 'Ka%';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

レコードの変更、新しいレコードの作成、ディスコ

 postgres=# UPDATE z SET z_text='Petrova' WHERE z_text='Sidorova'; postgres=# INSERT INTO z (z_text) VALUES ('Kruglov'); postgres=# UPDATE z SET z_text='Alexeeva' WHERE z_text='Bukova'; postgres=# INSERT INTO z (z_text) VALUES ('Kvadrat');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

マスクエントリを削除するのは得策ではないことがわかりました。Karlinとともに、Kaplan、Karas、Karmanを削除しました。



サーバーを停止します

 pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql stop pgsql@leninzhiv> exit root@leninzhiv>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして何をすべきか考え始めます。

ソースに行きます

覚えているように、make extractの後に、作業フォルダーをポートディレクトリからホームフォルダーにコピーし、変更を加えました。 したがって、我々はそこに行きます。 ポートフォルダー自体のソースコードを変更する方法を誰かが教えて、コードに加えられた変更後にすべてが適切に再構築されるようになれば、非常に感謝します。



最初に、WALログがファイルから読み取られる場所を見つけるという目標を設定しました。



work / postgresql-9.2.8 / srcディレクトリと常識のファイルの内容で「WAL」行を検索して、WAL関連のコードを含むファイルを見つけました。これはxlog.cファイルであることが判明しました。



Cプログラムをトレースする方法がわからないので、各関数の先頭でその名前のレコードをファイルに追加し、ビルドして実行しました。



結果はファイルに次のようになります。

 bool check_wal_buffers(int *newval, void **extra, GucSource source) void assign_xlog_sync_method(int new_sync_method, void *extra) Size XLOGShmemSize(void) static int XLOGChooseNumBuffers(void) bool check_wal_buffers(int *newval, void **extra, GucSource source) void XLOGShmemInit(void) Size XLOGShmemSize(void) static void ReadControlFile(void) void StartupXLOG(void) static void ReadControlFile(void) static char * str_time(pg_time_t tnow) static void ValidateXLOGDirectoryStructure(void) static void readRecoveryCommandFile(void) static List * readTimeLineHistory(TimeLineID targetTLI) static bool read_backup_label(XLogRecPtr *checkPointLoc, bool *backupEndRequired, bool *backupFromStandby) static XLogRecord * ReadCheckpointRecord(XLogRecPtr RecPtr, int whichChkpt) static XLogRecord * ReadRecord(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt) static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt, bool randAccess) static int XLogFileReadAnyTLI(uint32 log, uint32 seg, int emode, int sources) ... static XLogRecord * ReadRecord(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt) static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt, bool randAccess) static bool RecordIsValid(XLogRecord *record, XLogRecPtr recptr, int emode) static bool recoveryStopsHere(XLogRecord *record, bool *includeThis) static void CheckRecoveryConsistency(void) static XLogRecord * ReadRecord(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt) static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt, bool randAccess) ...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

一般的に、メインアクションはReadRecord-> XLogPageRead-> RecordIsValid-> RecoveryStopsHere-> CheckRecoveryConsistencyループで実行されるという印象を受けました。



ReadRecord関数をよく知っていると、戻りレコードと戻り（XLogRecord *）バッファーの2つの場所でレコードが返されることがわかりました。上記の簡単な方法で、WALログから回復する過程で戻りがreturn（XLogRecord *）バッファーを通過することを明らかにしました。 いいね！ 結果をファイルに書き込みます。



タイプXLogRecordの構造はxlog.hファイルで表示でき、非常に簡潔です。

 typedef struct XLogRecord { pg_crc32 xl_crc; /* CRC for this record */ XLogRecPtr xl_prev; /* ptr to previous record in log */ TransactionId xl_xid; /* xact id */ uint32 xl_tot_len; /* total len of entire record */ uint32 xl_len; /* total len of rmgr data */ uint8 xl_info; /* flag bits, see below */ RmgrId xl_rmid; /* resource manager for this record */ /* ACTUAL LOG DATA FOLLOWS AT END OF STRUCT */ } XLogRecord;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

さて、長さがある場合は、それを使用してレコードの内容をファイルに出力し、リターン（XLogRecord *）バッファーを追加します：

 FILE *pf2 = fopen("/usr/local/pgsql/data/log3.txt", "a"); char *buf_poi = buffer; for (uint32 i=0; i < record->xl_tot_len; i++) {fputc(*buf_poi, pf2); buf_poi++;} fprintf(pf2, "\n crc32: %u \n xl_xid=%i \n", record->xl_crc, record->xl_xid); fclose(pf2);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

古いPostgresを破壊し、新しいPostgresを組み立ててインストールします。

 root@leninzhiv> cd /usr/ports/databases/postgresql92-server root@leninzhiv> make deinstall
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

作業ディレクトリをホームフォルダーにコピーし、そこですべてのコードを変更したことを思い出してください。 次に、それをポートディレクトリの作業フォルダーの場所にコピーします。

 root@leninzhiv> rm -R /usr/ports/databases/postgresql92-server/work root@leninzhiv> cp -R ~/work /usr/ports/databases/postgresql92-server/work root@leninzhiv> make install
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データベースファイルを削除し、ベースバックアップをそれらの場所にコピーします。 WALファイル自体が追いつきます。

 root@leninzhiv> su - pgsql pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql stop pgsql@leninzhiv> rm -R /usr/local/pgsql/data pgsql@leninzhiv> cp -R /usr/db_archive/data /usr/local/pgsql/data pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql start pgsql@leninzhiv> psql -U pgsql -d postgres
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 postgres=# select * from z; postgres=# \q
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 pgsql@leninzhiv> /usr/local/etc/rc.d/postgresql stop
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

log3.txtファイルの内容を確認します。最初に、多くの大きなレコード、明らかにサービステーブルとデータの作成を確認します。

 #{####T#####r###R#### ###### ####0###@###### #e######## ###gNikolaev crc32: 3682278083 l_xid=1002 W# #####U#####,### ###`#######›Ќ%ћ###### crc32: 3423214679 xl_xid=1002 r" ####xU#####5######## ###### ####0###@########## crc32: 2698322546 xl_xid=1003 #%2####U#####5######## ###### ####0###@########## crc32: 841341184 xl_xid=1003 #W####U#####5######## ###### ####0###@########## crc32: 3881244668 xl_xid=1003 Z7######V#####5######## ###### ####0###@########## crc32: 4028315482 xl_xid=1003 µЄЈђ####PV#####,### ###`########ЄЌ%ћ###### crc32: 2426645173 xl_xid=1003 њ-B####€V#####y###Y###@ ###### ####0###@########I##### ####Ђ#(######gPetrova crc32: 1110285523 xl_xid=1004
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ニコラエフとペトロフのよく知られた名前の間には、同じ取引番号の下で4つの類似したエントリと1つの異なるエントリがあることがわかります。 明らかに、これらは削除コマンドです。つまり、「テーブル64822の行50を削除」などのコマンドはすでにWALログに書き込まれています。 原則として、予想どおり。 xl_xid = 1003で、書き込みの代わりにNULLを返すチェックを追加します。



再び、古いPostgresを削除し、新しいPostgresを収集してインストールし、リカバリを開始します



所定の場所でレコードを削除しました！ 確かに、削除後に発生するはずのすべてが発生しませんでした。



そのため、目標2は、記録の「再生」がどこに向かっているかを見つけることです。 同じファイルでreadRecordの使用をすばやく検索した結果、void StartupXLOG（void）関数が見つかりました...そして、この関数のreadRecord呼び出しの2番目または3番目の出現直後に、間違った方向に進んでいたことがわかりました次）すぐに最初のシックな診断ピースが来て、2番目に、「WALレコード自体を適用する」というコメントの直後-レコードを失うコマンドRmgrTable [record-> xl_rmid] .rm_redo（EndRecPtr、record）;



このコードを次のように変更します

 if (record->xl_xid==1003) {} else RmgrTable[record->xl_rmid].rm_redo(EndRecPtr, record);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

再構築、起動、再確認...勝利！ 削除されたレコードが削除され、削除後に行われた変更も適用されます！

地形に焦点を当てます

まあ、これは間違いなく良いことですが、非常に限られたデータセットで問題を解決しましたが、作業データベースのログで必要なレコードを見つける方法は？



StartupXLOG関数で言及したシックな診断部分に戻りましょう。

 #ifdef WAL_DEBUG if (XLOG_DEBUG || (rmid == RM_XACT_ID && trace_recovery_messages <= DEBUG2) || (rmid != RM_XACT_ID && trace_recovery_messages <= DEBUG3)) { StringInfoData buf; initStringInfo(&buf); appendStringInfo(&buf, "REDO @ %X/%X; LSN %X/%X: ", ReadRecPtr.xlogid, ReadRecPtr.xrecoff, EndRecPtr.xlogid, EndRecPtr.xrecoff); xlog_outrec(&buf, record); appendStringInfo(&buf, " - "); RmgrTable[record->xl_rmid].rm_desc(&buf, record->xl_info, XLogRecGetData(record)); elog(LOG, "%s", buf.data); pfree(buf.data); } #endif
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

pg_config_manual.hで#define WAL_DEBUGのコメントを外し、postgresql.confファイルにwal_debug = onを追加するだけで、ログに出力を含めることができますが、習慣的に、出力を別のファイルに送信しました。 私が理解しているように、この部分はrm_desc関数を使用してコマンドの説明を表示します（この場合、RmgrTableは関数の配列ですか？）。次のようになります。

 REDO @ 0/3015500; LSN 0/3015578: prev 0/30154D0; xid 1002; len 82: Heap - insert: rel 1663/12318/16386; tid 0/9 REDO @ 0/3015578; LSN 0/30155A8: prev 0/3015500; xid 1002; len 12: Transaction - commit: 2014-06-06 08:38:27.537874+00 REDO @ 0/30155A8; LSN 0/30155E0: prev 0/3015578; xid 1003; len 21: Heap - delete: rel 1663/12318/16386; tid 0/1 REDO @ 0/30155E0; LSN 0/3015618: prev 0/30155A8; xid 1003; len 21: Heap - delete: rel 1663/12318/16386; tid 0/4 REDO @ 0/3015618; LSN 0/3015650: prev 0/30155E0; xid 1003; len 21: Heap - delete: rel 1663/12318/16386; tid 0/5 REDO @ 0/3015650; LSN 0/3015688: prev 0/3015618; xid 1003; len 21: Heap - delete: rel 1663/12318/16386; tid 0/8 REDO @ 0/3015688; LSN 0/30156B8: prev 0/3015650; xid 1003; len 12: Transaction - commit: 2014-06-06 08:38:27.54153+00 REDO @ 0/30156B8; LSN 0/3015738: prev 0/3015688; xid 1004; len 89: Heap - hot_update: rel 1663/12318/16386; tid 0/7; new 0/10
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これは、トランザクション番号1003で既にわかっている部分であり、そこからはい、これらは4つの削除コマンドと1つのトランザクション確認であることがわかります。 削除コマンドでは、rel-「oid namespace / oid database / oid table」という形式のテーブルの識別子が表示されます。 対応する番号はリクエストで取得できます



SELECT oid、spcname FROM pg_catalog.pg_tablespace;

SELECT oid、datname FROM pg_catalog.pg_database;

そして、突然、

SELECT oid、relname FROM pg_catalog.pg_class;



2番目のガイドラインは、トランザクションの説明にタイムスタンプがあることです。 さて、ここで何かを説明する必要はありません。この同じ犯罪がいつ犯されたかがわかれば、対応する記録が見つかります。



さて、別の方法として、krakozyabraのエントリの表示に戻り、目的の「誤った」リクエストの直前または直後にパラメータが作成されたクエリを覚えていれば、INSERTおよびUPDATEコマンドにパラメータとして渡されたテキストの一部をナビゲートできます。 ただし、UPDATEの場合、新しい値として使用された行のみを見つけることができます。その行がレコードの検索に使用された場合、WALログでは発生しません。



最後に、PostgreSQL 9.3カウンターにpg_xlogdumpユーティリティが登場しました。これは、人間が読める形式でWALログコンテンツを提供する問題を解決することを目的としているようです。 一部の機能に興味がある場合は、開発者に連絡するのが理にかなっています。



作業中のデータベースのアーカイブでこの方法を使用すると、いくつかの落とし穴が生じる可能性があります。 たとえば、頻繁な避難を使用するデータベース内のレコードの一部を「スキップ」すると、UPDATEはどのように機能しますか？ チェックしませんでした。 しかし、いずれにせよ、その場合には、エラーを修正することをまったく望んでいないよりも、少なくともある程度の希望を持っている方が良いです。