全部產品
Search
文件中心

DataWorks:PostgreSQL資料來源

更新時間:Oct 25, 2024

PostgreSQL資料來源為您提供讀取和寫入PostgreSQL雙向通道的功能,方便您後續可以通過嚮導模式和指令碼模式配置資料同步任務。本文為您介紹DataWorks的PostgreSQL資料同步能力支援情況。

支援的版本

目前僅支援配置PostgreSQL資料來源為PostgreSQL101112131415版本。您可以通過如下語句查看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 COLUMNDROP COLUMN或者其他DDL的組合。

      重要

      ADD COLUMN時其他DROP COLUMN、 RNAME COLUMNALTER 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許可權。

說明

即時同步只支援邏輯複製機制,邏輯複製使用發布和訂閱模型,其中一個或多個訂閱者訂閱發行者節點上的一個或多個發布。訂閱者從他們訂閱的發布中提取資料。

表的邏輯複製通常從對發行者資料庫上的資料進行快照並將其複製到訂閱者開始。完成後,發行者上的更改會即時發送給訂閱者。

  1. 建立帳號。

    操作詳情請參見建立帳號和資料庫

  2. 配置許可權。

    檢查帳號是否有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同步流程引導

資料同步任務的配置入口和通用配置流程可參見下文的配置指導。

單表離線同步任務配置指導

整庫離線讀、單表/整庫全增量即時讀等同步任務配置指導

操作流程請參見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

配置一個從PostgreSQL資料庫同步抽取資料作業,使用指令碼開發的詳情請參見通過指令碼模式配置離線同步任務

