本記事は PostgreSQL Advent Calendar 2018の19日目です。
昨日は@yancyaさんの、JSON からテーブルへ値を入れるためのSQL についての記事でした。
自分は、複雑なSQLは今まで全然書いたことがないので、こういうのが書けるように勉強しないとなぁと思うばかりです。（勉強になります。）
はじめに
本記事では、タイトルどおりPG12の機能について、いくつか見てきているものがあるので、確認した範囲でご紹介しようと思います。
（コミットログを見ていたら、触ってみたくなってしまいました）
※注意：ここから書く情報は開発途中のもので試しているため、正式版では挙動が異なるものとあると思います。あくまで2018年12月時点での情報であることにご注意ください。
今回確認した新機能
本記事で紹介するのは以下の機能です。
recovery.confのpostgresql.confへの統合 （今回見た中では、一番影響が大きそうな話）
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2dedf4d9a899b36d1a8ed29be5efbd1b31a8fe85promote用のSQL関数 pg_promote()の追加
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=10074651e3355e2405015f6253602be8344bc829パーティショニングツリーの可視化する関数 pg_partition_tree()の追加
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d5eec4eefde70414c9929b32c411cb4f0900a2a9pg_stat_replicationにStandbyサーバでWALを最後に適用した時間を表示
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7fee252f6fbf78ca5e50ee591573d59f98e75d37
確認結果
・・・に入る前にまずは、PG12環境ですが、Gitからソースを取得し、ビルドすればOKです。
参考にコマンドを載せていますが、適当なオプションでやっていますので必要に応じて、オプションは適宜指定してください。
$ git clone https://git.postgresql.org/git/postgresql.git
$ cd postgresql
$ ./configure --prefix=/home/kazu/local/pg12/pgsql --with-libxml --with-libxslt --enable-debug --enable-cassert
$ make
$ make install
#環境変数は適宜指定してください
$ initdb -D $PGDATA --no-locale
$ pg_ctl -D $PGDATA start
psql等のバージョンが「12devel」になっていれば、OKです。
$ psql postgres
psql (12devel)
Type "help" for help.
1. recovery.confのpostgresql.confへの統合
そのままですが、これまでStandbyが使用していたrecovery.confが廃止され、パラメータはpostgresql.confに統合されます。
この記事では、単純なレプリケーション接続の設定しか実施していませんが、recovery_target等のパラメータも全てpostgresql.confに移されていますので、PITRをやる場合もpostgresql.confを編集する必要があります。
では、早速試してみます。
まずはPrimaryサーバとして、テーブルを作成して初期データを投入しておきます。
$ psql postgres
psql (12devel)
Type "help" for help.
postgres=# create table foo (i int, v varchar);
CREATE TABLE
postgres=# insert into foo values (1,'jajamaru'),(2,'pikkoro');
INSERT 0 2
postgres=# select * from foo;
i | v
---+----------
1 | jajamaru
2 | pikkoro
(2 rows)
今回は同じサーバ上にPort番号だけ変えて、Standbyサーバを起動します。
Standbyサーバはpg_basebackupを使用して構築します。
$ pg_basebackup -h localhost -D $HOME/rep/data -X stream --progress -U kazu -R
24485/24485 kB (100%), 1/1 tablespace
余談のつもりが結構大事な話：
なんで、-Rオプションを付けているんだろう？と思われているかと思います。
ご存知のとおり、PG11までは-Rオプションを付けることで recovery.conf が作成されていました。じゃあ、新しいPG12だといらないんじゃないか？答えは No でした。必要です。
PG12の場合、recovery.confの代わりにstandby.signalというファイルが作成されます。このファイルが無い状態でベースバックアップを起動するとPrimaryとして起動してしまうので注意が必要です。
※ちなみにオプションを付けなくても touch でファイルを作成してもStandbyサーバとして起動可能です！$ pg_basebackup --help pg_basebackup takes a base backup of a running PostgreSQL server. Usage: pg_basebackup [OPTION]... Options controlling the output: ・・・ -R, --write-recovery-conf write configuration for replication ・・・ $ ls -l rep/data/standby.signal -rw------- 1 kazu kazu 0 12月 18 14:43 rep/data/standby.signal
Standbyサーバでのパラメータの変更は以下のとおり。（standby_modeの項目は無くなっていました。）
・・・
#port = 5432
port = 5433
・・・
# - Standby Servers -
# These settings are ignored on a master server.
#primary_conninfo = '' # connection string to sending server
# (change requires restart)
primary_conninfo = 'user=kazu host=192.168.1.1 port=5432'
Standbyサーバを起動します。
$ pg_ctl -D /home/kazu/rep/data start
$ ps -ef | grep postgres
kazu 8448 1 0 14:01 pts/0 00:00:00 /home/kazu/local/pg12/pgsql/bin/postgres -D /home/kazu/local/pg12/data
kazu 8449 8448 0 14:01 ? 00:00:00 postgres: logger
kazu 8451 8448 0 14:01 ? 00:00:00 postgres: checkpointer
kazu 8452 8448 0 14:01 ? 00:00:00 postgres: background writer
kazu 8453 8448 0 14:01 ? 00:00:00 postgres: walwriter
kazu 8454 8448 0 14:01 ? 00:00:00 postgres: autovacuum launcher
kazu 8455 8448 0 14:01 ? 00:00:00 postgres: archiver last was 000000010000000000000004
kazu 8456 8448 0 14:01 ? 00:00:00 postgres: stats collector
kazu 8457 8448 0 14:01 ? 00:00:00 postgres: logical replication launcher
kazu 8808 1 0 14:09 pts/1 00:00:00 /home/kazu/local/pg12/pgsql/bin/postgres -D /home/kazu/rep/data
kazu 8809 8808 0 14:09 ? 00:00:00 postgres: logger
kazu 8810 8808 0 14:09 ? 00:00:00 postgres: startup recovering 000000010000000000000005
kazu 8811 8808 0 14:09 ? 00:00:00 postgres: checkpointer
kazu 8812 8808 0 14:09 ? 00:00:00 postgres: background writer
kazu 8813 8808 0 14:09 ? 00:00:00 postgres: stats collector
kazu 8814 8808 0 14:09 ? 00:00:00 postgres: walreceiver streaming 0/5000060
kazu 8815 8448 0 14:09 ? 00:00:00 postgres: walsender kazu ::1(57624) streaming 0/5000060
念の為、Primaryサーバでレコードを追加して、レプリケーションされているのか確認しておきます。
$ psql postgres -c "insert into foo values (3,'porori')"
INSERT 0 1
$ psql postgres -p 5433 -U kazu -c "select * from foo"
i | v
---+----------
1 | jajamaru
2 | pikkoro
3 | porori
(3 rows)
新しい設定でのレプリケーション設定について確認しました。
recovery.confがpostgresql.confに組み込まれたことで管理するファイルが減るのはいい事ですね！
ちなみにですが、うっかり手動でrecovery.confを作成していると。。。エラーで起動できないので過去のシステムをアップグレードする場合は要注意ですね。
LOG: database system was shut down in recovery at 2018-12-04 14:16:46 JST FATAL: using recovery command file "recovery.conf" is not supported LOG: startup process (PID 8948) exited with exit code 1 LOG: aborting startup due to startup process failure LOG: database system is shut down
2. promote用のSQL関数 pg_promote()の追加
これまでは、Standbyサーバで、$ pg_ctl promoteを実行する必要がありましたが、PG12からはSQL関数で可能になります。
Standbyサーバに対して、以下のSQLを実行するだけでPromoteできます。
$ psql postgres -p 5433 -U kazu -c "select pg_promote()"
pg_promote
------------
t
(1 row)
関数実行後、StandbyサーバがPrimaryサーバに昇格し稼働していることが確認できます。
[kazu@node_yama ~]$ psql postgres -p 5433 -U kazu -c "insert into foo values (4,'kikkoro')"
INSERT 0 1
[kazu@node_yama ~]$ psql postgres -p 5433 -U kazu -c "select * from foo"
i | v
---+----------
1 | jajamaru
2 | pikkoro
3 | porori
4 | kikkoro
(4 rows)
この機能追加によって、これまでHA製品でFOを実施する場合に余計な権限を与える必要がなくなるので、良さそうですね。セキュリティに関するニーズが高まっている昨今、権限分掌は着々と進んでいることがわかります。
3. psqlのCSV表示
\pset formatでcsvを指定することができるようになりました。
postgres=# \pset format csv
Output format is csv.
postgres=# select * from foo;
i,v
1,jajamaru
2,pikkoro
3,porori
補足すると、PG11以前でも以下の手順でCSV出力は可能だったので、これ自体は大きな変更ではないです。
とは言いつつ、CSVと書けるほうがわかりやすいのでこれも良い変更かと。
# 別の手順でCSV表示に変更
postgres=# \pset format unaligned
Output format is unaligned.
postgres=# \f ,
Field separator is ",".
postgres=# select * from foo;
i,v
1,jajamaru
2,pikkoro
3,porori
(3 rows)
4. パーティショニングツリーの可視化 pg_partition_tree()の追加
PG10から宣言的パーティショニングの実装により、パーティショニングは非常に使いやすくなったと思います。
PG12では、パーティショニングが複雑になった場合にも備えて、パーティショニングツリーを可視化できる機能が追加されています。
挙動の確認のため、以下のような構成でパーティショニングテーブルを作成します。
作成に使用したSQLは以下のとおり。
CREATE TABLE bar(i int,v varchar) PARTITION BY RANGE (i);
CREATE TABLE bar_h PARTITION OF bar (PRIMARY KEY(i)) FOR VALUES FROM (0) TO (3);
CREATE TABLE bar_m PARTITION OF bar (PRIMARY KEY(i)) FOR VALUES FROM (3) TO (6);
CREATE TABLE bar_l PARTITION OF bar (PRIMARY KEY(i)) FOR VALUES FROM (6) TO (9) PARTITION BY HASH (i);
CREATE TABLE bar_l_a PARTITION OF bar_l FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE bar_l_b PARTITION OF bar_l FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE bar_l_c PARTITION OF bar_l FOR VALUES WITH (MODULUS 3, REMAINDER 2);
これまでは、psqlで「\d+」等を使用して確認する必要がありましたが、追加された関数を使用すると簡単に確認することができます。
postgres=# SELECT * FROM pg_partition_tree('bar');
relid | parentrelid | isleaf | level
---------+-------------+--------+-------
bar | | f | 0
bar_h | bar | t | 1
bar_m | bar | t | 1
bar_l | bar | f | 1
bar_l_a | bar_l | t | 2
bar_l_b | bar_l | t | 2
bar_l_c | bar_l | t | 2
(7 rows)
多段のパーティショニングを今後利用することがある場合、こういった機能があることを覚えておいていただくと良いかもしれません。
ちなみにですが・・・
以前、PG11でのプルーニングについて「PostgreSQL11でのパーティショニング機能の進化（の細かいところ）」というタイトルで記事を書いていたのですが、全ての子テーブルに対してロックが取得される挙動は現時点のPG12でもまだ変わっていませんでした。きっとこれから変わるんだと思います。
変更を確認できたら、記事を更新しておきます。
5. 新規パラメータ追加
- log_statement_sample_rate
log_statement_sample_rate
これはlog_min_duration_statementの出力を制限するためのパラメータです。
スロークエリをログに出力する際に、その割合を0～1の範囲で設定することができます。
例えば、5ms以上掛かるSQLをログ出力する場合は以下の設定となります。
log_min_duration_statement = 5ms
log_statement_sample_rate = 0.5
この状態で時間の掛かるSQL「psql postgres -c "select pg_sleep(1)"」を10回実行した際のログは以下のとおりでした。
LOG: duration: 2004.158 ms statement: select pg_sleep(2)
LOG: duration: 2003.888 ms statement: select pg_sleep(2)
LOG: duration: 2007.858 ms statement: select pg_sleep(2)
LOG: duration: 2004.912 ms statement: select pg_sleep(2)
LOG: duration: 2001.030 ms statement: select pg_sleep(2)
LOG: duration: 2002.777 ms statement: select pg_sleep(2)
LOG: duration: 2002.096 ms statement: select pg_sleep(2)
結果、7回メッセージが出力され、半分とはいかなかったですが、ある程度出力を抑えられることは確認できました。
性能問題が出ているとdurationのログが大量に出力され、IO負荷増加によりさらに性能問題を引き起こしてしまう可能性があるので、この設定を追加しておくことで、その可能性を抑えることができそうです。
ただ、このパラメータを設定していると運が悪い場合、スロークエリが発見できないという可能性にもつながるので、その点は考慮しておくのが良いと思います。とは言いつつ、まぁ大量ログが出てしまうと致命傷にもなる可能性があるので、多少の取りこぼしには目を閉じて、パラメータを設定しておくのが安全かと思いました。
6. pg_stat_replicationにStandbyサーバでWALを最後に適用した時間を表示
- Add timestamp of last received message from standby to pg_stat_replication
タイトルのとおりですが、pg_stat_replicationに新しい項目として「reply_time」が追加されました。
この項目では、StandbyサーバからPrimaryサーバにreplyしたというメッセージを受け取った最後の時間を表示しています。
$ psql postgres -p 5432 -U kazu -c "select application_name,state,sync_state,reply_time from pg_stat_replication"
application_name | state | sync_state | reply_time
------------------+-----------+------------+-------------------------------
walreceiver | streaming | async | 2018-12-18 14:57:03.768817+09
(1 row)
今までは、「replay_lsn」と送ったWALの位置からreplyができているか等を確認していましたが、これからは現在時刻とこの適用時間の差を見て、Standbyサーバでの処理が遅れているかどうかを確認することができるようになりました。
実際、そこまで大きな情報が加わったという話ではないですが、遅延が発生している場合に より わかりやすくなった。というイメージかと思います。
さいごに
PG11が出たばかりではありますが、着々とPG12に向けての開発が進んでいるのを感じることができました！
ものによっては、システムやサードパーティの製品への影響がでかいもあるので、関係ある方は定期的にウォッチしたほうが良さそうですね。自分もこれからまだまだPostgreSQLは触っていくつもりなので、既存機能の使いこなしもそうですが、新機能についても見ていきたいと思います。
気になる機能があれば、また試してみます。