数据库迁移 MySQL -> Postgres
数据库迁移 MySQL -> Postgres
MySQL to PostgreSQL Types Mapping
Types Mapping
MySQL PostgreSQL BIGINT BIGINT BINARY(n) BYTEA BIT BOOLEAN CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n) DATE DATE DATETIME TIMESTAMP [WITHOUT TIME ZONE] DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s) DOUBLE DOUBLE PERCISION FLOAT REAL INT, INTEGER INT, INTEGER MEDIUMINT INTEGER NUMERIC(p,s) NUMERIC(p,s) SMALLINT SMALLINT TINYINT SMALLINT TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT TEXT TIME TIME [WITHOUT TIME ZONE] TIMESTAMP TIMESTAMP [WITHOUT TIME ZONE] VARBINARY(n), VARBINARY(max) BYTEA VARCHAR(n) VARCHAR(n) VARCHAR(max) TEXT MySQL auto_increment
MySQL PostgreSQL BIGINT AUTO_INCREMENT BIGSERIAL INTEGER AUTO_INCREMENT SERIAL SMALLINT AUTO_INCREMENT SMALLSERIAL TINYINT AUTO_INCREMENT SMALLSERIAL MySQL integer types (tinyint, smallint, int, bigint) UNSIGNED
MySQL PostgreSQL BIGINT UNSIGNED NUMERIC(20) INT UNSIGNED BIGINT MEDIUMINT UNSIGNED INTEGER SMALLINT UNSIGNED INTEGER TINYINT UNSIGNED INTEGER
使用pgloader将MySQL数据库迁移到PostgreSQL
PGLOADER
Supported operations include
- Migrate from MySQL to PostgreSQL
- Migrate from SQLite to PostgreSQL
- Migrate from MS SQL Server to PostgreSQL
Install
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20> pgloader使用postgresql的COPY命令将数据从源数据库或文件复制到目标PostgreSQL数据库中.
- sbcl: 一个Common Lisp编译器
- unzip: .zip文件的unzip器
- libsqlite3-dev: SQLite3的开发文件集合
- gawk: GNU awk 的缩写，是一种模式扫描和处理语言
- curl: 用于从URL传输数据的命令行工具
- make: 用于管理包编译的实用程序
- freetds-dev: MS SQL和Sybase数据库的客户端库
- libzip-dev:用于读取，穿件和修改zip存档的库
# 安装依赖项
$ sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev
# 下载pgloader
$ wget https://github.com/dimitri/pgloader/releases/download/v3.6.2/pgloader-bundle-3.6.2.tgz
# 提取tarball
$ tar -xvf pgloader-bundle
# 构建pgloader二进制文件
# make pgloaderMigrate from MySQL to PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95> Migrate a whole MySQL database, including its schema definition of tables and indexes, primary key and foreign key constraints, comments and default values, even when they require installing a trigger in PostgreSQL.
chyiyaqing in ~ at chyiyaqing-PowerEdge-R720
➜ pgloader mysql://admin:x@172.16.1.129:31540/stdb pgsql://kingbasees:x@180.76.152.26:5432/stdb
2021-10-29T18:31:21.028000+08:00 LOG pgloader version "3.6.2"
2021-10-29T18:31:21.453000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://admin@172.16.1.129:31540/stdb {10054E05A3}>
2021-10-29T18:31:21.453000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://kingbasees@180.76.152.26:5432/stdb {10056315A3}>
2021-10-29T18:32:49.847000+08:00 LOG report summary reset
table name errors rows bytes total time
---------------------------- --------- --------- --------- --------------
fetch meta data 0 159 1.411s
Create Schemas 0 0 0.253s
Create SQL Types 0 0 0.152s
Create tables 0 134 25.585s
Set Table OIDs 0 67 0.080s
---------------------------- --------- --------- --------- --------------
stdb.access_key 0 6 1.6 kB 0.588s
stdb.alert_history 0 84369 28.7 MB 28.544s
stdb.analysis_result 0 0 6.861s
stdb.auth_group 0 2 0.1 kB 7.222s
stdb.baas_contract_cluster 0 0 7.661s
stdb.cache 0 300 49.2 kB 8.278s
stdb.auth_client 0 0 2.851s
stdb.certification 0 0 8.631s
stdb.auth_history 0 20 1.6 kB 3.268s
stdb.chain_account 0 0 8.968s
stdb.baas_contract_warehouse 0 0 3.595s
stdb.config_info 0 0 9.251s
stdb.cert 0 0 4.014s
stdb.contract_entity 0 1 0.5 kB 9.452s
stdb.chain 0 163 118.5 kB 4.337s
stdb.contract_invoke_record 0 0 9.598s
stdb.chain_transaction 0 0 4.221s
stdb.contract_market_info 0 25 3.5 kB 9.774s
stdb.contract_address 0 0 4.065s
stdb.contract_template 0 129 79.5 kB 10.058s
stdb.contract_info 0 8 1.0 kB 4.046s
stdb.credential 0 41 48.5 kB 10.322s
stdb.contract_market 0 16 37.8 kB 4.197s
stdb.driver 0 14 94.6 kB 10.825s
stdb.contract_sample 0 664 9.2 MB 14.125s
stdb.file_system_entity 0 0 11.247s
stdb.gas_distribute 0 0 11.666s
stdb.grade_one 0 664 64.3 kB 11.957s
stdb.contract_version_info 0 1 0.1 kB 3.688s
stdb.debug_properties 0 0 4.150s
stdb.err_event 0 0 4.543s
stdb.hibernate_sequence 0 1 0.0 kB 11.902s
stdb.form 0 0 5.003s
stdb.instance_event 0 0 12.349s
stdb.grade_group 0 5 1.1 kB 5.484s
stdb.integral_account 0 0 12.779s
stdb.grpc_event 0 4002 894.1 kB 9.082s
stdb.java_file_entity 0 0 13.242s
stdb.license_history 0 0 13.717s
stdb.instance 0 70 122.2 kB 9.365s
stdb.log_operate 0 439 103.6 kB 14.198s
stdb.integral 0 0 9.765s
stdb.monitor_indicators 0 52 10.0 kB 14.635s
stdb.integral_transaction 0 0 10.234s
stdb.notice 0 270 385.6 kB 15.572s
stdb.java_project_entity 0 0 10.688s
stdb.log_file 0 0 11.105s
stdb.organization 0 34 8.1 kB 15.521s
stdb.monitor_alert 0 14 11.2 kB 11.741s
stdb.panel 0 169 7.5 kB 15.474s
stdb.node 0 333 150.5 kB 12.311s
stdb.renewal 0 0 15.791s
stdb.role 0 6 7.5 kB 15.830s
stdb.order_resource_ship 0 0 12.207s
stdb.rule_type 0 11 1.1 kB 15.752s
stdb.output 0 0 12.650s
stdb.secretkey 0 48 23.2 kB 16.119s
stdb.prometheus 0 283 56.8 kB 12.608s
stdb.third_contract_market 0 2 0.4 kB 16.107s
stdb.report_id_entity 0 5 0.1 kB 13.235s
stdb.user_setting 0 7 0.5 kB 16.055s
stdb.rule 0 39 13.0 kB 13.143s
stdb.verification_result 0 0 16.398s
stdb.sample_grade_model 0 0 13.639s
stdb.subscription 0 0 13.503s
stdb."user" 0 34 29.2 kB 14.018s
stdb.user_use_gas 0 0 13.895s
---------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 28.540s
Create Indexes 0 92 19.382s
Index Build Completion 0 92 1.384s
Reset Sequences 0 16 0.746s
Primary Keys 0 66 12.146s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 14 2.716s
Set Search Path 0 1 0.306s
Install Comments 0 74 13.552s
---------------------------- --------- --------- --------- --------------
Total import time ✓ 92247 40.2 MB 1m18.772s
使用pgbench测试PostgreSQL
- pgbench
Mysql SQL 迁移 Postgres
1 | # Limit 用法 |
路上遇到的坑
- PostgreSQL列名区分大小写
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30blocface=# \d public.alert
Table "public.alert"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('alert_id_seq'::regclass)
time_create | timestamp without time zone | | |
time_update | timestamp without time zone | | |
time_delete | timestamp without time zone | | |
name | character varying(255) | | |
mode | integer | | |
condition | integer | | |
silence | integer | | |
user_id | character varying(64) | | |
group_id | character varying(64) | | |
viewer | character varying(32) | | |
isDeleted | integer | | | 0
Indexes:
"alert_pkey" PRIMARY KEY, btree (id)
"IDX_alert_time_delete" btree (time_delete)
# 所有未双引号的标识符(包括列名)在PostgreSQL中被折叠为小写，用双引号创建的列名并因此保留了大写字母，必须在余下的时间将双引号括起来("first_Name")
blocface=# SELECT isDeleted from public.alert;
ERROR: column "isdeleted" does not exist
LINE 1: SELECT isDeleted from public.alert;
^
HINT: Perhaps you meant to reference the column "alert.isDeleted".
blocface=# SELECT "isDeleted" from public.alert;
isDeleted
-----------
(0 rows) - PostgreSQL Duplicate Key Violates unique constraint
1
2
3
4
5
6
7
8
9
10
11
12
13Q: the sequence was out of sync and no more records
A: the sequence must be synchronized mannually.
The name of the sequence can be determined with teh command PG_GET_SERIAL_SEQUENCE
# Command to get the sequence name:
# the table name must be in double quotes, surrounded by single quotes
> SELECT PG_GET_SERIAL_SEQUENCE('"foo"', 'foo_id')
# Validate, the sequence is out-of-sync
# When the Current Value is less than Max Value, your sequence is out-of-sync
> SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('"foo"', 'foo_id')) AS "Current Value", MAX("foo_id") AS "Max Value" FROM "foo";
# Correction
> SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"foo"', 'foo_id')), (SELECT (MAX("foo_id") + 1) FROM "foo"), FALSE);