読者です 読者をやめる 読者になる 読者になる

PostgreSQL: ポイントインタイムリカバリ(PITR) と Transaction ID

PostgreSQL の「ポイントインタイムリカバリ(PITR)」について調べていて、ドキュメントの中に気になる記述を見つけた。

もし以前のある時点まで復旧させたい場合(例えば、経験不足のデータベース管理者が主トランザクションテーブルを消去した直前)、recovery.confに要求する停止時点を指定するだけです。 停止時点は、「recovery target」として既知の停止時点で指定することも、日付と時刻で指定することも、リストアポイントか完了した特定のトランザクションIDで指定することもできます。 本ドキュメントの執筆時点では使用するトランザクションIDの識別を補助するツールがありませんので、ほとんどの場合は日付と時刻による指定のみを使用することになるでしょう。 https://www.postgresql.jp/document/9.6/html/continuous-archiving.html

データベースに対して何か致命的な操作をしてしまったとき、例えば WHERE を付けずに DELETE してしまったときに、その操作の直前の状態にデータベースを戻したいとする。

このドキュメントによれば、recovery.conf に復旧したい時刻を指定すべきであるそうだ。例えば、こんな風に。

recovery_target_time = '2017-05-02 09:00:00 JST'

私は recovery.confrecovery_target_xid という設定項目があることを知っていたので、不思議に思った。致命的な操作のトランザクションID(xid)を調べて、そのひとつ前のトランザクションIDをここに指定すればいいのではないか。

しかし、これはふたつの理由でうまく行かない。

ひとつの理由は、さきほどの引用にもある通り「トランザクションIDの識別を補助するツール」が存在しないためである。

もうひとつの理由は、「トランザクションIDはトランザクションの開始時に順番に割り振られ、トランザクションはそれとは異なる順番で完了し得る(PosgreSQL のドキュメント)」からである。


トランザクションIDの識別に関しては、ログにトランザクションIDとSQLステートメントを記録すればいいんじゃないかとも考えたけれど、これもダメである。

postgresql.conflog_line_prefix という設定項目があり、ここに %x というプレースホルダーを指定すれば「トランザクションID」が記録されるのだが、トランザクション内で 2 番目以降に発行された変更ステートメントにしか記録されない、という重大な制限がある。

http://stackoverflow.com/q/39585279/513554 を参照。

本当かなぁと疑って、私自身で実際にやってみたけれど、その通りであった。トランザクション内の 1 番目に発行されたステートメントの「トランザクションID」は 0 と記録される。

PostgreSQL のアップグレード時に明らかになる pg_wrapper の役割

PostgreSQL 9.5 のサーバーがデフォルトの 5432 番ポートで動いている状態で、PostgreSQL 9.6 のサーバーをインストールするとどうなるか。

$ apt-get install postgresql-9.6

結果。PostgreSQL 9.6 の main クラスタが 5433 番ポートで立ち上がる。

この状態で psql コマンドのバージョンを調べると、9.5.4 のように古いバージョン番号が報告される。

なぜか。

実は、psqlpg_dumppg_restorepg_basebackup などのコマンドは /usr/share/postgresql-common/pg_wrapper へのリンクとなっていて、この pg_wrapper がさらに適切なバージョンの「本物」のコマンドを実行する、という仕組みになっているからである。

PostgreSQL 9.6 用の「本物」の psql/usr/lib/postgresql/9.6/bin にある。

pg_wrapper が「本物」のコマンドを選択する方法については、man pg_wrapper に書いてある。その仕組みはかなり複雑だが、私たちが特に何もしていない場合、デフォルトの 5432 番ポートを listen している PostgreSQL サーバーのバージョンを使うことになる。つまり、現状では 9.5 だ。

ここで、PostgreSQL 9.5 をアンインストールしてみよう。

apt-get remove postgresql-9.5

5432 番を listen している PostgreSQL サーバーは停止されて、PostgreSQL 9.5 のプログラムは除去される。

そして psql --version を実行すると、次のようなエラーメッセージが出る。

Error: No existing local cluster is suitable as a default target.
Please see man pg_wrapper(1) how to specify one.

5432 番を listen している PostgreSQL サーバーが存在しないからだ。

しかし、psql --port=5433 --version というコマンドを実行すれば、正しく 9.6.2 という結果を返す。

だから、さきほどのエラーメッセージについては気にしなくてもいい。

もし気になるのであれば、いくつか回避方法がある。

  1. 環境変数 PGPORT5433 をセットする。
  2. /etc/postgresql/9.6/main/postgresql.confport 属性の値を 5432 に変更して、PostgreSQL サーバーを再起動する。

