利用pgloader工具将MySQL数据迁移至PostgreSQL数据库

这篇具有很好参考价值的文章主要介绍了利用pgloader工具将MySQL数据迁移至PostgreSQL数据库。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、简介

pgloader是一款开源软件,可以将各种来源的数据加载到PostgreSQL数据库中,支持动态读取数据,使用 COPY 流式传输协议将数据加载到 PostgreSQL 数据库中,并使用单独的线程读取和写入数据,由于能够直接从源数据库加载数据。今天我们就借助pgloader这款工具实现将MySQL数据迁移到PostgreSQL数据库。

二、版本说明

MySQL版本为8.0.31、PostgreSQL版本为13.5。

三、安装PostgreSQL数据库

1.创建pg用户及安装目录

useradd postgres  
mkdir -p /home/postgresql

2.安装pg数据库依赖包

yum install -y perl-ExtUtils-Embed readline-devel python3 python3-devel gcc-c++ cmake libarchive openssl-devel

3.pg安装部署

wget https://ftp.postgresql.org/pub/source/v13.5/postgresql-13.5.tar.gz
tar -zxvf postgresql-13.5.tar.gz  && cd postgresql-13.5/
./configure --prefix=/home/postgresql --with-python --with-perl --with-openssl
make &&make install
/home/postgresql/bin/pg_ctl --version   ##查看安装版本
pg_ctl (PostgreSQL) 13.5

4.设置环境变量

mkdir -p /app/postgresql/pgdata ##创建数据库存储目录
cat >> /etc/profile << EOF
## postgres ##
export PATH=/app/postgresql/bin:$PATH
export LD_LIBRARY_PATH=/app/postgresql/lib
export PGDATA=/app/postgresql/pgdata
EOF

source /etc/profile  #加载环境变量
pg_ctl --version
pg_ctl (PostgreSQL) 13.5

5.授权

chown -R postgres.postgres /home/postgresql
su - postgres
initdb  ##初始化

6.创建数据库

pg_ctl start -D $PGDATA  ##启动数据库
createuser -P zabbix  ##输出两次密码
Enter password for new role:
Enter it again:
createdb -O zabbix -E Unicode -T template0 zabbix  ##创建数据库

四、安装pgloader工具

1.安装部署

wget https://codeload.github.com/dimitri/pgloader/tar.gz/refs/tags/v3.6.9
tar -zxvf v3.6.9
dnf -y install freetds-devel  ##安装依赖包
cd pgloader-3.6.9/
chmod 755 bootstrap-centos.sh
./bootstrap-centos.sh
make pgloader
cp build/bin/pgloader /usr/local/bin/
pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.2.10-1.rhel8

2.修改Mysql身份认证

echo "default-authentication-plugin=mysql_native_password" >> /etc/my.conf ##pgloader不支持caching_sha2_password身份验证插件,而这个是 MySQL 8 的默认设置,需要修改配置,如MySQL8.0前版本无需操作
systemctl restart mysqld  ##重启数据库

3.导入Zabbix表结构

vi database/postgresql/schema.sql  ##从INSERT INTO dbversion开始往下全部删除,参考命令  :.,$d
CREATE INDEX sla_service_tag_1 ON sla_service_tag (slaid);
CREATE TABLE dbversion (
        dbversionid              bigint                                    NOT NULL,
        mandatory                integer         DEFAULT '0'               NOT NULL,
        optional                 integer         DEFAULT '0'               NOT NULL,
        PRIMARY KEY (dbversionid)
);
INSERT INTO dbversion VALUES ('1','6000000','6000017');
create or replace function hosts_name_upper_upper()
returns trigger language plpgsql as $func$
begin
update hosts set name_upper=upper(name)
where hostid=new.hostid;
psql -Uzabbix -dzabbix -f database/postgresql/schema.sql

4.迁移Mysql配置信息

参考文献:https://pgloader.readthedocs.io/en/latest/ref/mysql.html

