f 六月 2018 ~ 迪貝之家

Microsoft SQL Server Log Shipping

SQL 7就有的技術,簡單的說就是資料庫複本資料同步的技術,而這份複本可以開放讀取的功能以分散營業資料庫的負載,也可純粹當成備援資料庫,不管是在本地還是異地。怎麼達成? 透過SQL Agent自動化排程進行備份、傳檔及復原等作業。

Oracle GolGateden

機房搬遷跨Site能即時同步資料庫利器

Nagios 資料庫維運自動化

一開始設計這架構時,就排除使用remote agent的想法因為在專業分工的組織下,要求安裝新軟體於既有系統是一件不太可行的方案,既然身為DBA就只能把資料庫instance當作是一個最大的agent 想辦法在資料庫內做到我想做的事情

This is default featured post 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured post 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

pgbench and dbt-3 test on PGXL Cluster

pgbench 的測試劇本沒辦法呈現XL優勢,底下是pgbench 預設的交易語法
BEGIN;

UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES

(:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

END;
從語法中看到where condition 都透過aid 在處理資料,這是一個Primary Key 的欄位,所以每次處理資料就是單筆,但在XL的架構下,並不是每筆資料都是在local端進行處理,與Oracle RAC技術不同點是在於,XL會把sql語句拋到remote node執行後彙整結果,所以會有remote query的overhead(這可以從系統的log看得到remote query 的進行) ,因此不能使用pgbench 的預設劇本來進行測試比較,我找了一個叫做DBT3  的Open Source工具來進行PG與PGXL的壓力測試

DBT3 是遵循TPCH的規格開發的,已經在Postgres 10.4測過是可用的,但在XL 10Alpha2 下,好像有點問題,因為測試會停在第九個查詢語法,從datanode 的log來看,這段記錄會一直被拋出來execute p_1_5cb6_2/p_1_5cb6_2: Remote Subplan,不確定問題發生在哪裡??進行DBT-3  TPC-H benchmark XL95 及 Single 95 測試比較後,XL 有兩個缺點 :


  • 複雜查詢效能比不上單一instance,如subqery,越簡單的sql查詢,它的效能會顯示出來。
  • 當它沒辦法解析語法時,也就是不知道怎麼執行你丟給它的sql 語法時整個cluster 有可能當掉。


我認為XL是個未成熟,甚至可以說,它還是個實驗中產品,上述的第二點是個很大的致命傷,我不建議使用它,因為風險太高。如果要找MPP解決方案 ,應該花時間在GREENPLUM的研究。

Postgres-XL 建置測試

1.     硬體環境配置如下:
pgxl1(172.20.2.18) : 4 * datanode4 * gtm proxy
pgxl2(172.20.2.19) : 4 * datanode4 * gtm proxy
pgxl3(172.20.2.20) : gtm 1 * coordinator
pgxl4(172.20.2.21) : DBT-3 測試工具

架設好之後的確認 :

各服務的狀態 











從兩台coordinator 服務連進系統



這個Solution 有一個中控程式,叫做pgxcl_ctl,透過它,可以建置及管理Cluster 的所有元件;當然你也可以一個一個元件慢慢佈署及啟動,因為它基本上就是postgres source code,可以依照postgres 的安裝,然後initial 各元件資料,最後再啟動各元件,不過我想應該沒人想這麼麻煩,除非是為了更深入了解才進行這種建置測試。底下這是我的建置步驟, 怎麼進行source code編譯,我在之前的週報就提過,我就不再贅述,置於部署binary,我是透過scp 拷貝到其它的主機,pgxc_ctl 似乎有部署的功能,這部份我會找時間進行測試,底下就是我進行的步驟 :


1.  文件提到的前置作業
I : host equivalence
Password-less ssh access is required from the node that is going to run the pgxc_ctl utility.

II : 這我就不得不提一下,要把/usr/local/pgsql/bin放到/etc/environment放到.bash_profile 沒有用,在透過pgxc_ctl 進行建置的時候,會出現找不到指令的錯誤,原因待查。
The PATH environment variable should have the correct Postgres-XL binaries on all nodes, especially while running a command via ssh.

III : 因為coordinatordatanode 本身就是postgres db instance所以產出空的pgxc_ctl 指令的control file (pgxc_ctl.conf後),要把服務所在的網段加到參數裡

coordPgHbaEntries=(172.20.2.0/24)
datanodePgHbaEntries=(172.20.2.0/24)
     
The pg_hba.conf entries must be updated to allow remote access. Variables like coordPgHbaEntries and datanodePgHbaEntries in the pgxc_ctl.conf configuration file may need appropriate changes.

IV : 注意port 的通透
Firewalls and iptables may need to be updated to allow access to ports.

2.  系統架構環境
gtm  : pgxl3
gtm slave : pgxl4
gtm proxy, co, dn : pgxl1, pgxl2

3.  bash 環境變數
  export  PGHOME=/usr/local/pgsql
  export  PGUSER=postgres
  export  LD_LIBRARY_PATH=$PGHOME/lib
  export  PATH=$PGHOME/bin:$PATH

4.  建立空的pgxc_ctl.conf

$ mkdir  $HOME/DATA/pgxl/nodes
$ export dataDirRoot=$HOME/DATA/pgxl/nodes
$ mkdir $HOME/pgxc_ctl
$ pgxc_ctl
PGXC$  prepare config empty
PGXC$  exit
請依照第一點的前置作業的III 步驟進行

5.新增及啟動Global Transaction Manager (GTM)
$ pgxc_ctl
PGXC$  add gtm master gtm pgxl3 20001 $dataDirRoot/gtm

6.新增及啟動coordinator
PGXC$ add coordinator master coord1 pgxl1 5432 30001 $dataDirRoot/coord_master.1 none none
PGXC$ add coordinator master coord2 pgxl2 5432 30002 $dataDirRoot/coord_master.2 none none

7. 新增及啟動 datanode
PGXC$  add datanode master dn1  pgxl1 15432 40001 $dataDirRoot/dn_master.1 none none none
PGXC$  add datanode master dn2  pgxl2 15432 40002 $dataDirRoot/dn_master.2 none none none

8. 新增及啟動gtm proxy
PGXC$ add gtm_proxy proxy1 pgxl1 50001 $dataDirRoot/proxy1
PGXC$ add gtm_proxy proxy2 pgxl2 50002 $dataDirRoot/proxy2

9. 新增及啟動gtm slave
PGXC$ add gtm slave gtm_slave pgxl4 20002 $dataDirRoot/gtm_slave

10. 查看所有元件狀態
PGXC$ monitor all

底下是我建置及啟動Cluster 的畫面,上述只是為了方便查看需要執行的指令 :

產出預設pgxc_ctl.conf設定檔
postgres@pgxl3:~/pgxc_ctl$ pgxc_ctl
/bin/bash
Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash.
Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash.
Reading configuration using /home/postgres/pgxc_ctl/pgxc_ctl_bash --home /home/postgres/pgxc_ctl --configuration /home/postgres/pgxc_ctl/pgxc_ctl.conf                                                                    ostgres/pgxc_ctl --configuration /home/postgres/pgxc_ctl/pgxc_ctl.conf
Finished reading configuration.
   ******** PGXC_CTL START ***************

Current directory: /home/postgres/pgxc_ctl

新增Global Transaction Monitor 服務 (GTM)
postgres@pgxl3:~/pgxc_ctl$ pgxc add gtm master gtm pgxl3 20001 $dataDirRoot/gtm
Initialize GTM master
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.


fixing permissions on existing directory /home/postgres/DATA/pgxl/nodes/gtm...                                                                     ok
creating configuration files ... ok
creating control file ... ok

Success.
Done.
Start GTM master
waiting for server to shut down.... done
server stopped
server starting

--- 新增 第一個 AP 存取介面服務 (coordinator )
postgres@pgxl3:~/pgxc_ctl$ pgxc add coordinator master coord1 pgxl1 5432 30001 $dataDirRoot/coord_master.1 none none
Actual Command: ssh postgres@pgxl1 "( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/coord_master.1 --nodename coord1 ) > /tmp/pgxl3_STDOUT_21232_9 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl1:/tmp/pgxl3_STDOUT_21232_9 /tmp/STDOUT_21232_10 > /dev/null 2>&1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: lzh_TW
  NUMERIC:  lzh_TW
  TIME:     lzh_TW
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/postgres/DATA/pgxl/nodes/coord_master.1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB




selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success.
Starting coordinator master coord1
2018-05-07 16:15:11.356 CST [62168] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-05-07 16:15:11.356 CST [62168] LOG:  listening on IPv6 address "::", port 5432
2018-05-07 16:15:11.374 CST [62168] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-05-07 16:15:11.398 CST [62168] LOG:  redirecting log output to logging collector process
2018-05-07 16:15:11.398 CST [62168] HINT:  Future log output will appear in directory "pg_log".
Done.
ALTER NODE
 pgxc_pool_reload
------------------
 t
(1 row)




--- 新增 第二個 AP 存取介面服務 (coordinator )
postgres@pgxl3:~/pgxc_ctl$ pgxc   add coordinator master coord2 pgxl2 5432 30002 $dataDirRoot/coord_master.2 none none
Actual Command: ssh postgres@pgxl2 "( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/coord_master.2 --nodename coord2 ) > /tmp/pgxl3_STDOUT_21232_13 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl2:/tmp/pgxl3_STDOUT_21232_13 /tmp/STDOUT_21232_14 > /dev/null 2>&1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.




The database cluster will be initialized with locales
  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: lzh_TW
  NUMERIC:  lzh_TW
  TIME:     lzh_TW
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/postgres/DATA/pgxl/nodes/coord_master.2 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success.
INFO:  please do not close this session until you are done adding the new node
 pgxc_lock_for_backup
----------------------
 t
(1 row)

Actual Command: ssh postgres@pgxl2 "( pg_ctl start -w -Z restoremode -D $dataDirRoot/coord_master.2 -o -i ) > /tmp/pgxl3_STDOUT_21232_16 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl2:/tmp/pgxl3_STDOUT_21232_16 /tmp/STDOUT_21232_17 > /dev/null 2>&1

2018-05-07 16:18:48.582 CST [39065] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-05-07 16:18:48.582 CST [39065] LOG:  listening on IPv6 address "::", port 5432
2018-05-07 16:18:48.592 CST [39065] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-05-07 16:18:48.623 CST [39065] LOG:  redirecting log output to logging collector process
2018-05-07 16:18:48.623 CST [39065] HINT:  Future log output will appear in directory "pg_log".
SET
SET
SET
psql:/tmp/GENERAL_21232_15:14: ERROR:  role "postgres" already exists
ALTER ROLE
REVOKE
GRANT
CREATE NODE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
 



Actual Command: ssh postgres@pgxl2 "( pg_ctl stop -w -Z restoremode -D $dataDirRoot/coord_master.2 ) > /tmp/pgxl3_STDOUT_21232_18 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl2:/tmp/pgxl3_STDOUT_21232_18 /tmp/STDOUT_21232_19 > /dev/null 2>&1
Starting coordinator master coord2
2018-05-07 16:18:52.918 CST [39294] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-05-07 16:18:52.918 CST [39294] LOG:  listening on IPv6 address "::", port 5432
2018-05-07 16:18:52.935 CST [39294] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-05-07 16:18:52.964 CST [39294] LOG:  redirecting log output to logging collector process
2018-05-07 16:18:52.964 CST [39294] HINT:  Future log output will appear in directory "pg_log".
Done.
CREATE NODE
 pgxc_pool_reload
------------------
 t
(1 row)

ALTER NODE
 pgxc_pool_reload
------------------
 t
(1 row)

新增第一個資料儲存服務 ( datanode )
postgres@pgxl3:~/pgxc_ctl$ pgxc add datanode master dn1  pgxl1 15432 40001 $dataDirRoot/dn_master.1 none none none
Actual Command: ssh postgres@pgxl1 "( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/dn_master.1   --nodename dn1 ) > /tmp/pgxl3_STDOUT_21232_22 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl1:/tmp/pgxl3_STDOUT_21232_22 /tmp/STDOUT_21232_23 > /dev/null 2>&1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8

  MONETARY: lzh_TW
  NUMERIC:  lzh_TW
  TIME:     lzh_TW
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/postgres/DATA/pgxl/nodes/dn_master.1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success.
INFO:  please do not close this session until you are done adding the new node
 pgxc_lock_for_backup
----------------------
 t
(1 row)

Actual Command: ssh postgres@pgxl1 "( pg_ctl start -w -Z restoremode -D $dataDirRoot/dn_master.1 -o -i ) > /tmp/pgxl3_STDOUT_21232_25 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl1:/tmp/pgxl3_STDOUT_21232_25 /tmp/STDOUT_21232_26 > /dev/null 2>&1
2018-05-07 16:20:05.109 CST [62516] LOG:  listening on IPv4 address "0.0.0.0", port 15432
2018-05-07 16:20:05.109 CST [62516] LOG:  listening on IPv6 address "::", port 15432
2018-05-07 16:20:05.126 CST [62516] LOG:  listening on Unix socket 

"/tmp/.s.PGSQL.15432"
2018-05-07 16:20:05.150 CST [62516] LOG:  redirecting log output to logging collector process
2018-05-07 16:20:05.150 CST [62516] HINT:  Future log output will appear in directory "pg_log".
SET
SET
SET
psql:/tmp/GENERAL_21232_24:14: ERROR:  role "postgres" already exists
ALTER ROLE
REVOKE
GRANT
CREATE NODE
CREATE NODE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
Actual Command: ssh postgres@pgxl1 "( pg_ctl stop -w -Z restoremode -D $dataDirRoot/dn_master.1 ) > /tmp/pgxl3_STDOUT_21232_27 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl1:/tmp/pgxl3_STDOUT_21232_27/tmp/STDOUT_21232_28 > /dev/null 2>&1
Starting datanode master dn1.
2018-05-07 16:20:08.848 CST [62710] LOG:  listening on IPv4 address "0.0.0.0", port 15432
2018-05-07 16:20:08.848 CST [62710] LOG:  listening on IPv6 address "::", port 15432
2018-05-07 16:20:08.865 CST [62710] LOG:  listening on Unix socket "/tmp/.s.PGSQL.15432"
2018-05-07 16:20:08.890 CST [62710] LOG:  redirecting log output to logging collector process
2018-05-07 16:20:08.890 CST [62710] HINT:  Future log output will appear in directory "pg_log".
Done.
CREATE NODE
 pgxc_pool_reload
------------------
 t
(1 row)

CREATE NODE
 pgxc_pool_reload
------------------
 t
(1 row)

EXECUTE DIRECT
 pgxc_pool_reload
------------------
 t
(1 row)


新增第二個資料儲存服務 ( datanode )
postgres@pgxl3:~/pgxc_ctl$ pgxc   add datanode master dn2 pgxl2 15432 40002 $dataDirRoot/dn_master.2 none none none
Actual Command: ssh postgres@pgxl2 "( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/dn_master.2   --nodename dn2 ) > /tmp/pgxl3_STDOUT_21232_31 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl2:/tmp/pgxl3_STDOUT_21232_31 /tmp/STDOUT_21232_32 > /dev/null 2>&1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales

  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: lzh_TW
  NUMERIC:  lzh_TW
  TIME:     lzh_TW
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/postgres/DATA/pgxl/nodes/dn_master.2 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success.
INFO:  please do not close this session until you are done adding the new node
 pgxc_lock_for_backup
----------------------
 t
(1 row)

Actual Command: ssh postgres@pgxl2 "( pg_ctl start -w -Z restoremode -D $dataDirRoot/dn_master.2 -o -i ) > /tmp/pgxl3_STDOUT_21232_34 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl2:/tmp/pgxl3_STDOUT_21232_34 /tmp/STDOUT_21232_35 > /dev/null 2>&1
2018-05-07 16:21:19.202 CST [39638] LOG:  listening on IPv4 address "0.0.0.0", port 15432

2018-05-07 16:21:19.202 CST [39638] LOG:  listening on IPv6 address "::", port 15432
2018-05-07 16:21:19.219 CST [39638] LOG:  listening on Unix socket "/tmp/.s.PGSQL.15432"
2018-05-07 16:21:19.246 CST [39638] LOG:  redirecting log output to logging collector process
2018-05-07 16:21:19.246 CST [39638] HINT:  Future log output will appear in directory "pg_log".
SET
SET
SET
psql:/tmp/GENERAL_21232_33:14: ERROR:  role "postgres" already exists
ALTER ROLE
REVOKE
GRANT
CREATE NODE
CREATE NODE
CREATE NODE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
Actual Command: ssh postgres@pgxl2 "( pg_ctl stop -w -Z restoremode -D $dataDirRoot/dn_master.2 ) > /tmp/pgxl3_STDOUT_21232_36 2>&1" < /dev/null > /dev/null 2>&1
Bring remote stdout: scp postgres@pgxl2:/tmp/pgxl3_STDOUT_21232_36 /tmp/STDOUT_21232_37 > /dev/null 2>&1
Starting datanode master dn2.
2018-05-07 16:21:23.339 CST [39835] LOG:  listening on IPv4 address "0.0.0.0", port 15432
2018-05-07 16:21:23.339 CST [39835] LOG:  listening on IPv6 address "::", port 15432
2018-05-07 16:21:23.354 CST [39835] LOG:  listening on Unix socket "/tmp/.s.PGSQL.15432"
2018-05-07 16:21:23.380 CST [39835] LOG:  redirecting log output to logging collector process
2018-05-07 16:21:23.380 CST [39835] HINT:  Future log output will appear in directory "pg_log".
Done.
CREATE NODE
 pgxc_pool_reload
------------------
 t
(1 row)

CREATE NODE
 pgxc_pool_reload
------------------
 t
(1 row)

EXECUTE DIRECT
 pgxc_pool_reload
------------------
 t
(1 row)

EXECUTE DIRECT
 pgxc_pool_reload
------------------
 t
(1 row)

新增第一個 gtm proxy 服務 (它負責 批次傳輸 gtm coordinator 間的溝通,及 如果有gtm slave,會負責跟co間的溝通切到 slave 服務)
postgres@pgxl3:~/pgxc_ctl$ pgxc add gtm_proxy proxy1 pgxl1 50001 $dataDirRoot/proxy1


Initializing gtm proxy proxy1.
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.


fixing permissions on existing directory /home/postgres/DATA/pgxl/nodes/proxy1 ... ok
creating configuration files ... ok

Success.
Done.
Starting gtm proxy proxy1.
server starting
Done.

新增第二個 gtm proxy 服務
postgres@pgxl3:~/pgxc_ctl$ pgxc add gtm_proxy proxy2 pgxl2 50002 $dataDirRoot/proxy2
Initializing gtm proxy proxy2.
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.


fixing permissions on existing directory /home/postgres/DATA/pgxl/nodes/proxy2 ... ok
creating configuration files ... ok

Success.
Done.
Starting gtm proxy proxy2.
server starting
Done.

新增gtm slave , 不讓GTM 成為整個架構中SPOF(Single Point Of Failure)
postgres@pgxl3:~/pgxc_ctl$ pgxc add gtm slave gtm_slave pgxl4 20002 $dataDirRoot/gtm_slave
Initialize GTM slave
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.


fixing permissions on existing directory /home/postgres/DATA/pgxl/nodes/gtm_slave ... ok
creating configuration files ... ok
creating control file ... ok

Success.
Done.
Start GTM slaveserver starting
Done.

最後,這個solution 有一家國外公司提供技術支援,我跟他們亞太區的負責人做了mail 溝通,討論support model。針對客戶自建的Cluster 系統,他們僅提供顧問服務,可以remote支援,但僅在雙方談好的時間下進行支援,顧問服務一次得購足40個小時(應該是以它為單位) 40小時的期限是12個月,底下是價碼 :

2ndQuadrant Remote Consulting - $8,500 for 40 hours.

    Taken down hourly for remote.
    Hours are valid for 12 months.
    Migration, Database Upgrades, HA/DR setup, Sharding.

沒有緊急叫修,如果有這個需要,該負責人建議使用他們家的雲端服務,價碼如下:


2ndQuadrant 24x7 Postgres XL Support.

    Gold - $48,000 for up to 16 nodes.
    $2,500 for each additional node.
    12 month subscription.

這是對方發出mail的原文:
Our consulting service is not designed to deal with emergency. It is scheduled in advanced at a mutual convenient time and usually works well for projects like:

    Migration from legacy relational platforms (Oracle, SQL Server, MySQL, EDB Postgres Advanced Server,
Pivotal Greenplum)
    Schema Design, Database Set Up, Configuration Tuning, Query Tuning, Data Modeling and Troubleshooting
    Benchmarking and Performance Tuning, Performance Forecasting and Capacity Planning
    Major Release (Multi-Version) PostgreSQL Upgrades
    Distributed Database Implementations (Multi Master, MPP Sharding)
    High Availability / Disaster Recovery review and set up
    Backup and Recovery review and set up, Replication review and set up, Partitioning review and set up
    Custom PostgreSQL Development

2ndQuadrant Remote Consulting - $8,500 for 40 hours.

    Taken down hourly for remote.
    Hours are valid for 12 months.
    Migration, Database Upgrades, HA/DR setup, Sharding.


If you like to be covered against emergencies you need to subscribe to our 2ndQuadrant Production Support.
2ndQuadrant operate a continuous follow the sun support system. There is no incident limit or time limit on support tickets. 2ndQuadrant charge per supported database instance not per socket or vcore. 2ndQuadrant Platinum 24x7 support is unique in the industry. No one else can guarantee a 15 minute response time with 4 hour work around and 24 hour core patch. 2ndQuadrant also offer an industry leading 1 hour SLA product. Other service providers only offer 1 hour SLA's.

2ndQuadrant 24x7 Postgres XL Support.

    Gold - $48,000 for up to 16 nodes.
    $2,500 for each additional node.
    12 month subscription.


Our support teams are staffed by PostgreSQL developers and contributors, 2ndQuadrant have written and contributed significant amount of the code in PostgreSQL, our support organization is capable of engaging on deep intractable production issues and working these systematically to

resolution. When you become a support customer, 2ndQuadrant will fingerprint your supported servers. We will run (or have you run) a non-intrusive data collection script that give us details on how your databases are laid out and performing, we complete a questionnaire on your environment and systems and establish protocols for interacting with our support portal and engineers. We invest in understanding your environments in advance so that we can immediately move to problem resolution based on a deep understanding of your environment.