LoginSignup
31
13

More than 5 years have passed since last update.

PostgreSQL 12の新機能をいくつか試してみた

Last updated at Posted at 2018-12-18

本記事は PostgreSQL Advent Calendar 2018の19日目です。

昨日は@yancyaさんの、JSON からテーブルへ値を入れるためのSQL についての記事でした。
自分は、複雑なSQLは今まで全然書いたことがないので、こういうのが書けるように勉強しないとなぁと思うばかりです。(勉強になります。)

はじめに

本記事では、タイトルどおりPG12の機能について、いくつか見てきているものがあるので、確認した範囲でご紹介しようと思います。
(コミットログを見ていたら、触ってみたくなってしまいました)

※注意:ここから書く情報は開発途中のもので試しているため、正式版では挙動が異なるものとあると思います。あくまで2018年12月時点での情報であることにご注意ください。

今回確認した新機能

本記事で紹介するのは以下の機能です。

  1. recovery.confのpostgresql.confへの統合 (今回見た中では、一番影響が大きそうな話)
    https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2dedf4d9a899b36d1a8ed29be5efbd1b31a8fe85

  2. promote用のSQL関数 pg_promote()の追加
    https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=10074651e3355e2405015f6253602be8344bc829

  3. psqlのCSV表示
    https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=aa2ba50c2c13dd35a95a91638aa0a2b88c6df1eb

  4. パーティショニングツリーの可視化する関数 pg_partition_tree()の追加
    https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d5eec4eefde70414c9929b32c411cb4f0900a2a9

  5. 新規パラメータ追加
    https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=88bdbd3f746049834ae3cc972e6e650586ec3c9d

  6. pg_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の項目は無くなっていました。)

postgresql.conf
・・・
#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では、パーティショニングが複雑になった場合にも備えて、パーティショニングツリーを可視化できる機能が追加されています。

挙動の確認のため、以下のような構成でパーティショニングテーブルを作成します。
image.png

作成に使用した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_min_duration_statementの出力を制限するためのパラメータです。
スロークエリをログに出力する際に、その割合を0~1の範囲で設定することができます。

例えば、5ms以上掛かるSQLをログ出力する場合は以下の設定となります。

postgresql.conf
log_min_duration_statement = 5ms
log_statement_sample_rate = 0.5

この状態で時間の掛かるSQL「psql postgres -c "select pg_sleep(1)"」を10回実行した際のログは以下のとおりでした。

postgresql.log
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を最後に適用した時間を表示

タイトルのとおりですが、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は触っていくつもりなので、既存機能の使いこなしもそうですが、新機能についても見ていきたいと思います。
気になる機能があれば、また試してみます。

明日は@kaigaiさんに代わり、@snagaさんが書かれるようです!お楽しみに!

31
13
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
31
13