mkdir -p /root/migration && cd /root/migration
vi config.pgloader  ##当复制下面配置的时候请去除所有的注释
LOAD DATABASE
FROM mysql://zabbix:*****@127.0.0.1:3306/zabbix
INTO postgresql://zabbix:*****@127.0.0.1:5432/zabbix
WITH include no drop,
#当列出此选项时,pgloader在加载数据时将不包含任何DROP语句。
truncate,
#当列出这个选项时,pgloader在将数据加载到每个PostgreSQL表之前,对每个PostgreSQL表发出TRUNCATE命令。删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子
create no tables,
#当列出此选项时,pgloader在加载数据之前跳过表的创建,目标表必须已存在。
#此外,当使用不创建表时,pgloader从当前目标数据库获取元数据并检查类型转换,然后在加载数据之前删除约束和索引,并在加载完成后重新安装它们。
create no indexes,
#当列出此选项时,pgloader将跳过创建索引。
no foreign keys,
#当列出此选项时,pgloader将跳过创建外键。
reset sequences,
#当列出这个选项时,在数据加载结束时,在所有索引都创建完成之后,pgloader将创建的所有PostgreSQL序列重置为它们所附列的当前最大值。
data only
#当列出此选项时,pgloader只发出COPY语句,而不进行任何处理。
SET maintenance_work_mem TO '1024MB', work_mem to '512MB'
#设置maintenance_work_mem和work_mem,根据自己机器的配置来设置,越大迁移越快
EXCLUDING TABLE NAMES MATCHING ~/history.*/, ~/trend.*/
#不迁移history表和trends表
ALTER SCHEMA 'zabbix' RENAME TO 'public';
#将pgloader转换生成的zabbix模式更名为public

pgloader config.pgloader ##开始迁移所有的配置不包含历史数据
Total import time          ✓     126602    12.5 MB          3.820s  ##新库耗时时间较短

5.查看迁移数据

#  psql -Uzabbix -dzabbix -h127.0.0.1
> \d items
      Column      |          Type           | Collation | Nullable |          Default          
------------------+-------------------------+-----------+----------+---------------------------
 itemid           | bigint                  |           | not null |
 type             | integer                 |           | not null | 0
 snmp_oid         | character varying(512)  |           | not null | ''::character varying
 hostid           | bigint                  |           | not null |
 name             | character varying(255)  |           | not null | ''::character varying
 ...
 Indexes:
    "items_pkey" PRIMARY KEY, btree (itemid)
    "items_1" btree (hostid, key_)
    "items_3" btree (status)
    "items_4" btree (templateid)
    "items_5" btree (valuemapid)
    "items_6" btree (interfaceid)
    "items_7" btree (master_itemid)
    "items_8" btree (key_)
    "items_9" btree (hostid, name_upper)
迁移MySQL历史数据

