PostgreSQL 9.x 連到 Greenplum

Greenplum 基於 PostgreSQL 8.x，照理說要很像 PostgreSQL；那麼兩類資料庫要跨接時，能怎麼做？目前我找到的方式，有使用 DBLink，或是用 postgres_fdw 這個 Foreign Data Wrapper 套件。

使用 DBLink 連到 Greenplum，找到的都是實驗性質的使用。所以在此我用 Foreign Data Wrapper 接到 Greenplum。

Foreign Data Wrapper 是一個 PostgreSQL 的擴充套件（Extension），所以要使用  Foreign Data Wrapper，要先在所連線的資料庫裡面建立 Extension。例如，以下在 postgres 下為例：

postgres=# CREATE EXTENSION postgres_fdw;

若想要之後每次都產生 database 時，都自動加上這個套件的話，則將該套件建立到 template1：

[postgres ~]$ psql -d template1 -c 'create extension postgres_fdw;'

接著便開始要進行設定。

連線設定，要先使用 CREATE SERVER 指令，使用連線字串設定方式設定連線到 Greenplum 的資訊。在此 Greenplum 的 入口 IP （Master Instance）為 192.168.238.152，port 為 5432：

postgres=# CREATE SERVER gp_db
postgres-# FOREIGN DATA WRAPPER postgres_fdw
postgres-# OPTIONS (host '192.168.238.152', port '5432', dbname 'fdwtest');
CREATE SERVER

接著設定登入帳密：值得注意的是，一般帳密都是設在連線字串裡面，不過 Foreign Data Wrapper 則是另外用 CREATE USER MAPPING 指令設定的：

postgres=# CREATE USER MAPPING FOR user4gpfdw SERVER gp_db
postgres-# OPTIONS (user 'fdwuser',password 'fdwuser');
CREATE USER MAPPING

到目前為止，Foreign Data Wrapper 的資訊已經建立完畢了。

再來便是要在 Greenplum 上面建立一個可以連線的測試資料表格。
在 Greenplum 上：

gpadmin=# \c fdwtest brandon
You are now connected to database "test" as user "brandon".
fdwtest=# SET search_path TO testing;
SET
fdwtest=# CREATE TABLE productest
fdwtest-# (name VARCHAR(40), prod_id INTEGER)
fdwtest-# DISTRIBUTED BY (prod_id);
CREATE TABLE

接著，回到 PostgreSQL 上，建立一個上面已經存在於 Greenplum 上的資料表：

postgres=# CREATE FOREIGN TABLE foreigntab(
postgres(# name TEXT,
postgres(# id INTEGER)
postgres-# SERVER gp_db
postgres-# OPTIONS (schema_name 'testing', table_name 'productest');
CREATE FOREIGN TABLE

可以在 Greenplum 上檢查一下連線資訊：

gpadmin=# SELECT datname,usename,client_addr,application_name
gpadmin=#  FROM pg_stat_activity;
  datname | usename |  client_addr   | application_name
 ---------+---------+----------------+------------------
  gpadmin | brandon |                | psql
  fdwtest | fdwuser | 192.168.62.178 | postgres_fdw
  fdwtest | brandon | 192.168.62.178 | psql
(3 rows)

以上可見 postgres_fdw 連線進入的資訊，表示 postgres_fdw 有成功連線到 Greenplum 。

使用會發現，要直接輸入資料，會出現下列錯誤：

Greenplum Database does not support REPEATABLE READ transactions

所以要指定 transaction level，再輸入資料，才能避免上述錯誤：

postgres=# START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION
Time: 0.331 ms

postgres*=# INSERT INTO foreigntab VALUES ('rastberry',101);
INSERT 0 1

Time: 220.690 ms
postgres*=# -- 這時資料輸入進去了。 
postgres*=# -- 從 PostgreSQL裡面檢查一下資料
postgres*=# SELECT * FROM foreigntab;
    name   | prod_id 
-----------+---------
 grava     |     100
 pen       |       1
 rastberry |     101
 pencil    |       2
(4 rows)

Time: 169.112 ms
postgres*=# END;
COMMIT

Time: 110.171 ms

要透過 postgres_fdw 存取 Greenplum 上的資料，都需要向上面所示，使用 SERIALIZABLE 的交易等級才行。

現在到 Greenplum 上直接檢查，也會看到剛剛從 PostgreSQL 輸入進來的資料：

gpadmin=# SELECT * FROM productest;
    name   | prod_id 
-----------+---------
 pen       |       1
 pencil    |       2
 grava     |     100
 rastberry |     101
(4 rows)

[Update 20190325] 如果要用 IMPORT FOREIGN SCHEMA 簡化匯入外部表的方式，也需要指定 isolation level。跟上面一樣，CREATE SERVER 以及 CREATE USER MAPPING 不用包在這個 Transaction 裡面（這邊延續上面的環境～）

postgres=# CREATE SCHEMA gptables;
CREATE SCHEMA
postgres=# BEGIN;
BEGIN
postgres=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
postgres=# IMPORT FOREIGN SCHEMA testing LIMIT TO (productest) FROM SERVER gp_db INTO gptables;
IMPORT FOREIGN SCHEMA
postgres=# COMMIT;
COMMIT
postgres=# 

參考資料：

這是一個 postgres_fdw 的操作介紹
A look at Foreign Data Wrappers Craig Kerstiens 
以下是此次實作中，用到的 Greenplum 資料
What are the Table Distribution Policy in Greenplum - A Web Portal for Greenplum Database Professionals!
SET TRANSACTION | Pivotal Greenplum Database Docs