PostgreSQL: MySQL の SHOW CREATE DATABASE の代わりになるもの

私の知る限りにおいて、PostgreSQL のコンソールであるデータベースの CREATE DATABASE 文を知る方法はない。すなわち、MySQL コンソールの SHOW CREATE DATABASE コマンドに対応するコマンドはない。

しかし、代わりに次のようなコマンドを実行すればよい:

sudo -u postgres pg_dump --schema-only --create db0 \
| grep "CREATE DATABASE"

あるいは、もっと簡潔に

sudo -u postgres pg_dump -s -C db0 | grep "CREATE DATABASE"

ただし、db0 はデータベース名の例であるので、適宜変更する。

pg_dump はデータベースをダンプするコマンドである。--schema-only または -s オプションを付けると、テーブルやインデックスなどの「スキーマ定義」だけがダンプされる。--create オプションまたは -C オプションを付けると、ダンプの中に CREATE DATABASE 文が書き込まれる。それを grep コマンドで抜き出している。

結果として、ターミナルには次のような結果が出力される。

CREATE DATABASE db0 WITH TEMPLATE = template0 ENCODING = 'UTF8' 
LC_COLLATE = 'ja_JP.UTF-8' LC_CTYPE = 'ja_JP.UTF-8';

PostgreSQL: 非特権ユーザーによる pg_dump と pg_restore

前提条件

  • ホスト palau の 50001 番ポートで PostgreSQL サーバが稼働している。
  • このサーバの非特権ユーザー alice がデータベース db0db1 を所有している。
  • alice のパスワードは bravo である。
  • db0 には複数のテーブルがある。ただし、スキーマpublic のみ。
  • db1 は空である。

スキーマ」という用語については、後述。

pg_dump

db0 のダンプを custom 形式で ~/db0.dump に作成する。

$ PGPASSWORD=bravo pg_dump \
--host=palau --port=50001 --user=alice \
--format=custom --no-privileges --schema=public --blobs \
--dbname=db0 --file ~/db0.dump

pg_restore

~/db0.dump db0 の内容をデータベース db1 に リストアする。

$ PGPASSWORD=bravo pg_restore \
--host=palau --port=50001 --user=alice \
--dbname=db1 ~/db0.dump

考察

--no-privileges オプションを付けないと revoke 文や grant 文がダンプファイルに書き込まれる。これらの文は、非特権ユーザーでは実行できないため、次のようなエラーを招く:

pg_restore: WARNING:  no privileges could be revoked for "public"
pg_restore: WARNING:  no privileges could be revoked for "public"
pg_restore: WARNING:  no privileges were granted for "public"
pg_restore: WARNING:  no privileges were granted for "public"

--schema=public オプションを付けないと、次のようなエラーが出る:

pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--schema=public オプションを付けたときは、--blobs オプションを付けないとラージオブジェクトのデータがダンプファイルに書き込まれない。

ただし、pg_restore はリストアの途中で出たエラーを無視するので、以上のオプションを付けなくても実害はない。

--schema=public オプションについての注記

PostgreSQL にはデータベース内のオブジェクトをグループ化するための「スキーマ」という概念がある。データベースの構造を意味する一般用語の「スキーマ」とは無関係。

public はデフォルトのスキーマであり、スキーマを指定せずに作成されたオブジェクトがそこに分類される。

データベース db0public スキーマしか存在しないのであれば、本来 --schema=public というオプションは意味を持たない。しかし、このオプションを指定しないと pg_dump コマンドは plgpsql エクステンションに関するコメントをダンプファイルに書き込む。このエクステンションの所有者は特権ユーザーであるため、リストア時にエラーを引き起こす。

参考資料

PostgreSQL 9.6: レプリケーションの設定(同期)

この記事は、PostgreSQL 9.6: レプリケーションの設定(非同期)の続きです。

同期レプリケーションモードへの移行

postgresql.conf (マスター側)の書き換え

# sudo vim /etc/postgresql/9.6/cluster1/postgresql.conf
synchronous_standby_names = 'cluster2'
  • synchronous_standby_names には、スレーブ側の recovery.conf (後述)に記載する primary_conninfoapplication_name として指定する。複数の名前を指定する場合はコンマで区切る。

recovery.conf の書き換え

テキストエディタ/var/lib/postgresql/9.6/cluster2/recovery.conf を開き、primary_conninfo に指定された文字列の末尾に application_name=cluster2 を追加する。

このファイルは pg_basebackup コマンドによって生成される(-R オプションを指定した場合)。