# cd /root/migration
# vi data.pgloader  ##过滤掉history和trends七张表,每个大版本的表数量不相同,下面过滤的表请按实际版本中表数量过滤
LOAD DATABASE
FROM mysql://zabbix:*****@127.0.0.1:3306/zabbix
INTO postgresql://zabbix:*****@127.0.0.1:5432/zabbix
WITH include no drop,
no truncate,
create no tables,
create no indexes,
no foreign keys,
reset sequences,
data only
SET maintenance_work_mem TO '1024MB', work_mem TO '512MB'
EXCLUDING TABLE NAMES MATCHING 'acknowledges',
'actions',
'alerts',
'auditlog',
'autoreg_host',
'conditions',
'config',
'config_autoreg_tls',
'corr_condition',
'corr_condition_group',
'corr_condition_tag',
'corr_condition_tagpair',
'corr_condition_tagvalue',
'corr_operation',
'correlation',
'dashboard',
'dashboard_page',
'dashboard_user',
'dashboard_usrgrp',
'dbversion',
'dchecks',
'dhosts',
'drules',
'dservices',
'escalations',
'event_recovery',
'event_suppress',
'event_tag',
'events',
'expressions',
'functions',
'globalmacro',
'globalvars',
'graph_discovery',
'graph_theme',
'graphs',
'graphs_items',
'group_discovery',
'group_prototype',
'ha_node',
'host_discovery',
'host_inventory',
'host_tag',
'hostmacro',
'hosts',
'hosts_groups',
'hosts_templates',
'housekeeper',
'hstgrp',
'httpstep',
'httpstep_field',
'httpstepitem',
'httptest',
'httptest_field',
'httptest_tag',
'httptestitem',
'icon_map',
'icon_mapping',
'ids',
'images',
'interface',
'interface_discovery',
'interface_snmp',
'item_condition',
'item_discovery',
'item_parameter',
'item_preproc',
'item_rtdata',
'item_tag',
'items',
'lld_macro_path',
'lld_override',
'lld_override_condition',
'lld_override_opdiscover',
'lld_override_operation',
'lld_override_ophistory',
'lld_override_opinventory',
'lld_override_opperiod',
'lld_override_opseverity',
'lld_override_opstatus',
'lld_override_optag',
'lld_override_optemplate',
'lld_override_optrends',
'maintenance_tag',
'maintenances',
'maintenances_groups',
'maintenances_hosts',
'maintenances_windows',
'media',
'media_type',
'media_type_message',
'media_type_param',
'module',
'opcommand',
'opcommand_grp',
'opcommand_hst',
'opconditions',
'operations',
'opgroup',
'opinventory',
'opmessage',
'opmessage_grp',
'opmessage_usr',
'optemplate',
'problem',
'problem_tag',
'profiles',
'proxy_autoreg_host',
'proxy_dhistory',
'proxy_history',
'regexps',
'report',
'report_param',
'report_user',
'report_usrgrp',
'rights',
'role',
'role_rule',
'script_param',
'scripts',
'service_alarms',
'service_problem',
'service_problem_tag',
'service_status_rule',
'service_tag',
'services',
'services_links',
'sessions',
'sla',
'sla_excluded_downtime',
'sla_schedule',
'sla_service_tag',
'sysmap_element_trigger',
'sysmap_element_url',
'sysmap_shape',
'sysmap_url',
'sysmap_user',
'sysmap_usrgrp',
'sysmaps',
'sysmaps_element_tag',
'sysmaps_elements',
'sysmaps_link_triggers',
'sysmaps_links',
'tag_filter',
'task',
'task_acknowledge',
'task_check_now',
'task_close_problem',
'task_data',
'task_remote_command',
'task_remote_command_result',
'task_result',
'timeperiods',
'token',
'trigger_depends',
'trigger_discovery',
'trigger_queue',
'trigger_tag',
'triggers',
'users',
'users_groups',
'usrgrp',
'valuemap',
'valuemap_mapping',
'widget',
'widget_field'
ALTER SCHEMA 'zabbix' RENAME TO 'public';

# pgloader data.pgloader  ##只有一台Server监控数据用时较短
2023-06-11T21:00:32.007800+21:00 LOG pgloader version "3.6.7~devel"
2023-06-11T21:00:32.007800+21:00 LOG Migrating from #<MYSQL-CONNECTION mysql://zabbix@127.0.0.1:3306/zabbix {10067B4643}>
2023-06-11T21:00:32.007800+21:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://zabbix@127.0.0.1:5432/zabbix {10067B47D3}>
2023-06-11T21:00:32.007800+21:00 WARNING Source column "public"."history_uint"."value" is casted to type "bigint" which is not the same as "numeric", the type of current target database column "public"."history_uint"."value".
2023-06-11T21:00:32.007800+21:00 WARNING Source column "public"."trends_uint"."value_min" is casted to type "bigint" which is not the same as "numeric", the type of current target database column "public"."trends_uint"."value_min".
2023-06-11T21:00:32.007800+21:00 WARNING Source column "public"."trends_uint"."value_avg" is casted to type "bigint" which is not the same as "numeric", the type of current target database column "public"."trends_uint"."value_avg".
2023-06-11T21:00:32.007800+21:00 WARNING Source column "public"."trends_uint"."value_max" is casted to type "bigint" which is not the same as "numeric", the type of current target database column "public"."trends_uint"."value_max".
2023-06-11T21:00:32.007800+21:00 LOG report summary reset
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
        fetch meta data          0          7                     0.076s
