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

本記事は [PostgreSQL Advent Calendar 2018](https://qiita.com/advent-calendar/2018/postgresql)の19日目です。

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

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

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

#今回確認した新機能
本記事で紹介するのは以下の機能です。

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

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

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

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

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

1. pg_stat_replicationにStandbyサーバでWALを最後に適用した時間を表示
   https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7fee252f6fbf78ca5e50ee591573d59f98e75d37

#確認結果

・・・に入る前にまずは、PG12環境ですが、Gitからソースを取得し、ビルドすればOKです。
参考にコマンドを載せていますが、適当なオプションでやっていますので必要に応じて、オプションは適宜指定してください。

```console
$ 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です。

```console
$ psql postgres
psql (12devel)
Type "help" for help.
```

##1. recovery.confのpostgresql.confへの統合

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2dedf4d9a899b36d1a8ed29be5efbd1b31a8fe85


そのままですが、これまで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を使用して構築します。

```console
$ 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サーバとして起動可能です！

>```console
$ 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サーバを起動します。

```console
$ 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サーバでレコードを追加して、レプリケーションされているのか確認しておきます。

```console
$ 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()の追加

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=10074651e3355e2405015f6253602be8344bc829

これまでは、Standbyサーバで、`$ pg_ctl promote`を実行する必要がありましたが、PG12からはSQL関数で可能になります。

Standbyサーバに対して、以下のSQLを実行するだけでPromoteできます。

```console
$ psql postgres -p 5433 -U kazu -c "select pg_promote()"
 pg_promote 
------------
 t
(1 row)
```

関数実行後、StandbyサーバがPrimaryサーバに昇格し稼働していることが確認できます。

```console
[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表示

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=aa2ba50c2c13dd35a95a91638aa0a2b88c6df1eb

\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()の追加

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d5eec4eefde70414c9929b32c411cb4f0900a2a9

PG10から宣言的パーティショニングの実装により、パーティショニングは非常に使いやすくなったと思います。
PG12では、パーティショニングが複雑になった場合にも備えて、パーティショニングツリーを可視化できる機能が追加されています。

挙動の確認のため、以下のような構成でパーティショニングテーブルを作成します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/125929/e0017584-7315-4dd0-fb25-b75395162ffc.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でのパーティショニング機能の進化（の細かいところ）」](https://qiita.com/U_ikki/items/b4e0fe78ae9a8b064466)というタイトルで記事を書いていたのですが、全ての子テーブルに対してロックが取得される挙動は現時点のPG12でもまだ変わっていませんでした。きっとこれから変わるんだと思います。
変更を確認できたら、記事を更新しておきます。

##5. 新規パラメータ追加

- log_statement_sample_rate
  - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=88bdbd3f746049834ae3cc972e6e650586ec3c9d

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

- Add timestamp of last received message from standby to pg_stat_replication
    - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7fee252f6fbf78ca5e50ee591573d59f98e75d37

タイトルのとおりですが、pg_stat_replicationに新しい項目として「reply_time」が追加されました。
この項目では、StandbyサーバからPrimaryサーバにreplyしたというメッセージを受け取った最後の時間を表示しています。

```console
$ 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さんが書かれるようです！お楽しみに！