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.

MPP 初體驗 — PGXL

 這個solution我個人是覺得不能用,架構是很好,但我用dbt-3來進行測試,第三項throughput test 就是會hang 住;後來我用GreenPlum 進行測試,人家的系統可是一次就Pass。國外維護XL程式碼的那家公司2ndQuadrant 發了mail詢問我測得如何,我就直接說,你們家的東西不能用,結果對方回應我,很遺憾沒經過顧問的諮詢,你們做出了這個結論。這就怪了,你如果要推方案給我,是不是該主動進行POC給我看阿,怎會感覺是要我付錢給你來測給我看哩!!我會把數據貼出來供大家參考。
這是XL95在 factor 10下的測試結果,紅色框框部份結果空白,就是它測試不過
 XL10根本不用看了,它連第二項測試Power test都過不了。
數字沒比較,就看不出差異,所以我就測了Postgres 9.5 ,然後拿它的數據跟XL95進行比較
有興趣的人應該會想知道Q9、Q20及Q21到底是甚麼,文章最後我會把這三個SQL Query貼出來。
測試過程中一直失敗,我就一直在Intenet 谷哥相關資訊,所以就找到了GreenPlum的資料。一開始我還覺得以postgres 8.3這麼舊的系統為底子的東西有甚麼好測的,後來研究了一下,就想說隨便架設一個來測看看好了,想不到這隨便一架,dbt-3的三項完整測試就過了,當下我實在是無言,浪費那麼多的時間在XL的測試上頭,底下就是GP在Factor 10下的測試數據
光看GP 與XL第一張圖的比較,就可以看出差異了。
我當然也拿它跟Postgres 9.5做了比較。
GP 在dbt-3 Factor 30下我也測過,是OK的;反倒是Postgres 9.5 在這個資料量下的throughput test 同XL一樣會hang住,所以我就沒再繼續測下去。
在進行XL dbt-3 測試前,我用過pgbench 進行OLTP的測試,跟Postgres 9.5 的測試結果相比較的話,真是慘不忍睹,所以它也不能拿來當成線上交易系統來使用。
這是測試環境 :
Postgres XL 9.5及10 (三台主機, 9 nodes ) 測試環境 :
GTM and Coordinator 在獨立一台主機
Datanode * 8 分散在兩台與GTM不相同的主機
gtm proxy * 8 與 Datanode 放在一起
shared buffers 各2GB,共 18GB
dbt3 獨立於在上述三台主機
Greenplum Cluster 5.9 (三台主機, 9 nodes ) 測試環境 :
Master 在獨立一台主機
Segment * 8 分散在兩台與Master不相同的主機,各own 有4 segment
shared buffers 各2GB,共 18GB
dbt3 獨立於在上述三台主機
Postgres 9.5 Single instance 測試環境 :
db instance 一台 , shared buffer 18GB
dbt3 獨立於在上述postgresql 9.5 主機

Q9:
select  nation, o_year, sum(amount) as sum_profit
from  (
select n_name as nation, extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
  from  part, supplier,lineitem,partsupp,orders, nation
  where  s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey 
   and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%floral%'
 ) as profit group by  nation, o_year order by  nation, o_year desc;
 
Q20:
select  s_name, s_address
from
 supplier,
 nation
where
 s_suppkey in ( select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'papaya%')
   and ps_availqty in ( select 0.5 * sum(l_quantity) from lineitem
    where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= '1996-01-01'
     and l_shipdate < cast(date '1996-01-01' + interval '1 year' as date) ))
 and s_nationkey = n_nationkey and n_name = 'UNITED KINGDOM'order by s_name;
 

Q21:
select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation
where s_suppkey = l1.l_suppkey  and o_orderkey = l1.l_orderkey  and o_orderstatus = 'F'
 and l1.l_receiptdate > l1.l_commitdate
 and exists (select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey )
 and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey
   and l3.l_receiptdate > l3.l_commitdate) and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE'
group by s_name order by  numwait desc,s_name LIMIT 100;

地端資料庫透過native backup restore 的方式移轉到AWS SQL RDS

首先你當然是已經把備份檔已經放到了S3 bucket權限也已經設好
— restore 資料庫
exec msdb.dbo.rds_restore_database
@restore_db_name=’test1′ ,
@s3_arn_to_restore_from=’arn:aws:s3:::steven-sqlbackup/urdb_compress.bak’;
GO

Restore 的過程中, 切入test1 ,會丟下列訊息
Msg 927, Level 14, State 2, Server EC2AMAZ-99VV0NB, Line 1
Database ‘test1’ cannot be opened. It is in the middle of a restore.

查看資料庫restore 的狀態

exec msdb.dbo.rds_task_status @db_name=’test1′
go

資料庫回復之後當然要建立使用者,但是就算建出來之後在AWS SQL RDS 的環境下你沒有權限執行 sp_change_users_login 去重新連結instance login 及 database user我想到的解決方式就是,砍掉database user , 再重建database user(不然就得在地端改為contained db)但因為database user 應該也會是schema owner因此要先把schema owner 轉到其他的database user 在原本on-premise 的資料庫裡頭 ECDO_SA這個database user 是b2c 及 b2e 兩個schema的擁有者所以要進行b2c 及 b2e 兩個schema 所有權的移轉

USE test1
GO

ALTER AUTHORIZATION ON SCHEMA::b2c TO steven
GO
ALTER AUTHORIZATION ON SCHEMA::b2e TO steven
GO
drop user ECDO_SA
go

建立 ECDO_SA 登入帳號
use master
go
CREATE LOGIN [ECDO_SA] WITH PASSWORD= ‘123456’, DEFAULT_DATABASE=[test1], DEFAULT_LANGUAGE=[繁體中文], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

建立ECDO_SA 資料庫使用者,這樣ECDO_SA登入instance後,才能存取test1 資料庫
use test1
go
CREATE USER ECDO_SA FOR LOGIN ECDO_SA
go

再把b2c 及 b2e 兩個schema 的擁有者給回 ECDO_SA
ALTER AUTHORIZATION ON SCHEMA::b2c TO ECDO_SA
GO
ALTER AUTHORIZATION ON SCHEMA::b2e TO ECDO_SA
GO

設定ECDO_SA 資料庫使用者預設使用的schemea, 這樣存取表格資料,就不用帶full qualified name
ALTER USER ECDO_SA WITH DEFAULT_SCHEMA = b2e;
GO

透過ECDO_SA 帳號登入AWS MSSQL RDS
sqlcmd -S stevenatpic.crrfnaqae3yd.us-east-1.rds.amazonaws.com -U ECDO_SA -P 123456 -l 2

測試查詢資料
select * form e_oll
GO

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.