明けましておめでとうございます。
サイオステクノロジー OSS サポート担当 Y です。
今年もよろしくお願い致します。
今回は、PostgreSQL 11 にて postgres_fdw (外部テーブル) 及び宣言的パーティショニングを利用したパーティションテーブルを作成してみました。(※以下の内容は CentOS 7.6/PostgreSQL 10.6/PostgreSQL 11.1 にて検証しています。)
■はじめに
一昨日リリースされた PostgreSQL 10 の新機能である宣言的パーティショニングでは、postgres_fdw を使った外部テーブルを子テーブルに設定することが可能です。
また、昨年リリースされた PostgreSQL 11 では、宣言的パーティショニングと postgres_fdw に関連する機能強化が実施されています。
今回は、PostgreSQL 11 で強化された部分を含めて、宣言的パーティショニングと postgres_fdw に関連する検証を実施してみました。
■環境構成
それではさっそく、検証してみます。
今回は以下の様な構成で、パーティションテーブル (RANGE パーティション) を作成します。
[サーバ構成]
----------------------------------------------------------------------
+--> [pgsql11_child1.example.com]
[pgsql11_parent.example.com] --(postgres_fdw)--+--> [pgsql11_child2.example.com]
+--> [pgsql11_child3.example.com]
----------------------------------------------------------------------
[テーブル構成]
----------------------------------------------------------------------
+--> [子テーブル (foreign_hoge_1)]
[親テーブル (foreign_hoge)] --(postgres_fdw)--+--> [子テーブル (foreign_hoge_2)]
+--> [子テーブル (foreign_hoge_3)]
----------------------------------------------------------------------
■子テーブルの準備
親テーブル (pgsql11_parent.example.com) 側では、postgres_fdw のインストール等の複数の作業が必要であるため、先に子テーブル側 (pgsql11_child*.example.com) で必要な作業を実施しておきます。
以下の様に、各ノードでテーブルを作成します。子テーブル側での作業は基本的にこれだけです。
[postgres@pgsql11_child1 ~]$ psql -c "CREATE TABLE hoge_1 (key int, data text)"
CREATE TABLE
[postgres@pgsql11_child1 ~]$
[postgres@pgsql11_child1 ~]$ psql -c "\d hoge_1"
Table "public.hoge_1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
key | integer | | |
data | text | | |
[postgres@pgsql11_child1 ~]$
[postgres@pgsql11_child2 ~]$ psql -c "CREATE TABLE hoge_2 (key int, data text)"
CREATE TABLE
[postgres@pgsql11_child2 ~]$
[postgres@pgsql11_child2 ~]$ psql -c "\d hoge_2"
Table "public.hoge_2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
key | integer | | |
data | text | | |
[postgres@pgsql11_child2 ~]$
[postgres@pgsql11_child3 ~]$ psql -c "CREATE TABLE hoge_3 (key int, data text)"
CREATE TABLE
[postgres@pgsql11_child3 ~]$
[postgres@pgsql11_child3 ~]$ psql -c "\d hoge_3"
Table "public.hoge_3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
key | integer | | |
data | text | | |
[postgres@pgsql11_child3 ~]$
■postgres_fdw の準備
親テーブルを作成する pgsql11_parent.example.com 上では、postgres_fdw を使った外部テーブルを作成する必要がありますが、postgres_fdw は contrib モジュールとして提供されているため、必要に応じてインストールを実施します。(今回はソースコードからインストールしています)
各 contrib モジュールのソースコードは、PostgreSQL の tar ボールを展開したディレクトリ配下の contrib/<モジュール名> というディレクトリに格納されているため、当該ディレクトリに移動し make 及び make install を実行します。(※make install はインストール先ディレクトリの権限等、必要に応じて root ユーザ権限で実行します。)
[postgres@pgsql11_parent postgres_fdw]$ pwd /usr/local/src/postgresql-11.1/contrib/postgres_fdw [postgres@pgsql11_parent postgres_fdw]$ [postgres@pgsql11_parent postgres_fdw]$ make make -C ../../src/backend generated-headers make[1]: Entering directory `/usr/local/src/postgresql-11.1/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/usr/local/src/postgresql-11.1/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/usr/local/src/postgresql-11.1/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/usr/local/src/postgresql-11.1/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/usr/local/src/postgresql-11.1/src/backend/utils' make[1]: Leaving directory `/usr/local/src/postgresql-11.1/src/backend' make -C ../../src/interfaces/libpq all make[1]: Entering directory `/usr/local/src/postgresql-11.1/src/interfaces/libpq' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/usr/local/src/postgresql-11.1/src/interfaces/libpq' gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I../../src/interfaces/libpq -I. -I. -I../../src/include -D_GNU_SOURCE -c -o postgres_fdw.o postgres_fdw.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I../../src/interfaces/libpq -I. -I. -I../../src/include -D_GNU_SOURCE -c -o option.o option.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I../../src/interfaces/libpq -I. -I. -I../../src/include -D_GNU_SOURCE -c -o deparse.o deparse.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I../../src/interfaces/libpq -I. -I. -I../../src/include -D_GNU_SOURCE -c -o connection.o connection.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I../../src/interfaces/libpq -I. -I. -I../../src/include -D_GNU_SOURCE -c -o shippable.o shippable.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o postgres_fdw.so postgres_fdw.o option.o deparse.o connection.o shippable.o -L../../src/port -L../../src/common -L../../src/interfaces/libpq -lpq -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags [postgres@pgsql11_parent postgres_fdw]$
[root@pgsql11_parent postgres_fdw]# pwd /usr/local/src/postgresql-11.1/contrib/postgres_fdw [root@pgsql11_parent postgres_fdw]# [root@pgsql11_parent postgres_fdw]# make install make -C ../../src/backend generated-headers make[1]: Entering directory `/usr/local/src/postgresql-11.1/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/usr/local/src/postgresql-11.1/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/usr/local/src/postgresql-11.1/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/usr/local/src/postgresql-11.1/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/usr/local/src/postgresql-11.1/src/backend/utils' make[1]: Leaving directory `/usr/local/src/postgresql-11.1/src/backend' make -C ../../src/interfaces/libpq all make[1]: Entering directory `/usr/local/src/postgresql-11.1/src/interfaces/libpq' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/usr/local/src/postgresql-11.1/src/interfaces/libpq' /usr/bin/mkdir -p '/usr/local/pgsql/lib' /usr/bin/mkdir -p '/usr/local/pgsql/share/extension' /usr/bin/mkdir -p '/usr/local/pgsql/share/extension' /usr/bin/install -c -m 755 postgres_fdw.so '/usr/local/pgsql/lib/postgres_fdw.so' /usr/bin/install -c -m 644 ./postgres_fdw.control '/usr/local/pgsql/share/extension/' /usr/bin/install -c -m 644 ./postgres_fdw--1.0.sql '/usr/local/pgsql/share/extension/' [root@pgsql11_parent postgres_fdw]#
make install が完了すると、
[postgres@pgsql11_parent ~]$ ls -l /usr/local/pgsql/lib/postgres_fdw.so -rwxr-xr-x 1 root root 111624 Dec 17 01:33 /usr/local/pgsql/lib/postgres_fdw.so
次に、psql で PostgreSQL に接続し CREATE EXTENSION コマンドを実行します。
[postgres@pgsql11_parent ~]$ psql -c "CREATE EXTENSION postgres_fdw" CREATE EXTENSION
CREATE EXTENSION が完了すると、以下の様に pg_extension に postgres_fdw の項目が出力されます。
[postgres@pgsql11_parent ~]$ psql -c "SELECT * FROM pg_extension" extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition --------------+----------+--------------+----------------+------------+-----------+-------------- plpgsql | 10 | 11 | f | 1.0 | | postgres_fdw | 10 | 2200 | t | 1.0 | | (2 rows)
postgres_fdw のインストールはこれで完了です。
■外部テーブルを利用したパーティションテーブル作成
それでは、外部テーブルの作成とそれらを利用したパーティションテーブルの作成を実施してみます。
まずは、親テーブルを作成するサーバ (pgsql11_parent.example.com) 上で外部サーバを定義します。(外部サーバの定義方法の詳細等については割愛します。)
[postgres@pgsql11_parent ~]$ psql -c "CREATE SERVER foreign1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pgsql11_child1.example.com', port '5432', dbname 'postgres')"
CREATE SERVER
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "CREATE SERVER foreign2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pgsql11_child2.example.com', port '5432', dbname 'postgres')"
CREATE SERVER
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "CREATE SERVER foreign3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pgsql11_child3.example.com', port '5432', dbname 'postgres')"
CREATE SERVER
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "SELECT * FROM pg_foreign_server"
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
----------+----------+--------+---------+------------+--------+-------------------------------------------------------------
foreign1 | 10 | 16387 | | | | {host=pgsql11_child1.example.com,port=5432,dbname=postgres}
foreign2 | 10 | 16387 | | | | {host=pgsql11_child2.example.com,port=5432,dbname=postgres}
foreign3 | 10 | 16387 | | | | {host=pgsql11_child3.example.com,port=5432,dbname=postgres}
(3 rows)
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "CREATE USER MAPPING FOR postgres SERVER foreign1 OPTIONS (user 'postgres', password '')"
CREATE USER MAPPING
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "CREATE USER MAPPING FOR postgres SERVER foreign2 OPTIONS (user 'postgres', password '')"
CREATE USER MAPPING
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "CREATE USER MAPPING FOR postgres SERVER foreign3 OPTIONS (user 'postgres', password '')"
CREATE USER MAPPING
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "SELECT * FROM pg_user_mappings"
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+----------+--------+----------+---------------------------
16391 | 16388 | foreign1 | 10 | postgres | {user=postgres,password=}
16392 | 16389 | foreign2 | 10 | postgres | {user=postgres,password=}
16393 | 16390 | foreign3 | 10 | postgres | {user=postgres,password=}
(3 rows)
上記の様に外部サーバを定義した後に、これらの外部サーバ上にあるテーブル (外部テーブル) を使ってパーティションテーブルを作成します。
まずは、親テーブルを作成します。
[postgres@pgsql11_parent ~]$ psql -c "CREATE TABLE foreign_hoge (key int, data text) PARTITION BY RANGE (key)"
CREATE TABLE
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "\d foreign_hoge"
Table "public.foreign_hoge"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
key | integer | | |
data | text | | |
Partition key: RANGE (key)
Number of partitions: 0
この時点では、まだ子テーブルが作成されていない親テーブルのみが存在しています。
次に、外部テーブルを利用して、上記親テーブルの子テーブルを作成します。
[postgres@pgsql11_parent ~]$ psql -c "CREATE FOREIGN TABLE foreign_hoge_1 PARTITION OF foreign_hoge FOR VALUES FROM (1) TO (4) SERVER foreign1 OPTIONS (schema_name 'public', table_name 'hoge_1')"
CREATE FOREIGN TABLE
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "CREATE FOREIGN TABLE foreign_hoge_2 PARTITION OF foreign_hoge FOR VALUES FROM (4) TO (7) SERVER foreign2 OPTIONS (schema_name 'public', table_name 'hoge_2')"
CREATE FOREIGN TABLE
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "CREATE FOREIGN TABLE foreign_hoge_3 PARTITION OF foreign_hoge FOR VALUES FROM (7) TO (10) SERVER foreign3 OPTIONS (schema_name 'public', table_name 'hoge_3')"
CREATE FOREIGN TABLE
[postgres@pgsql11_parent ~]$
[postgres@pgsql11_parent ~]$ psql -c "SELECT s.srvname, c.relname, t.* FROM pg_foreign_table AS t LEFT OUTER JOIN pg_foreign_server AS s ON s.oid = t.ftserver LEFT OUTER JOIN pg_class AS c ON c.oid = t.ftrelid"
srvname | relname | ftrelid | ftserver | ftoptions
----------+----------------+---------+----------+----------------------------------------
foreign1 | foreign_hoge_1 | 16421 | 16388 | {schema_name=public,table_name=hoge_1}
foreign2 | foreign_hoge_2 | 16424 | 16389 | {schema_name=public,table_name=hoge_2}
foreign3 | foreign_hoge_3 | 16427 | 16390 | {schema_name=public,table_name=hoge_3}
(3 rows)
\d+ コマンドにて親テーブルである foreign_hoge の詳細を確認すると、子テーブルが作成されていることが確認できます。
[postgres@pgsql11_parent ~]$ psql -c "\d+ foreign_hoge"
Table "public.foreign_hoge"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
key | integer | | | | plain | |
data | text | | | | extended | |
Partition key: RANGE (key)
Partitions: foreign_hoge_1 FOR VALUES FROM (1) TO (4),
foreign_hoge_2 FOR VALUES FROM (4) TO (7),
foreign_hoge_3 FOR VALUES FROM (7) TO (10)
■テストデータの格納及び参照
前述した方法で作成したパーティションテーブルにデータを INSERT してみます。
[postgres@pgsql11_parent ~]$ psql -c "INSERT INTO foreign_hoge SELECT a, md5(clock_timestamp()::text) FROM generate_series(1,9) AS a" INSERT 0 9 [postgres@pgsql11_parent ~]$ [postgres@pgsql11_parent ~]$ psql -c "SELECT * FROM foreign_hoge" key | data -----+---------------------------------- 1 | 6159f424bfcdb621ae7b8b670ac22281 2 | dbf091816d5bfe2e3e421f84758a6963 3 | dd0ad6f2be346add7cf72688f2159588 4 | fcb4aaa69fe9066149608f66bcc21a83 5 | 303f721269714832ea3b850847f2d3ee 6 | 72c439e4fe648e0158ce26b40eb615dd 7 | b18c88342998ab9d515196ef7738aba2 8 | 859d3369d0a31bcd6097227b7d5b9a08 9 | 9c90ec5afa8409be8546099096c3ecff (9 rows)
上記の様に、親テーブルにデータを INSERT することができました。
また、各子テーブル (外部テーブル) にそれぞれデータが振り分けられていることも確認できます。
[postgres@pgsql11_parent ~]$ psql -c "SELECT * FROM foreign_hoge_1" key | data -----+---------------------------------- 1 | 6159f424bfcdb621ae7b8b670ac22281 2 | dbf091816d5bfe2e3e421f84758a6963 3 | dd0ad6f2be346add7cf72688f2159588 (3 rows) [postgres@pgsql11_parent ~]$ [postgres@pgsql11_parent ~]$ psql -c "SELECT * FROM foreign_hoge_2" key | data -----+---------------------------------- 4 | fcb4aaa69fe9066149608f66bcc21a83 5 | 303f721269714832ea3b850847f2d3ee 6 | 72c439e4fe648e0158ce26b40eb615dd (3 rows) [postgres@pgsql11_parent ~]$ [postgres@pgsql11_parent ~]$ psql -c "SELECT * FROM foreign_hoge_3" key | data -----+---------------------------------- 7 | b18c88342998ab9d515196ef7738aba2 8 | 859d3369d0a31bcd6097227b7d5b9a08 9 | 9c90ec5afa8409be8546099096c3ecff (3 rows)
ちなみに、PostgreSQL 10 で同じ構成のパーティションテーブルを作成し、INSERT を実行すると以下の様なエラーになってしまいます。
[postgres@pgsql10_parent ~]$ psql -c "INSERT INTO foreign_hoge SELECT a, md5(clock_timestamp()::text) FROM generate_series(1,9) AS a" ERROR: cannot route inserted tuples to a foreign table
PostgreSQL 10 では、上記の様に外部テーブルを利用した子テーブルに対してレコードをルーティングできない (親テーブルに INSERT できない) という制約がありましたが、PostgreSQL 11 では外部テーブルを利用した子テーブルに対してレコードをルーティングできる (親テーブルに INSERT できる) ように機能強化されています。
■最後に
今回は、PostgreSQL 11 にて外部テーブルを利用したパーティションテーブルを作成してみました。
PostgreSQL 10 で追加された機能である宣言的パーティショニングには制約事項が多かったのですが、PostgreSQL 11 では以前紹介したデフォルトパーティションや Hash パーティショニングの様な機能強化に加え、外部テーブルを利用したパーティショニングの機能も強化されています。
次回は、外部テーブルを利用したパーティションテーブルと、ローカルで作成した (外部テーブルを利用しない) パーティションテーブルの動作を検証してみようと思います。
