データベースを跨ぐDML

postgres_fdw

データ処理の過程で、データが他のデータベース(スキーマ)にある場合で同じインスタンスにある場合に、テーブルのデータを毎回EXPORTしてインポートしたりするのは手間になります。そんな場合に、MySQLとPostgreSQLではどの様にデータベースを跨ぐ処理を実行するか参考の為にメモしておきます。

MySQL

MySQLの場合は、データベースを跨ぐDMLの場合はシンプルに以下の様にデータベース名.テーブル名を指定すればOKなので非常にシンプルに扱う事が出来る。

root@localhost [POC]>  CREATE TABLE `t1` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `note` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.34 sec)


root@localhost [POC]> insert into t1(note) values('This is cross schema check');
Query OK, 1 row affected (0.01 sec)

root@localhost [POC]> insert into t1(note) values('This is cross schema check');
Query OK, 1 row affected (0.00 sec)

root@localhost [POC]> insert into t1(note) values('This is cross schema check');
Query OK, 1 row affected (0.01 sec)

root@localhost [POC]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [mysql]> select * from POC.t1;
+----+----------------------------+
| id | note                       |
+----+----------------------------+
|  1 | This is cross schema check |
|  2 | This is cross schema check |
|  3 | This is cross schema check |
+----+----------------------------+
3 rows in set (0.00 sec)

root@localhost [mysql]> 
explain on remote table MySQL

PostgreSQL

PostgreSQLの場合は、他のデータベース(スキーマ)から他のデータベースのテーブルをそのまま参照する事は出来ないので、以下の様にpostgres_fdw等を利用して設定してあげる必要がある。ここは、MySQLやMS SQLを使い慣れているユーザーには不便に感じる事もあるかと思います。

postgres_fdwモジュールは、外部のPostgreSQLサーバに格納されたデータをアクセスするために使用する、postgres_fdw外部データラッパを提供します。

~/git/rdbms-docker/postgresql [ 9:40:19]> psql -h 127.0.0.1 -p 5432 -U postgres
Password for user postgres:
psql (14.1 (Ubuntu 14.1-1.pgdg18.04+1))
Type "help" for help.

postgres=# \l+
                                                               List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 POC       | postgres | UTF8     | C       | C     |                       | 8681 kB | pg_default |
 postgres  | postgres | UTF8     | C       | C     |                       | 8553 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +| 8401 kB | pg_default | unmodifiable empty database
           |          |          |         |       | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +| 8401 kB | pg_default | default template for new databases
           |          |          |         |       | postgres=CTc/postgres |         |            |
(4 rows)

postgres=# create database OTHER owner 'postgres' encoding 'UTF8' lc_collate 'C' lc_ctype 'C' template 'template0';
CREATE DATABASE
postgres=# \l+
                                                               List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 POC       | postgres | UTF8     | C       | C     |                       | 8681 kB | pg_default |
 other     | postgres | UTF8     | C       | C     |                       | 8401 kB | pg_default |
 postgres  | postgres | UTF8     | C       | C     |                       | 8553 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +| 8401 kB | pg_default | unmodifiable empty database
           |          |          |         |       | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +| 8401 kB | pg_default | default template for new databases
           |          |          |         |       | postgres=CTc/postgres |         |            |
(5 rows)

postgres=# \c POC
You are now connected to database "POC" as user "postgres".
POC=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | t1   | table | postgres
 public | test | table | postgres
(2 rows)

POC=# \c other
You are now connected to database "other" as user "postgres".
other=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
other=# CREATE SERVER link_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost',port '5432',dbname 'POC');
CREATE SERVER

other=# create user mapping for postgres server link_server options (user 'postgres', password 'password');
CREATE USER MAPPING

other=# import foreign schema public limit to (t1) from server link_server into public;
IMPORT FOREIGN SCHEMA

other=# \d
            List of relations
 Schema | Name |     Type      |  Owner
--------+------+---------------+----------
 public | t1   | foreign table | postgres
(1 row)

other=# select * from t1;
 id |            note
----+-----------------------------
  1 | This is Sequence Lock Check
  2 | This is Sequence Lock Check
  3 | This is Sequence Lock Check
  4 | This is Sequence Lock Check
  5 | This is Sequence Lock Check
  6 | This is Sequence Lock Check
  7 | This is Sequence Lock Check
  8 | This is Sequence Lock Check
  9 | This is Sequence Lock Check
 10 | This is Sequence Lock Check
(10 rows)

other=#

参照:F.33. postgres_fdw

explain on remote table

留意:ユーザー権限、統計情報、トランザクション等は別途適切に設定して下さい。

カテゴリー:

最近のコメント

表示できるコメントはありません。