ちなみに primary_conninfo に指定される文字列は attr=value の形式の設定値をスペースで連結したもの。初期値は以下のようになっている。

  • user=repl_user
  • password=p@ssw0rd
  • host=127.0.0.1
  • port=54321
  • sslmode=prefer
  • sslcompression=1
  • krbsrvname=postgres

application_name の値は、マスター側の postgresql.conf に記載する synchronous_standby_names の値と一致させる。

recovery.conf には、primary_conninfo の他に standby_moderepl_slot_name という設定項目がある。

standby_mode の値は on とする。repl_slot_name の値は、マスター側で追加したレプリケーションスロットの名前と同じにする。本稿では application_name と同じにしたが、異なっていてもよい。

クラスタの再起動

$ sudo systemctl restart postgresql@9.6-cluster2
$ sudo systemctl restart postgresql@9.6-cluster1

リロードではうまく行かない。

同期レプリケーションの動作確認

マスター側に接続

$ sudo -u postgres psql --port 54321
> \x

レプリケーションの状態

> SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 31820
usesysid         | 16384
usename          | repl_user
application_name | cluster2
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 56424
backend_start    | 2017-04-23 20:33:40.746694+09
backend_xmin     | 
state            | streaming
sent_location    | 0/D002490
write_location   | 0/D002490
flush_location   | 0/D002490
replay_location  | 0/D002490
sync_priority    | 1
sync_state       | sync

application_name 列と sync_state 列をチェック。

データベースを作成してみる

> CREATE DATABASE repl_test2;

しばらく反応がなければ、レプリケーションの設定がうまく行っていない。Ctrl-C を入力してキャンセルする。その結果、

Cancel request sent WARNING: ユーザからの要求により同期レプリケーションの待ち状態をキャンセルしています DETAIL: トランザクションはローカルではすでにコミット済みですが、スタンバイ側にはレプリケーションされていない可能性があります。

のようなメッセージが出て入力プロンプトに戻る。この場合、マスターとスタンバイの間がずれてしまっているので、スタンバイを初めから作りなおす必要がある。

スタンバイ側に接続

$ sudo -u postgres psql --port 54322
> \x

データベース repl_test2 の情報を確認

> \l repl_test2
データベース一覧
-[ RECORD 1 ]-----+------------
名前              | repl_test2
所有者            | postgres
エンコーディング  | UTF8
照合順序          | ja_JP.UTF-8
Ctype(変換演算子) | ja_JP.UTF-8
アクセス権        | 

参考資料

PostgreSQL 9.6: レプリケーションの設定(非同期)

要点

  • MySQL 用語の「マスター」と「スレーブ」を、PostgreSQL 用語では「マスター」と「スタンバイ(standby)」と呼ぶ。
  • レプリケーションには「非同期(async)」と「同期(sync)」の区別がある。可用性を上げたいなら「同期」を選択する。
  • pg_basebackup コマンドを利用すると、マスターを停止させずにスタンバイを構築できる。
  • PostgreSQL 9.4 で導入された「ロジカルレプリケーション」、「レプリケーションスロット」を活用すべし。

参考資料

クラスタ群の作成

本稿では、同一のマシン上にふたつの PostgreSQL クラスタ cluster1cluster2 を作成し、前者をマスター、後者をスタンドバイとするレプリケーション

$ sudo pg_createcluster --port=54321 9.6 cluster1
$ sudo pg_createcluster --port=54322 9.6 cluster2

sudo -u postgres pg_createcluster ... のように postgres ユーザーで実行してもクラスタは作成される。 しかし、systemctl daemon-reload の実行ができないため警告が出る。root ユーザーで実行する。

PostgreSQL にログを英語で出力させたい場合は、--lc-messages=C オプションを加える。

参考資料

マスター側での作業

マスターサーバーの起動

$ sudo systemctl start postgresql@9.6-cluster1

sudo pg_ctlcluster 9.6 cluster1 start でもよい。

repl_user ロールの追加

$ sudo -u postgres psql --port 54321
> CREATE ROLE repl_user LOGIN REPLICATION PASSWORD 'p@ssw0rd';

パスワード p@ssw0rd は例。適宜変更する。

postgresql.conf の書き換え

テキストエディタ/etc/postgresql/9.6/cluster1/postgresql.conf に以下の設定を追加。