-----------------------  ---------  ---------  ---------  --------------
         public.history          0       8008   286.8 kB          0.110s
    public.history_uint          0       2429    74.1 kB          0.067s
     public.trends_uint          0         50     1.6 kB          0.082s
    public.history_text          0          2    52.6 kB          0.117s
          public.trends          0        142     8.4 kB          0.020s
     public.history_str          0         12     0.9 kB          0.036s
     public.history_log          0          0                     0.025s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.171s
        Reset Sequences          0          0                     0.027s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓      10643   424.5 kB          0.198s

6.查看历史数据迁移

psql -Uzabbix
\c zabbix
zabbix=> select * from history;
 itemid |   clock    |         value          |    ns     
--------+------------+------------------------+-----------
  10073 | 1670483513 |     0.8324606300857088 | 661499763
  10073 | 1670483573 |     1.0157088072329086 | 718082482
  10073 | 1670483693 |     0.8991120422218923 | 907381983
  10073 | 1670483753 |      1.015714184025525 | 963646786
  10073 | 1670483813 |     1.0329172154884476 |  19686404
  10073 | 1670483873 |     1.0158031677606336 |  70690315
  10073 | 1670483933 |     1.0157542389272938 | 124586880
  10073 | 1670483993 |      1.015691219317195 | 182209551
  10073 | 1670484053 |     1.0156428524089065 | 242692284

7.设置主外键

cat schema.sql |tail -n +2090 > altertable.sql > altertable.sql  ##将所有ALTER以及另一段sql放入altertable.sql中
psql -Uzabbix -dzabbix -f database/postgresql/altertable.sql
psql -Uzabbix
\c zabbix
\d+ items
...
Foreign-key constraints:
    "c_items_1" FOREIGN KEY (hostid) REFERENCES hosts(hostid) ON DELETE CASCADE
    "c_items_2" FOREIGN KEY (templateid) REFERENCES items(itemid) ON DELETE CASCADE
    "c_items_3" FOREIGN KEY (valuemapid) REFERENCES valuemap(valuemapid)
    "c_items_4" FOREIGN KEY (interfaceid) REFERENCES interface(interfaceid)
    "c_items_5" FOREIGN KEY (master_itemid) REFERENCES items(itemid) ON DELETE CASCADE

五、Zabbix前端访问测试

1.数据库连接配置

pgloader,Zabbix技术文档,数据库,mysql,postgresql,zabbix,运维开发

2.测试连接成功

pgloader,Zabbix技术文档,数据库,mysql,postgresql,zabbix,运维开发

注:按照实际环境测试环境先熟悉验证,不建议直接生产操作该步骤

博客可能不能及时回复问题,技术问题欢迎加入交流。

具有丰富的模板资源及模板开发能力、项目落地管理经验分享欢迎加入交流

微信号:king_songax文章来源地址https://www.toymoban.com/news/detail-648023.html