{
    "type":"job",
    "version":"2.0",//版本號碼。
    "steps":[
        {
            "stepType":"postgresql",//外掛程式名。
            "parameter":{
                "datasource":"",//資料來源。
                "column":[//欄位。
                    "col1",
                    "col2"
                ],
                "where":"",//篩選條件。
                "splitPk":"",//用splitPk代表的欄位進行資料分區,資料同步會啟動並發任務進行資料同步。
                "table":""//表名。
            },
            "name":"Reader",
            "category":"reader"
        },
        { 
            "stepType":"stream",
            "parameter":{},
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"//錯誤記錄數。
        },
        "speed":{
            "throttle":true, //當throttle值為false時,mbps參數不生效,表示不限流;當throttle值為true時,表示限流。
            "concurrent":1, //作業並發數。
            "mbps":"12"//限流,此處1mbps = 1MB/s。
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Reader指令碼參數

參數

描述

是否必選

預設值

datasource

資料來源名稱,指令碼模式支援添加資料來源,此配置項填寫的內容必須與添加的資料來源名稱保持一致。

table

選取的需要同步的表名稱。

column

所配置的表中需要同步的列名集合,使用JSON的數組描述欄位資訊 。預設使用所有列配置,例如[ * ]。

  • 支援列裁剪,即可以挑選部分列進行同步。

  • 支援列換序,即列可以不按照表schema資訊順序進行同步。

  • 支援常量配置,您需要按照MySQL SQL文法格式,例如["id", "table","1", "'mingya.wmy'", "'null'", "to_char(a+1)", "2.3" , "true"]

    • id為普通列名。

    • table為包含保留字的列名。

    • 1為整型數字常量。

    • 'mingya.wmy'為字串常量(注意需要加上一對單引號)。

    • 'null'為字串。

    • to_char(a+1)為計算字串長度函數。

    • 2.3為浮點數。

    • true為布爾值。

  • column必須顯示指定同步的列集合,不允許為空白。

splitPk

PostgreSQL Reader進行資料幫浦時,如果指定splitPk,表示您希望使用splitPk代表的欄位進行資料分區,資料同步會啟動並發任務,以提高資料同步的效能:

  • 推薦splitPk使用者使用表主鍵,因為表主鍵通常情況下比較均勻,因此切分出來的分區也不容易出現資料熱點。

  • splitPk僅支援整型資料切分,不支援字串、浮點、日期等其它類型 。如果您指定其它非支援類型,忽略splitPk功能,使用單通道進行同步。

  • 如果splitPk不填寫,包括不提供splitPk或者splitPk值為空白,資料同步視作使用單通道同步該表資料 。

where

篩選條件,PostgreSQL Reader根據指定的columntablewhere條件拼接SQL,並根據該SQL進行資料幫浦。例如在測試時,您可以使用where條件指定實際業務情境,通常會選擇當天的資料進行同步,指定where條件為id>2 and sex=1

  • where條件可以有效地進行業務增量同步處理。

  • where條件不配置或者為空白,視作全表同步資料。

querySql(進階模式,嚮導模式不提供)

在部分業務情境中,where配置項不足以描述所篩選的條件,您可以通過該配置型來自訂篩選SQL。當配置該項後,資料同步系統會忽略tables、columns和splitPk配置項,直接使用該配置的內容篩選資料。例如需要進行多表JOIN後同步資料,使用select a,b from table_a join table_b on table_a.id = table_b.id。當您配置querySql時,PostgreSQL Reader直接忽略table、column和where條件的配置。

fetchSize

該配置項定義了外掛程式和資料庫伺服器端每次批量資料擷取條數,該值決定了Data Integration和伺服器端的網路互動次數,能夠較大地提升資料幫浦效能。

說明

fetchSize值過大(>2048)可能造成資料同步進程OOM。

512

Writer指令碼Demo

指令碼配置樣本如下,詳情請參見上述參數說明。

{
  "type":"job",
  "version":"2.0",//版本號碼。
  "steps":[ 
    {
      "stepType":"stream",
      "parameter":{},
      "name":"Reader",
      "category":"reader"
    },
    {
      "stepType":"postgresql",//外掛程式名。
      "parameter":{
        "datasource":"",//資料來源。
        "column":[// 欄位。
          "col1",
          "col2"
        ],
        "table":"",//表名。
        "preSql":[],//執行資料同步任務之前率先執行的SQL語句。
        "postSql":[],//執行資料同步任務之後率先執行的SQL語句。
      },
      "name":"Writer",
      "category":"writer"
    }
  ],
  "setting":{
    "errorLimit":{
      "record":"0"//錯誤記錄數
    },
    "speed":{
      "throttle":true,//當throttle值為false時,mbps參數不生效,表示不限流;當throttle值為true時,表示限流。
      "concurrent":1, //作業並發數。
      "mbps":"12"//限流,此處1mbps = 1MB/s。
    }
  },
  "order":{
    "hops":[
      {
        "from":"Reader",
        "to":"Writer"
      }
    ]
  }
}

Writer指令碼參數

參數

描述

是否必選

預設值

datasource

資料來源名稱,指令碼模式支援添加資料來源,該配置項填寫的內容必須要與添加的資料來源名稱保持一致。

table

選取的需要同步的表名稱。

writeMode

選擇匯入模式,目前支援insert和copy兩種方式:

  • insert:執行PostgreSQL的insert into...values... 語句,將資料寫入PostgreSQL中。當資料出現主鍵/唯一性索引衝突時,待同步的資料行寫入PostgreSQL失敗,目前記錄行成為髒資料。建議您優先選擇insert模式。

  • copy:PostgreSQL提供copy命令,用於表與檔案(標準輸出,標準輸入)之間的相互複製。Data Integration支援使用copy from將資料載入到表中。建議您在遇到效能問題時再嘗試使用該模式。

insert

column

目標表需要寫入資料的欄位,欄位之間用英文逗號分隔。例如"column":["id","name","age"]。如果要依次寫入全部列,使用(*)表示,例如"column":["*"]

preSql

執行資料同步任務之前率先執行的SQL語句。目前嚮導模式僅允許執行一條SQL語句,指令碼模式可以支援多條SQL語句,例如清除舊資料。

postSql

執行資料同步任務之後執行的SQL語句。目前嚮導模式僅允許執行一條SQL語句,指令碼模式可以支援多條SQL語句,例如加上某一個時間戳記。

batchSize

一次性批量提交的記錄數大小,該值可以極大減少Data Integration與PostgreSQL的網路互動次數,並提升整體輸送量。但是該值設定過大可能會造成Data Integration運行進程OOM情況。

1,024

pgType

PostgreSQL特有類型的轉化配置,支援bigint[]、double[]、text[]、Jsonb和JSON類型。配置樣本如下。

{
    "job":
    {
        "content":
        [{
            "reader": {...},
            "writer":
            {
                "parameter":
                {
                    "column":
                    [
                        // 目標表欄位列表
                        "bigint_arr",
                        "double_arr",
                        "text_arr",
                        "jsonb_obj",
                        "json_obj"
                    ],
                    "pgType":
                    {
                        // 特殊的類型設定,key為目標表的欄位名,value為欄位類型。
                        "bigint_arr": "bigint[]",
                        "double_arr": "double[]",
                        "text_arr": "text[]",
                        "jsonb_obj": "jsonb",
                        "json_obj": "json"
                    }

                }
            }
        }]
    }
}

附錄二:PostgreSQL資料來源增加SSL認證

PostgreSQL SSL認證檔案說明

在DataWorks上建立或修改PostgreSQL資料來源串連方式,支援配置SSL認證方式,SSL認證相關的配置項說明,具體如下。

PostgreSQL資料庫

DataWorks的PostgreSQL資料來源配置

SSL鏈路加密

用戶端加密

配置ACL

配置項

說明

開啟

不啟用

不涉及

Truststore認證檔案

可選,用戶端使用該認證來證明伺服器。

  • 不配置:使用普通鏈路。

  • 配置:使用SSL加密鏈路。

啟用

配置ACL為prefer

  • Keystore認證檔案

  • 私密金鑰檔案

  • 私密金鑰密碼

Keystore認證檔案私密金鑰檔案均可選,配置ACL為prefer表示服務端不強制校正用戶端。

  • 如果私密金鑰檔案未加密,使用SSL加密鏈路串連資料庫,校正私密金鑰檔案。

  • 如果私密金鑰檔案加密,則必須配置私密金鑰密碼才可正常校正私密金鑰檔案,成功連通。

配置ACL為verify-ca

  • Keystore認證檔案:必須配置。為用戶端CA認證,用戶端需要使用該認證向伺服器證明自己的身份。

  • 私密金鑰檔案:必須配置。用戶端持有該私密金鑰檔案,不對外公開,用來與上傳到伺服器的Keystore認證檔案進行身份認證。

  • 私密金鑰密碼:可選配置。如果私密金鑰檔案被密碼保護,則需要填寫私密金鑰密碼,反之則可不填寫。

說明
  • ACL配置設定為prefer時,不會強制校正用戶端內容。

    • 如果SSL認證時不配置任何檔案,走普通鏈路。

    • 如果SSL認證時添加了認證檔案,可參考以上表格相對應描述。

  • ACL配置設定為verify-ca時,保證Keystore認證檔案私密金鑰檔案以及私密金鑰密碼三個配置項配置,即可建立資料來源。

擷取PostgreSQL SSL認證檔案

本文將以RDS PostgreSQL執行個體為例產生SSL認證認證。

  1. Truststore認證檔案擷取方式。

    Truststore 認證檔案擷取的具體資訊可以參考使用雲端認證快速開啟SSL加密

    1. 進入RDS執行個體列表,單擊查看對應地區的RDS執行個體,並點擊目標執行個體ID,進入執行個體資訊頁面。

    2. 選擇需要保護的串連串,具體操作如下圖所示:

    image

    說明
    • 如果已開啟外網地址,系統將同時顯示內網和外網兩個串連地址。雲端認證只能對一個串連地址進行保護,而內網串連地址相對更安全,推薦您對外網串連地址進行保護。查看內網和外網地址的具體方法。請參考查看內外網地址

    • 如果您需要同時對內網和外網串連地址進行保護,可以參考使用自訂認證開啟SSL加密

    • 配置雲端認證後,執行個體的運行狀態將會變成修改SSL中,該狀態持續三分鐘左右,請耐心等待運行狀態變更為運行中後再進行後續操作。

    c. 單擊下載CA認證,即可擷取到Truststore認證檔案。

    image

    下載的CA認證包含3個檔案,在DataWorks配置PostgreSQL資料來源時,將尾碼為.pem的檔案或尾碼為.p7b檔案上傳到Truststore 認證檔案配置項中。

  2. Keystore認證檔案私密金鑰檔案私密金鑰密碼的擷取與配置。

    前提條件:已完成使用雲端認證快速開啟SSL加密使用自訂認證開啟SSL加密以及擁有OpenSSL工具。

    說明

    如果您使用的是Linux系統,則系統已內建OpenSSL工具,無需進行安裝。如果您使用Windows系統,請擷取OpenSSL軟體包並安裝。

    Keystore認證檔案私密金鑰檔案私密金鑰密碼擷取與配置的具體資訊可以參考配置用戶端CA認證

    1. 在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"
    2. 產生用戶端認證請求檔案(client.csr)和用戶端認證私密金鑰(client.key)。

      openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=<用戶端使用者名稱>"

      該命令中-subj參數後的CN取值請配置為用戶端訪問資料庫的使用者名稱。

    3. 產生用戶端認證(client.crt)。

      openssl x509 -req -in client.csr -text -days 365  -CA ca1.crt -CAkey ca1.key -CAcreateserial  -out client.crt
    4. 如果您的RDS PostgreSQL伺服器需要驗證用戶端CA認證,那麼您需要開啟產生的用戶端自我簽署憑證ca1.crt檔案,複製認證內容粘貼在請填寫用戶端認證授權機構公開金鑰內容對話方塊內,作為用戶端CA認證。image

    5. 在RDS側配置好用戶端CA認證後,在DataWorks上配置PostgreSQL資料來源時,需要將用戶端認證私密金鑰client.key轉換為client.pk8檔案,並將client.pk8上傳至DataWorks的PostgreSQL資料來源配置的私密金鑰檔案配置項中。

      cp client.key client.pk8
    6. 配置私密金鑰密碼。

      openssl pkcs8 -topk8 -inform PEM -in client.key -outform der -out client.pk8 -v1 PBE-MD5-DES
      說明

      在執行配置私密金鑰密碼命令時,必須輸入密碼。如果您設定了密碼,則在DataWorks的PostgreSQL資料來源配置中的私密金鑰密碼也需要使用相同的密碼。

配置 PostgreSQL SSL 認證檔案

在將擷取到的認證檔案上傳到DataWorks的PostgreSQL配置項時,其對應操作如下:

image

配置ACL進入RDS執行個體列表,單擊查看對應地區的RDS執行個體,並點擊目標執行個體ID,進入執行個體資訊頁面後,點擊資料安全性 > 配置ACL單擊修改後,可以選擇不同SSL認證方法,可參考強制用戶端開啟SSL串連

image

說明
  • 若ACL認證方法為prefer,則PostgreSQL伺服器不會對用戶端認證進行強制校正。

  • 若在RDS PostgreSQL中配置了ACL認證方法為verify-ca,則在配置DataWorks的PostgreSQL資料來源時,需要上傳正確的用戶端認證,保證伺服器能夠檢查用戶端認證真偽。