wal_level = logical
max_wal_senders = 16
max_replication_slots = 16
  • wal_level = logical により「ロジカルレプリケーション」が有効になる。
  • max_wal_sendersmax_replication_slots には、「スタンドバイの個数 + 1」以上の値をセットする。max_connections (デフォルト値: 100)の値を超えてはならないが、適宜余裕を持って設定する。
  • WALはWrite Ahead Loggin(ログ先行書き込み)の略。データの一貫性を確実にするための仕組み。WALファイルをスレーブに転送するプロセスがWAL Sender。
  • レプリケーションスロットは、レプリケーションの実施を確実にするための仕組み。スレーブ単位でレプリケーションの状態を管理し、スレーブが必要としているWALファイルを消したり、(スレーブが参照している)不要データ領域をVACUUM処理で削除したりしないようにできる。

pg_hba.conf の書き換え

テキストエディタ/etc/postgresql/9.6/cluster1/pg_hba.conf につぎの設定を追加。

host replication repl_user 127.0.0.1/32 md5

レプリケーションを実行するユーザー(ロール)として、先に作った repl_user を指定。 別のマシンとの間でレプリケーションを行う場合の設定については後述。

クラスタの再起動

$ sudo systemctl restart postgresql@9.6-cluster1

reload ではうまく行かない。

レプリケーションスロットの追加

$ sudo -u postgres psql --port 54321
> SELECT * FROM pg_create_physical_replication_slot('cluster2');

スタンドバイ側での作業

クラスタの停止

念のため、クラスタ cluster2 を停止する。

$ sudo systemctl stop postgresql@9.6-cluster2

pg_createcluster コマンドでクラスタ cluster2 を作ったまま起動していなければ、この手順は不要。

データディレクトリの削除

クラスタ cluster2 のデータディレクトリを削除する。

$ sudo rm -rf /var/lib/postgresql/9.6/cluster2

マスターからデータを複製

マスタークラスタからデータディレクトリを複製する。

$ sudo -u postgres \
PGPASSWORD=p@ssw0rd /usr/lib/postgresql/9.6/bin/pg_basebackup \
-h 127.0.0.1 --port=54321 -U repl_user \
-D /var/lib/postgresql/9.6/cluster2 \
-S cluster2 -X stream --progress -R

オプション -S にはレプリケーションスロットの名前を指定する。 オプション -R を付けると、recovery.conf のひな型が作成される。

postgresql.conf の書き換え

テキストエディタ/etc/postgresql/9.6/cluster2/postgresql.conf に以下の記述を追加。

hot_standby = on
hot_standby_feedback = on

レプリケーションスロットを利用するには hot_standby_feedback の値を on にしなければならない。

クラスタの起動

$ sudo systemctl start postgresql@9.6-cluster2

うまく起動しない場合は、/var/log/postgresql/postgresql-9.6-cluster2.log を見る。 psql: FATAL: the database system is starting up という行が連続する場合、postgresql.confhot_standby の値が on になっていること(誤字がないこと)を確認する。

動作確認

マスター側に接続

$ sudo -u postgres psql --port 54321
> \x

\x は「垂直表示モード」への切り替え。

レプリケーションの状態

> SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 31545
usesysid         | 16384
usename          | repl_user
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 56390
backend_start    | 2017-04-23 20:25:14.440237+09
backend_xmin     | 
state            | streaming
sent_location    | 0/D001748
write_location   | 0/D001748
flush_location   | 0/D001748
replay_location  | 0/D001748
sync_priority    | 0
sync_state       | async

sync_state が「非同期(async)」になっている。

レプリケーションスロットの状態

> SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]-------+----------
slot_name           | cluster2
plugin              | 
slot_type           | physical
datoid              | 
database            | 
active              | t
active_pid          | 31820
xmin                | 596
catalog_xmin        | 
restart_lsn         | 0/D002490
confirmed_flush_lsn | 

active 列が t である点をチェック。

データベースの作成

> CREATE DATABASE repl_test1;

スタンバイに接続

$ sudo -u postgres psql --port 54322
> \x

データベース repl_test1 の情報を確認

> \l repl_test1
データベース一覧
-[ RECORD 1 ]-----+------------
名前              | repl_test1
所有者            | postgres
エンコーディング  | UTF8
照合順序          | ja_JP.UTF-8
Ctype(変換演算子) | ja_JP.UTF-8
アクセス権        | 

\l はデータベースのリストを表示するコマンド。

同期レプリケーションモードへの移行

postgresql.conf (マスター側)の書き換え

# sudo vim /etc/postgresql/9.6/cluster1/postgresql.conf
synchronous_standby_names = 'cluster2'
  • synchronous_standby_names には、スレーブ側の recovery.conf (後述)に記載する primary_conninfoapplication_name として指定する。複数の名前を指定する場合はコンマで区切る。

recovery.conf の書き換え