到了这里,关于利用pgloader工具将MySQL数据迁移至PostgreSQL数据库的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • PostgreSQL实战-数据库迁移部署

    根据项目需求,我们需要将现有的PostgreSQL数据库重新部署到新的服务器上。由于项目本身就是基于PostgreSQL数据库构建的,因此数据库迁移将变得十分便捷。接下来,我将简要介绍我们的迁移步骤。 备份原数据库 数据库安装的过程我这里就不介绍了,可以看看我之前的文章

    2024年02月15日
    浏览(50)
  • Oracle到PostgreSQL数据库的语法迁移手册(建议收藏)

    异构数据库的迁移(譬如从Oracle迁移到PostgreSQL)工作主要包括三个方面, 数据库对象的迁移,包括库、模式、表、索引、视图、触发器、存储过程等等; 数据内容的迁移,主要指的是数据表中的数据; 数据应用的迁移,主要指的是应用中SQL语句的迁移。 目前对于数据库对

    2024年04月08日
    浏览(74)
  • 数据库信息速递 AWS因迁移PostgreSQL DBaaS而遭遇长时间停机时间而备受诟病

    开头还是介绍一下群,如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。加群请联系 liuaustin3 ,在新加的朋友会分到2群(共840人左右 1 + 2 + 3)新人会进入3群。 亚马逊的云业务告诉用户,它将在2

    2024年02月09日
    浏览(75)
  • mysql数据库迁移

    公司有个项目,刚开始数据量不是大的时候,数据库和服务上的所有应用数据都放在一个旧小盘中,随着项目数据的增长,旧的磁盘被占满了,导致系统无法写入数据,我和同事排查了很长时间,最终确定是磁盘被占满导致的一系列连锁问题。问题找到了,接下来就是想办法

    2024年02月14日
    浏览(47)
  • mysql数据库数据如何迁移目录

    默认位置 C:ProgramDataMySQLMySQL Server 8.0 步骤2中Data文件夹就是mysql存放数据的位置 这里举例移动到E盘下 原来my.ini文件不要修改文件位置,如果修改需要另行学习

    2024年02月07日
    浏览(73)
  • 【数据库迁移系列】从MySQL到openGauss的数据库对象迁移实践

    在之前这一篇中我们分享过使用chameleon工具完成MySQL到openGauss的全量数据复制、实时在线复制。9.30新发布的openGauss 3.1.0版本 ,工具的全量迁移和增量迁移的性能不但有了全面提升,而且支持数据库对象视图、触发器、自定义函数、存储过程的迁移。 本篇就来分享一下使用c

    2024年02月02日
    浏览(61)
  • 如何迁移MySQL数据库?

    迁移MySQL数据库可以采取多种方法,以下是其中几种常用的方法: 备份和还原:将原有的MySQL数据库备份到一个文件中,然后将备份文件还原到新的MySQL服务器中。具体来说,可以使用mysqldump命令进行备份,使用mysql命令进行还原。例如,备份一个名为mydb的数据库可以使用以下

    2024年02月09日
    浏览(53)
  • 数据库迁移之mysql到达梦数据库

    由于业务需求要求数据库国产化,因此将数据从mysql数据库中迁移到国产达梦数据库中。将mysql中的每个库迁移到达梦不同模式下,下面为详细过程。 (1)安装达梦客户端工具 (2)点击选择DM管理工具 (3)创建模式和用户 在用户菜单中,新建一个TEST模式以及相应的用户。

    2024年02月08日
    浏览(61)
  • MySQL数据库迁移(直接复制文件)

    MySQL数据库迁移(直接复制文件) - 简书 (jianshu.com) 看了几种方法: 1、修改注册表的 windows下迁移mysql数据 - 程序员丁先生 - 博客园 (cnblogs.com) 2、mysqldump指令将数据库表/数据保存成xx.sql文件存到本地的 (157条消息) 如何简单实现mysql数据库迁移_岁月呀的博客-CSDN博客 看着都复

    2024年02月15日
    浏览(49)
  • 超全mysql转换postgresql数据库方案

    写在前文: 近期由于公司业务产品发展需要,要求项目逐渐国产化:(1)项目国产操作系统部署;(2)数据库国产化;国产数据库最终选型为highgo(瀚高),该数据库基于pg开发,所以要求先将mysql适配到postgresql数据库; 1.1.1 镜像拉取 dockerhub官网选取自己想安装的版本(

    2024年02月06日
    浏览(63)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包