DataWorks：PostgreSQL資料來源

離線讀寫

• 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"
  },

• 暫不支援基於唯一索引直接更新寫入到Postgres資料來源，建議先將資料寫入暫存資料表，再通過RENAME操作來完成更新。

即時讀

Data Integration即時同步任務存在如下約束與限制：

• Data Integration對ADD COLUMN進行了特別支援：

  • 約束：ADD COLUMN時不能有ADD COLUMN和DROP COLUMN或者其他DDL的組合。

    重要

    ADD COLUMN時其他DROP COLUMN、RENAME COLUMN等ALTER COLUMN的行為將使資料同步任務不能正常工作。

  • 限制：除了ADD COLUMN外，無法識別使用者的其他DDL操作。

• 不支援ALTER TABLE/CREATE TABLE。

• 不支援TEMPORARY表、UNLOGGED表和Hyper表複製，PostgreSQL資料庫沒有提供機制來對這兩種類型的表進行log解析訂閱。

• 不支援Sequences複製（serial/bigserial/identity）。

• 不支援TRUNCATE操作。

• 不支援大對象複製（Bytea）。

• 不支援視圖、物化視圖、外部表格複製。

• PostgreSQL作為單表即時和整庫即時的源端時，僅支援同步有owner許可權的表。

準備工作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持續增長。

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

• 操作流程請參見嚮導模式配置、指令碼模式配置。

• 指令碼模式配置的全量參數和指令碼Demo請參見下文的附錄一：指令碼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指令碼參數

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指令碼參數

{
    "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認證檔案說明

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

擷取PostgreSQL SSL認證檔案

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

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

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

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

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

   

   說明

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

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

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

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

   

   下載的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認證。

   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配置項時，其對應操作如下：

• Truststore 認證檔案：上傳Truststore認證檔案擷取步驟擷取到的尾碼為.pem的檔案或尾碼為.p7b檔案。

• Keystore 認證檔案：上傳產生用戶端認證步驟擷取到的用戶端認證檔案client.crt。

• 私密金鑰檔案：上傳轉換私密金鑰檔案步驟擷取到的用戶端認證私密金鑰檔案轉換成的client.pk8檔案。

• 私密金鑰密碼：指的是配置私密金鑰密碼步驟配置的密碼。

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