【DB吐槽大會】第46期 - PG copy不能跳過錯誤行

背景

1、産品的問題點

• PG copy不能跳過錯誤行

2、問題點背後涉及的技術原理

• PG 支援copy文法, 由于其高效的寫入性能(功能單一, 協定精簡, 節省了冗長的query parser,rewrite,plan,execute過程), 通常被用于高速資料導入導出.
• PG copy被作為1個事務來處理, 如果一次copy過程中出現了異常的行, 将導緻整個copy任務復原.

3、這個問題将影響哪些行業以及業務場景

• 導入、遷移、恢複資料等場景

4、會導緻什麼問題?

• 如果一個copy的表資料量很多, 已經導入了很多資料, 然後發現異常資料, 導緻整個copy任務復原, 浪費時間, 同時産生的寫操作、wal都是浪費的. 必須重來一遍.

5、業務上應該如何避免這個坑

• 使用pg_bulkload插件以及對應的導入工具, 支援多項配置, 甚至能直接寫資料檔案, 跳過wal, shared buffer等, 效率比普通copy還要高.

• • http://ossc-db.github.io/pg_bulkload/pg_bulkload.html
  • https://github.com/ossc-db/pg_bulkload/

6、業務上避免這個坑犧牲了什麼, 會引入什麼新的問題

• 使用pg_bulkload需要注意, 如果選擇了direct模式導入, 會繞過wal, 那麼以前的備份就無法使用wal來恢複新導入的資料. 必須在bulk load後做一次全量備份. 同時如果有基于流複制的邏輯訂閱、實體standby, 統統都無法接受這些bulk load寫入的資料.
• pg_bulkload屬于第三方工具

7、資料庫未來産品疊代如何修複這個坑

• 希望PG自身能支援copy是跳過并記錄下異常的行到日志、指定檔案、或表裡面, 導入結束後進行修複.

• • 例如, 可以設定批量大小, 批量送出, 遇到異常時異常批次的資料跳過異常重新寫入一遍. 也比全部復原好.
  • 例如, 可以設定錯誤條數上限, 如果超過上限則回退整個copy事務.

https://github.com/digoal/blog/blob/master/202109/20210915_06.md#postgresql-%E8%AE%B8%E6%84%BF%E9%93%BE%E6%8E%A5 https://github.com/digoal/blog/issues/76