【PostgreSQL 11】外部テーブル (postgres_fdw) を利用したパーティションテーブルを作成してみた (その1)

明けましておめでとうございます。
サイオステクノロジー 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 が完了すると、/lib/ 配下に postgres_fdw.so というファイルが格納されているはずです。

[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 パーティショニングの様な機能強化に加え、外部テーブルを利用したパーティショニングの機能も強化されています。

次回は、外部テーブルを利用したパーティションテーブルと、ローカルで作成した (外部テーブルを利用しない) パーティションテーブルの動作を検証してみようと思います。

ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

役に立った 役に立たなかった

0人がこの投稿は役に立ったと言っています。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です