Pgloader極簡教程

Naylor發表於2024-11-20

目錄
  • 簡介
  • 安裝
    • 安裝概述
    • CentOS編譯安裝
  • 使用
    • 命令列
    • 配置檔案遷移
    • 可能遇到的錯誤
    • 更多配置參考
  • 總結
  • 引用

簡介

pgloader是一個資料同步工具，用來將資料從其它地方遷移到postgresql中，支援從如下源遷移：

  • 檔案：CSV、Fixed Format、Postgres COPY text format、DBF、IXF
  • 資料庫系統：SQLite、MySql、MSSQLServer、PostgreSQL、Redshift

應用場景

需要往postgresql中匯入資料的時候，如資料遷移。

安裝

安裝概述

安裝方式比較豐富，詳見 https://pgloader.readthedocs.io/en/latest/install.html 。

遺憾的是未提供CentOS環境編譯好的程式供下載，所以需要手動編譯安裝。

CentOS編譯安裝

去官網下載最新原始碼：

https://github.com/dimitri/pgloader

將原始碼放到 /usr/bin下，本文為例：


[root@bogon pgloader-3.6.9]# pwd
/usr/local/pgloader-3.6.9

如果下載的是原始碼壓縮包需要使用如下命令解壓：


tar -zxvf pgloader-3.6.9.tar.gz

賦予指令碼執行許可權：

cd /usr/local/pgloader-3.6.9


chmod -R 777 *

執行 bootstrap-centos7.sh 指令碼，下載相關依賴


bootstrap-centos7.sh

執行編譯：


make  pgloader

如果有提示到 ("libcrypto.so.1.1" "libcrypto.so.1.0.0" "libcrypto.so.3" "libcrypto.so") 沒有找到或者相關資訊
需要先安裝 openssl


yum -y install openssl openssl-devel

複製編譯好的程式到系統執行目錄 /usr/local/bin/ 下


cp  /usr/local/pgloader-3.6.9/build/bin/pgloader        /usr/local/bin/

檢視是否安裝好了：


[root@bogon home]# pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.2.5

使用

pgloader 有兩種常見的使用方式：

  • 透過命令列
  • 透過遷移配置檔案

命令列

如下命令列：

pgloader mysql://user:password@ip:3306/dbName postgresql://user:password@ip:5432/dbName
  • 將名為dbName的資料庫結構和資料 從mysql遷移到postgresql
  • pgloader 為上述 /usr/local/bin/pgloader 的可執行檔案
  • 後面是mysql 的連線資訊 ， postgresql 的連線資訊，中間使用空格分隔
  • 需要使用有寫入許可權的賬號，建議使用root使用者操作

配置檔案遷移

另外一種方式就是編寫遷移配置檔案，然後使用 pgloader sync.load 命令執行配置檔案。

如下配置檔案演示了僅同步mysql的source_db庫中的ramble_doc 表到 postgresql中的target_db庫中，執行完畢之後將在postgresql中新建一個名為ramble_doc 的表，並新增資料。

LOAD DATABASE
     FROM     mysql://root:xxx@192.168.1.92:3306/source_db
     INTO     postgresql://postgres:xxx@192.168.1.24:5432/target_db

INCLUDING ONLY TABLE NAMES matching 'ramble_doc' ;
  • LOAD DATABASE ：表示從資料庫執行遷移
  • FROM ：源資料庫連線資訊
  • INTO ：目標資料庫連線資訊
  • INCLUDING ONLY TABLE NAMES matching ：僅包含匹配的表
  • 最後那個分號不可少
  • 配置檔案需要按照格式編寫，如縮排

如下配置檔案演示了同步mysql 的source_db庫下所有表到postgresql的target_db庫下面，包含表結構和資料。

LOAD DATABASE
     FROM     mysql://root:xxx@192.168.1.92:3306/source_db
     INTO     postgresql://postgres:xxx@192.168.1.24:5432/target_db
     WITH batch rows = 10000 , batch size =200MB , prefetch rows  = 5000 , workers = 4 ,concurrency = 3
;
  • WITH：with 後面可以追加一些附屬引數，各個引數使用英文逗號分隔。常見的引數如：是否需要同步資料還是僅同步結構，是否在寫入資料前先刪除表等
  • batch rows ：在同步資料的時候分批插入postgresql的行數，預設為2.5萬。
  • batch size：每批最大資料大小，設定此引數可避免出現記憶體溢位
  • prefetch rows：在同步資料的時候分批從mysql讀取的行數，預設為1000。
  • workders： 執行緒數量
  • concurrency：併發執行緒數量

可能遇到的錯誤

記憶體溢位

報錯資訊為：


Heap exhausted during garbage collection: 64 bytes available, 80 requested.


垃圾回收期間堆已耗盡：可用64個位元組，請求80個位元組。

解決方案為調優分批數量和併發數量，需要根據源資料庫資料量，硬體情況不斷嘗試。

更多配置參考

官網給了一個例子：


LOAD DATABASE
     FROM      mysql://root@localhost/sakila
     INTO postgresql://localhost:54393/sakila

 WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1,
      multiple readers per thread, rows per range = 50000

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '128MB',
      work_mem to '12MB',
      search_path to 'sakila, public, "$user"'

  SET MySQL PARAMETERS
      net_read_timeout  = '120',
      net_write_timeout = '120'

 CAST type bigint when (= precision 20) to bigserial drop typemod,
      type date drop not null drop default using zero-dates-to-null,
      -- type tinyint to boolean using tinyint-to-boolean,
      type year to integer

 MATERIALIZE VIEWS film_list, staff_list

 -- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor'
 -- EXCLUDING TABLE NAMES MATCHING ~<ory>
 -- DECODING TABLE NAMES MATCHING ~/messed/, ~/encoding/ AS utf8
 -- ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
 -- ALTER TABLE NAMES MATCHING ~/_list$/ SET SCHEMA 'mv'

 ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/
  SET SCHEMA 'mv'

 ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
 ALTER TABLE NAMES MATCHING ~/./ SET (fillfactor='40')

 ALTER SCHEMA 'sakila' RENAME TO 'pagila'

 BEFORE LOAD DO
   $$ create schema if not exists pagila; $$,
   $$ create schema if not exists mv;     $$,
   $$ alter database sakila set search_path to pagila, mv, public; $$;

https://pgloader.readthedocs.io/en/latest/ref/mysql.html

總結

pgloader是一個資料庫遷移工具，花一點點時間研究一下如何使用，將在資料庫遷移的時候起到事半功倍的效果，往往比自己編寫遷移指令碼更加完善和可靠。

引用

  • 官網：https://pgloader.readthedocs.io/en/latest/
  • github：https://github.com/dimitri/pgloader

相關文章