DataWorks：PostgreSQL資料來源

支援的版本

目前僅支援配置PostgreSQL資料來源為PostgreSQL10、11、12、13、14、15版本。您可以通過如下語句查看PostgreSQL資料庫的版本。

SHOW SERVER_VERSION;

使用限制

離線讀寫

PostgreSQL資料來源支援Password認證方式（支援SCRAM-SHA-256認證方式），如果PostgreSQL資料庫端更改了密碼和密碼認證方式，則需要更新資料來源配置，並且重新測試連通性和手動運行相關任務驗證。

"parameter": {
    "datasource": "abc",
    "column": [
        "id",
        "\"123Test\"", //添加轉義符
],
"where": "",
"splitPk": "id",
"table": "public.wpw_test"
},

即時讀

支援的欄位類型

離線讀寫

支援大部分PostgreSQL類型，但也存在部分類型沒有支援的情況，請注意檢查您的資料類型。

資料同步前準備：PostgreSQL環境準備

在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持續增長。

資料同步任務開發：PostgreSQL同步流程引導

PostgreSQL資料同步任務的配置入口和通用配置流程指導可參見下文的配置指導，詳細的配置參數解釋可在配置介面查看對應參數的文案提示。

建立資料來源

在進行資料同步任務開發時，您需要在DataWorks上建立一個對應的資料來源，操作流程請參見建立並管理資料來源。

如果您的PostgreSQL資料庫開啟了SSL認證，那麼您在添加DataWorks的PostgreSQL資料來源時也需要開啟SSL認證，具體操作流程請參見附錄二：PostgreSQL資料來源增加SSL認證。

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

• 操作流程請參見通過嚮導模式配置離線同步任務、通過指令碼模式配置離線同步任務。

• 指令碼模式配置的全量參數和指令碼Demo請參見下文的附錄一：PostgreSQL指令碼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不進行任何安全性校正。

附錄一：PostgreSQL指令碼Demo與參數說明

附錄：離線任務指令碼配置方式

如果您配置離線任務時使用指令碼模式的方式進行配置，您需要在任務指令碼中按照指令碼的統一格式要求編寫指令碼中的reader參數和writer參數，指令碼模式的統一要求請參見通過指令碼模式配置離線同步任務，以下為您介紹指令碼模式下的資料來源的Reader參數和Writer參數的指導詳情。

PostgreSQL Reader指令碼Demo

{
    "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"
            }
        ]
    }
}

PostgreSQL Reader指令碼參數

PostgreSQL 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"
      }
    ]
  }
}

PostgreSQLe Writer指令碼參數

{
    "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認證