テキストエディタ/var/lib/postgresql/9.6/cluster2/recovery.conf を開き、primary_conninfo に指定された文字列の末尾に application_name=cluster2 を追加する。

このファイルは pg_basebackup コマンドによって生成される(-R オプションを指定した場合)。

ちなみに primary_conninfo に指定される文字列は attr=value の形式の設定値をスペースで連結したもの。初期値は以下のようになっている。

  • user=repl_user
  • password=p@ssw0rd
  • host=127.0.0.1
  • port=54321
  • sslmode=prefer
  • sslcompression=1
  • krbsrvname=postgres

application_name の値は、マスター側の postgresql.conf に記載する synchronous_standby_names の値と一致させる。

recovery.conf には、primary_conninfo の他に standby_moderepl_slot_name という設定項目がある。

standby_mode の値は on とする。repl_slot_name の値は、マスター側で追加したレプリケーションスロットの名前と同じにする。本稿では application_name と同じにしたが、異なっていてもよい。

クラスタの再起動

$ sudo systemctl restart postgresql@9.6-cluster2
$ sudo systemctl restart postgresql@9.6-cluster1

リロードではうまく行かない。

別のマシンとの間でレプリケーションを行う

まず、マスターの postgresql.conflisten_addresses 属性にマスターが動作するマシンの IP アドレスを指定する。

listen_addresses = '192.168.0.3'

この属性のデフォルト値は localhost であり、そのままでは外部からの接続を受け付けないことになる。

次に、スタンドバイの IP アドレスまたはスタンドバイの属するネットワークの IP アドレス範囲を、マスターの pg_hba.conf に指定する。例えば、スタンドバイがネットワーク 192.168.0.0/24 に属するなら、次のように記述する。

host replication repl_user 192.168.0.0/24 md5

これらの設定を変更したら、マスターを再起動する。

$ sudo systemctl restart postgresql@9.6-cluster1

遅延レプリケーション

スタンドバイの recovery.conf に次の記述を追加すると、レプリケーションを12時間遅らせることができる。

recovery_min_apply_delay = '12h'

遅延レプリケーションを設定しておくと、誤操作等によってマスターのデータが消失・損壊したときに役に立つ。例えば、WHERE 節を指定せずに DELETE 文や UPDATE 文を発行してしまった場合に、一定時間はデータを復元できる。

複数の PostgreSQL クラスタ(インスタンス)を起動する

PostgreSQLクラスタインスタンス)管理は、MySQLmysql_multi よりも簡単。

以下、Ubuntu 16.04 で行ったトライアルの結果。

クラスタの作成

$ sudo pg_createcluster --port=54321 9.6 abc
$ sudo pg_createcluster --port=54322 9.6 xyz

sudo -u postgres pg_createcluster ... のように postgres ユーザーで実行してもクラスタは作成される。 しかし、systemctl daemon-reload の実行ができないため警告が出る。root ユーザーで実行すべし。

これで、abcxyz というふたつのクラスタが作られる。データディレクトリは、つぎの通り:

  • /var/lib/postgresql/9.6/abc
  • /var/lib/postgresql/9.6/xyz

クラスタの名前に - 記号が使えない点に注意。

クラスタのリスト

$ pg_lsclusters

結果は、つぎのように表示される(一部省略)。

Ver Cluster Port  Status Owner    Data directory
9.6 abc     54321 online postgres /var/lib/postgresql/9.6/abc
9.6 main    5432  down   postgres /var/lib/postgresql/9.6/main
9.6 xyz     54322 online postgres /var/lib/postgresql/9.6/xyz

「Status」列の値が online であれば「起動中」、down であれば「停止中」。

クラスタの起動、停止、再起動、リロード

$ sudo systemctl start postgresql@9.6-abc
$ sudo systemctl stop postgresql@9.6-abc
$ sudo systemctl restart postgresql@9.6-abc
$ sudo systemctl reload postgresql@9.6-abc

systemctl が使えない環境では sudo pg_ctlcluster 9.6 abc start コマンド等を使用する。

psql ターミナルによる接続

$ sudo -u postgres psql --port 54321

--host オプションなしの場合、Unix ソケット /var/run/postgresql/.s.PGSQL.54321 経由での接続となる。

クラスタの削除

$ sudo pg_dropcluster --stop 9.6 abc

クラスタを停止して、削除する。具体的に削除されるのは、以下のディレクトリとファイル:

  • /etc/postgresql/9.6/abc
  • /var/lib/postgresql/9.6/abc
  • /var/log/postgresql/postgresql-9.6-abc.log*