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

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください
【3/22開催】テックブログを書こう!アウトプットのススメ
1年で100本ブログを出した新米エンジニアがPV数が伸びなくてもTech Blogを書き続ける理由とは?
https://tech-lab.connpass.com/event/312805/

【4/18開催】VSCode Dev Containersで楽々開発環境構築祭り〜Python/Reactなどなど〜
Visual Studio Codeの拡張機能であるDev Containersを使ってReactとかPythonとかSpring Bootとかの開発環境をラクチンで構築する方法を紹介するイベントです。
https://tech-lab.connpass.com/event/311864/

明けましておめでとうございます。
サイオステクノロジー 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 パーティショニングの様な機能強化に加え、外部テーブルを利用したパーティショニングの機能も強化されています。

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

アバター画像
About サイオステクノロジーの中の人 41 Articles
サイオステクノロジーで働く中の人です。
ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

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

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


ご覧いただきありがとうございます。
ブログの最新情報はSNSでも発信しております。
ぜひTwitterのフォロー&Facebookページにいいねをお願い致します!



>> 雑誌等の執筆依頼を受付しております。
   ご希望の方はお気軽にお問い合わせください!

Be the first to comment

Leave a Reply

Your email address will not be published.


*


質問はこちら 閉じる