Pgloader极简教程

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: pgloader 是一款强大的数据同步与迁移工具,专为将数据从多种来源迁移到 PostgreSQL 设计。它支持从文件(如 CSV)和多种数据库系统(如 SQLite、MySQL)迁移数据。pgloader 提供了丰富的安装选项,包括手动编译安装,适用于不同操作系统。使用上,既可以通过命令行直接操作,也可以通过配置文件进行复杂的数据迁移任务,如表结构和数据的同步。其高效性和灵活性使得数据库迁移变得更加简便和可靠。

[toc]

简介

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的ource_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是一个数据库迁移工具,花一点点时间研究一下如何使用,将在数据库迁移的时候起到事半功倍的效果,往往比自己编写迁移脚本更加完善和可靠。

引用

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
SQL 关系型数据库 MySQL
将MySQL 数据迁移到 PostgreSQL
将MySQL 数据迁移到 PostgreSQL 可以采用以下步骤: 安装 PostgreSQL 数据库:首先,需要安装 PostgreSQL 数据库。可以从官方网站(https://www.postgresql.org/)下载最新版本的 PostgreSQL,并根据官方指南进行安装。 创建 PostgreSQL 数据库:在 PostgreSQL 中创建与 MySQL 数据库相对应的数据库。可以使用 pgAdmin 或命令行工具(如 psql)来创建数据库。例如,如果在 MySQL 中有一个名为 &quot;mydb&quot; 的数据库,那么可以在 PostgreSQL 中创建一个具有相同名称的数据库。 导
4540 0
|
关系型数据库 MySQL Linux
TiDB实时同步数据到PostgreSQL(三) ---- 使用pgloader迁移数据
使用PostgreSQL数据迁移神器pgloader从TiDB迁移数据到PostgreSQL,同时说明如何在最新的Rocky Linux 9(CentOS 9 stream也适用)上通过源码编译安装pgloader。
|
数据可视化 关系型数据库 MySQL
将 PostgreSQL 迁移到 MySQL 数据库
将 PostgreSQL 迁移到 MySQL 数据库
2276 2
|
缓存 NoSQL Java
Spring Boot 3 整合 Spring Cache 与 Redis 缓存实战
Spring Boot 3 整合 Spring Cache 与 Redis 缓存实战
|
SQL 关系型数据库 MySQL
postgresql|数据库|MySQL数据库向postgresql数据库迁移的工具pgloader的部署和初步使用
postgresql|数据库|MySQL数据库向postgresql数据库迁移的工具pgloader的部署和初步使用
938 0
|
11月前
|
运维 关系型数据库 Java
DataKit6.0将MySQL8.0迁移至openGauss6.0
DataKit6.0将MySQL8.0迁移至openGauss6.0
|
关系型数据库 数据库 PostgreSQL
postgresql|【基于pg_basebackup命令的归档备份和恢复---热备冷恢复方式】
postgresql|【基于pg_basebackup命令的归档备份和恢复---热备冷恢复方式】
677 0
|
关系型数据库 MySQL Java
项目从 MySQL 切换 PostgreSQL,踩了太多的坑!!!
底层自己操作数据库可能就无法修改源码了,只能修改数据库表字段类型了
732 1
|
存储 关系型数据库 MySQL
Spring Boot 2.x基础教程:使用PostgreSQL数据库
Spring Boot 2.x基础教程:使用PostgreSQL数据库
2401 0
Spring Boot 2.x基础教程:使用PostgreSQL数据库
|
关系型数据库 MySQL 数据库
使用navicat将mysql数据转换为postgresql
使用navicat将mysql数据转换为postgresql
793 1