PostgreSQL資料來源為您提供讀取和寫入PostgreSQL雙向通道的功能,方便您後續可以通過嚮導模式和指令碼模式配置資料同步任務。本文為您介紹DataWorks的PostgreSQL資料同步能力支援情況。
支援的版本
目前僅支援配置PostgreSQL資料來源為PostgreSQL10
、11
、12
、13
、14
、15
版本。您可以通過如下語句查看PostgreSQL資料庫的版本。
SHOW SERVER_VERSION;
使用限制
離線讀寫
支援閱讀檢視表。
PostgreSQL資料來源支援Password認證方式(支援SCRAM-SHA-256認證方式),如果PostgreSQL資料庫端更改了密碼和密碼認證方式,則需要更新資料來源配置,並且重新測試連通性和手動運行相關任務驗證。
當PostgreSQL中表名稱、欄位名稱是以數字開頭,或者名稱中包含大小寫英文字母、中劃線(-)時需要使用雙引號("")進行轉義,不進行轉義會導致PostgreSQL外掛程式讀取或寫入PostgreSQL資料失敗。但是在PostgreSQL Reader和Writer外掛程式中,雙引號("")為JSON關鍵字,因此,您需要使用反斜線(\)再次對雙引號("")進行轉義。例如,表名稱為123Test
,則轉義後表名稱為\"123Test\"
。
雙引號("")中,前引號(")和後引號(")均需使用反斜線(\)進行轉義。
嚮導模式不支援轉義,您需要轉換為指令碼模式進行轉義。
使用指令碼模式進行轉義的程式碼範例如下。
"parameter": {
"datasource": "abc",
"column": [
"id",
"\"123Test\"", //添加轉義符
],
"where": "",
"splitPk": "id",
"table": "public.wpw_test"
},
即時讀
Data Integration即時同步任務存在如下約束與限制:
Data Integration對
ADD COLUMN
進行了特別支援:約束:
ADD COLUMN
時不能有ADD COLUMN
和DROP COLUMN
或者其他DDL的組合。重要ADD COLUMN
時其他DROP COLUMN、 RNAME COLUMN
等ALTER COLUMN
的行為將使資料同步任務不能正常工作。限制:除了
ADD COLUMN
外,無法識別使用者的其他DDL操作。
不支援
ALTER TABLE/CREATE TABLE
。不支援TEMPORARY表、UNLOGGED表和Hyper表複製,PostgreSQL資料庫沒有提供機制來對這兩種類型的表進行log解析訂閱。
不支援Sequences複製(
serial/bigserial/identity
)。不支援TRUNCATE操作。
不支援大對象複製(Bytea)。
不支援視圖、物化視圖、外部表格複製。
支援的欄位類型
離線讀寫
支援大部分PostgreSQL類型,但也存在部分類型沒有支援的情況,請注意檢查您的資料類型。
針對PostgreSQL的類型轉換列表,如下所示。
類型分類 | PostgreSQL資料類型 |
整數類 | BIGINT、BIGSERIAL、INTEGER、SMALLINT和SERIAL |
浮點類 | DOUBLE PRECISION、MONEY、NUMERIC和REAL |
字串類 | VARCHAR、CHAR、TEXT、BIT和INET |
日期時間類 | DATE、TIME和TIMESTAMP |
布爾型 | BOOL |
二進位類 | BYTEA |
除上述羅欄欄位類型外,其它類型均不支援。
PostgreSQL Reader中MONEY、INET和BIT需要您使用
a_inet::varchar
類似的文法進行轉換。
資料同步前準備
在DataWorks上進行資料同步前,您需要參考本文提前在PostgreSQL側進行資料同步環境準備,以便在DataWorks上進行PostgreSQL資料同步任務配置與執行時服務正常。以下為您介紹PostgreSQL同步前的相關環境準備。
準備工作1:建立帳號並配置帳號許可權
您需要規劃一個資料庫的登入帳號用於後續執行操作,此帳號需要擁有資料庫的REPLICATION、 LOGIN
許可權。
即時同步只支援邏輯複製機制,邏輯複製使用發布和訂閱模型,其中一個或多個訂閱者訂閱發行者節點上的一個或多個發布。訂閱者從他們訂閱的發布中提取資料。
表的邏輯複製通常從對發行者資料庫上的資料進行快照並將其複製到訂閱者開始。完成後,發行者上的更改會即時發送給訂閱者。
建立帳號。
操作詳情請參見建立帳號和資料庫。
配置許可權。
檢查帳號是否有
replication
許可權。select userepl from pg_user where usename='xxx'
預期返回結果為True,返回False則表示無許可權,您可以通過如下語句進行授權。
ALTER USER <user> REPLICATION;
準備工作2:檢查是否支援備庫
SELECT pg_is_in_recovery()
目前僅支援主庫,預期返回結果為False,返回True時表示是備庫,即時同步不支援備庫,需修改資料來源配置資訊為主庫的資訊,請參見配置PostgreSQL資料來源。
準備工作3:檢查wal_level
是否為logical
show wal_level
wal_level
指定了wal_log
的層級,預期返回結果為logical,否則不支援邏輯複製機制。
準備工作4:檢查是否可以啟動wal_sender
進程
-- 查詢 max_wal_senders
show max_wal_senders;
-- 查詢 pg_stat_replication 數量
select count(*) from pg_stat_replication
當max_wal_senders
不為空白,且max_wal_senders
值大於pg_stat_replication
數量時,則表示有空閑可用的wal_sender
進程。PostgreSQL資料庫會為同步資料程式啟動wal_sender
進程來給訂閱者發送日誌。
對於每一個需要同步的表,需要手動執行ALTER TABLE [tableName] REPLICA IDENTITY FULL
語句進行授權,否則即時同步任務會報錯。
PostgreSQL即時同步任務啟動後,會在資料庫中自動建立slot、publications,slot名稱格式為:di_slot_ + 解決方案ID
,publication名稱格式為:di_pub_ + 解決方案ID
,當即時同步任務停止或下線後,需手動刪除,否則可能會導致PostgreSQL WAL持續增長。
建立資料來源
在進行資料同步任務開發時,您需要在DataWorks上建立一個對應的資料來源,操作流程請參見建立並管理資料來源,詳細的配置參數解釋可在配置介面查看對應參數的文案提示。
如果您的PostgreSQL資料庫開啟了SSL認證,那麼您在添加DataWorks的PostgreSQL資料來源時也需要開啟SSL認證,具體操作流程請參見附錄二:PostgreSQL資料來源增加SSL認證。
資料同步任務開發:PostgreSQL同步流程引導
資料同步任務的配置入口和通用配置流程可參見下文的配置指導。
單表離線同步任務配置指導
操作流程請參見通過嚮導模式配置離線同步任務、通過指令碼模式配置離線同步任務。
指令碼模式配置的全量參數和指令碼Demo請參見下文的附錄一:指令碼Demo與參數說明。
整庫離線讀、單表/整庫全增量即時讀等同步任務配置指導
操作流程請參見Data Integration側同步任務配置。
常見問題
主備同步資料恢複問題
主備同步問題指PostgreSQL使用主從災備,備庫從主庫不間斷恢複資料。由於主備資料同步存在一定的時間差,特別在於某些特定情況,例如網路延遲等問題,導致備庫同步恢複的資料與主庫有較大差別,從備庫同步的資料不是一份目前時間的完整鏡像。
一致性約束
PostgreSQL在資料存放區劃分中屬於RDBMS系統,對外可以提供強一致性資料查詢介面。例如一次同步任務啟動運行過程中,當該庫存在其他資料寫入方寫入資料時,由於資料庫本身的快照特性,PostgreSQL Reader完全不會擷取到寫入的更新資料。
上述是在PostgreSQL Reader單執行緒模式下資料同步一致性的特性,PostgreSQL Reader可以根據您配置的資訊使用並發資料幫浦,因此不能嚴格保證資料一致性。
當PostgreSQL Reader根據splitPk進行資料切分後,會先後啟動多個並發任務完成資料同步。多個並發任務相互之間不屬於同一個讀事務,同時多個並發任務存在時間間隔,因此這份資料並不是完整的、一致的資料快照資訊。
針對多線程的一致性快照需求,目前在技術上無法實現,只能從工程角度解決。工程化的方式存在取捨,在此提供以下解決思路,您可以根據自身情況進行選擇。
使用單線程同步,即不再進行資料切片。缺點是速度比較慢,但是能夠很好保證一致性。
關閉其它資料寫入方,保證當前資料為待用資料,例如鎖表、關閉備庫同步等。缺點是可能影響線上業務。
資料庫編碼問題
PostgreSQL在伺服器端僅支援EUC_CN和UTF-8兩種簡體中文編碼,PostgreSQL Reader底層使用JDBC進行資料幫浦,JDBC天然適配各類編碼,並在底層進行了編碼轉換。因此PostgreSQL Reader不需您指定編碼,可以自動擷取編碼並轉碼。
對於PostgreSQL底層寫入編碼和其設定的編碼不一致的混亂情況,PostgreSQL Reader對此無法識別,也無法提供解決方案,匯出結果有可能為亂碼。
增量資料同步的方式
PostgreSQL Reader使用JDBC SELECT陳述式完成資料幫浦工作,因此可以使用
SELECT…WHERE…
進行增量資料幫浦,方式如下:資料庫線上應用寫入資料庫時,填充modify欄位為更改時間戳記,包括新增、更新、刪除(邏輯刪除)。對於該類應用,PostgreSQL Reader只需要where條件後跟上一同步階段時間戳記即可。
對於新增流水型資料,PostgreSQL Reader在where條件後跟上一階段最大自增ID即可。
對於業務上無欄位區分新增、修改資料的情況,PostgreSQL Reader無法進行增量資料同步,只能同步全量資料。
SQL安全性
PostgreSQL Reader提供querySql語句交給您自己實現SELECT抽取語句,PostgreSQL Reader本身對querySql不進行任何安全性校正。
附錄一:指令碼Demo與參數說明
離線任務指令碼配置方式
如果您配置離線任務時使用指令碼模式的方式進行配置,您需要按照統一的指令碼格式要求,在任務指令碼中編寫相應的參數,詳情請參見通過指令碼模式配置離線同步任務,以下為您介紹指令碼模式下資料來源的參數配置詳情。
Reader指令碼Demo
Reader指令碼參數
Writer指令碼Demo
Writer指令碼參數
附錄二:PostgreSQL資料來源增加SSL認證
PostgreSQL SSL認證檔案說明
在DataWorks上建立或修改PostgreSQL資料來源串連方式,支援配置SSL認證方式,SSL認證相關的配置項說明,具體如下。
PostgreSQL資料庫 | DataWorks的PostgreSQL資料來源配置 | |||
SSL鏈路加密 | 用戶端加密 | 配置ACL | 配置項 | 說明 |
開啟 | 不啟用 | 不涉及 | Truststore認證檔案 | 可選,用戶端使用該認證來證明伺服器。
|
啟用 | 配置ACL為prefer |
| Keystore認證檔案和私密金鑰檔案均可選,配置ACL為prefer表示服務端不強制校正用戶端。
| |
配置ACL為verify-ca |
|
ACL配置設定為prefer時,不會強制校正用戶端內容。
如果SSL認證時不配置任何檔案,走普通鏈路。
如果SSL認證時添加了認證檔案,可參考以上表格相對應描述。
ACL配置設定為verify-ca時,保證Keystore認證檔案、私密金鑰檔案以及私密金鑰密碼三個配置項配置,即可建立資料來源。
擷取PostgreSQL SSL認證檔案
本文將以RDS PostgreSQL執行個體為例產生SSL認證認證。
Truststore認證檔案擷取方式。
Truststore 認證檔案擷取的具體資訊可以參考使用雲端認證快速開啟SSL加密。
進入RDS執行個體列表,單擊查看對應地區的RDS執行個體,並點擊目標執行個體ID,進入執行個體資訊頁面。
選擇需要保護的串連串,具體操作如下圖所示:
說明如果已開啟外網地址,系統將同時顯示內網和外網兩個串連地址。雲端認證只能對一個串連地址進行保護,而內網串連地址相對更安全,推薦您對外網串連地址進行保護。查看內網和外網地址的具體方法。請參考查看內外網地址。
如果您需要同時對內網和外網串連地址進行保護,可以參考使用自訂認證開啟SSL加密。
配置雲端認證後,執行個體的運行狀態將會變成修改SSL中,該狀態持續三分鐘左右,請耐心等待運行狀態變更為運行中後再進行後續操作。
c. 單擊下載CA認證,即可擷取到Truststore認證檔案。
下載的CA認證包含3個檔案,在DataWorks配置PostgreSQL資料來源時,將尾碼為
.pem
的檔案或尾碼為.p7b
檔案上傳到Truststore 認證檔案配置項中。Keystore認證檔案、私密金鑰檔案、私密金鑰密碼的擷取與配置。
前提條件:已完成使用雲端認證快速開啟SSL加密或使用自訂認證開啟SSL加密以及擁有OpenSSL工具。
說明如果您使用的是Linux系統,則系統已內建OpenSSL工具,無需進行安裝。如果您使用Windows系統,請擷取OpenSSL軟體包並安裝。
Keystore認證檔案、私密金鑰檔案、私密金鑰密碼擷取與配置的具體資訊可以參考配置用戶端CA認證。
在Linux系統上的OpenSSL工具或在Windows系統上安裝OpenSSL軟體,產生自我簽署憑證(ca1.crt)和自我簽署憑證密鑰(ca1.key)。
openssl req -new -x509 -days 3650 -nodes -out ca1.crt -keyout ca1.key -subj "/CN=root-ca1"
產生用戶端認證請求檔案(client.csr)和用戶端認證私密金鑰(client.key)。
openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=<用戶端使用者名稱>"
該命令中
-subj
參數後的CN取值請配置為用戶端訪問資料庫的使用者名稱。產生用戶端認證(client.crt)。
openssl x509 -req -in client.csr -text -days 365 -CA ca1.crt -CAkey ca1.key -CAcreateserial -out client.crt
如果您的RDS PostgreSQL伺服器需要驗證用戶端CA認證,那麼您需要開啟產生的用戶端自我簽署憑證ca1.crt檔案,複製認證內容粘貼在請填寫用戶端認證授權機構公開金鑰內容對話方塊內,作為用戶端CA認證。
在RDS側配置好用戶端CA認證後,在DataWorks上配置PostgreSQL資料來源時,需要將用戶端認證私密金鑰client.key轉換為client.pk8檔案,並將client.pk8上傳至DataWorks的PostgreSQL資料來源配置的私密金鑰檔案配置項中。
cp client.key client.pk8
配置私密金鑰密碼。
openssl pkcs8 -topk8 -inform PEM -in client.key -outform der -out client.pk8 -v1 PBE-MD5-DES
說明在執行配置私密金鑰密碼命令時,必須輸入密碼。如果您設定了密碼,則在DataWorks的PostgreSQL資料來源配置中的私密金鑰密碼也需要使用相同的密碼。
配置 PostgreSQL SSL 認證檔案
在將擷取到的認證檔案上傳到DataWorks的PostgreSQL配置項時,其對應操作如下:
Truststore 認證檔案:上傳Truststore認證檔案擷取步驟擷取到的尾碼為
.pem
的檔案或尾碼為.p7b
檔案。Keystore 認證檔案:上傳產生用戶端認證步驟擷取到的用戶端認證檔案client.crt。
私密金鑰檔案:上傳轉換私密金鑰檔案步驟擷取到的用戶端認證私密金鑰檔案轉換成的client.pk8檔案。
私密金鑰密碼:指的是配置私密金鑰密碼步驟配置的密碼。
配置ACL:進入RDS執行個體列表,單擊查看對應地區的RDS執行個體,並點擊目標執行個體ID,進入執行個體資訊頁面後,點擊 單擊修改後,可以選擇不同SSL認證方法,可參考強制用戶端開啟SSL串連。
若ACL認證方法為prefer,則PostgreSQL伺服器不會對用戶端認證進行強制校正。
若在RDS PostgreSQL中配置了ACL認證方法為verify-ca,則在配置DataWorks的PostgreSQL資料來源時,需要上傳正確的用戶端認證,保證伺服器能夠檢查用戶端認證真偽。