Mysql迁移至pgsql分享

王不留情
556 阅读16分钟

PostgreSQL与MySQL在选择开源关系数据库管理系统时是一个重要的决定。PostgreSQL和MySQL都是经过时间验证的解决方案，可以与Oracle和SQL Server等企业解决方案竞争。MySQL以其易用性和速度而闻名，而PostgreSQL有许多更高级的功能。下表比较了PostgreSQL与MySQL的功能：

PostgreSQLMySQL
Known as世界上最先进的开源数据库。世界上最流行的开源数据库。
Development开源开源
Pronunciationpost gress queue ellmy ess queue ell
LicensingMIT-style licenseGNU General Public License
Implementation programming languageCC/C++
GUI toolPgAdminMySQL Workbench
ACIDYesYes
Storage engine单存储引擎多存储引擎 InnoDB and MyISAM
Full-text searchYesYes (Limited)
Drop a temporary tableDROP TABLE语句中没有TEMP或TEMPORARY关键字支持DROP TABLE语句中的TEMP或TEMPORARY关键字，该语句只允许删除临时表。
DROP TABLE支持CASCADE选项删除表的依赖对象，例如表和视图。不支持CASCADE选项。
TRUNCATE TABLEPostgreSQL TRUNCATE TABLE支持更多功能，如CASCADE、RESTART IDENTITY、CONTINUE IDENTITY和事务安全等。MySQL TRUNCATE TABLE不支持级联和事务安全，即数据一旦删除，就无法回滚。
Auto increment ColumnSERIALAUTO_INCREMENT
Identity ColumnYesNo
Analytic functionsYesNo
Data typesSupport many advanced types such as array, hstore, and user-defined type.SQL-standard types
Unsigned integerNoYes
Boolean typeYesUse TINYINT(1) internally for Boolean
IP address data typeYesNo
Set default value for a column同时支持常量和函数调用TIMESTAMP或DATETIME列必须是常量或CURRENT_TIMESTAMP
CTEYesYes
EXPLAIN output更详细简单描述
Materialized viewsYesNo
CHECK constraintYesYes
Table inheritanceYesNo
Programming languages for stored proceduresRuby, Perl, Python, TCL, PL/pgSQL, SQL, JavaScript, etc.SQL
FULL OUTER JOINYesNo
INTERSECTYesNo
EXCEPTYesNo
Partial indexesYesNo
Bitmap indexesYesNo
Expression indexesYesNo
Covering indexesYes (since version 9.2)对MySQL支持覆盖索引，允许在不接触表数据的情况下，通过单独扫描索引来检索数据。这在具有数百万行的大型表的情况下是有利的。
Triggers支持可以对大多数类型的命令触发的触发器，但全局影响数据库的触发器除外，例如角色和表空间。仅限于某些命令
PartitioningRANGE, LISTRANGE、LIST、HASH、KEY和使用RANGE或LIST与HASH或KEY子分区组合的复合分区
Task SchedulepgAgentScheduled event
Connection Scalability每个新连接都是一个操作系统进程每个新连接都是一个操作系统线程

  1. PostgreSQL

PostgreSQL 是一种功能强大、具备扩展性的关系型数据库管理系统，注重高级功能和标准的符合性。它提供了许多高级功能，如复杂查询、事务完整性以及多版本并发控制（MVCC），适用于处理复杂的数据和高要求的应用程序。

PostgreSQL的优点

  1. 强大的特性：提供丰富的高级功能，如复杂查询、触发器、存储过程、视图等。
  2. 扩展性：支持自定义函数、数据类型以及自定义扩展，适用于复杂的数据处理需求。
  3. 地理空间功能：提供丰富的地理空间数据类型和功能，适用于地理信息系统（GIS）应用。
  4. 稳定性和安全性：在数据完整性和安全性方面表现出色，提供强大的安全功能。
  5. 高级的并发控制：PostgreSQL 提供多版本并发控制（MVCC），允许多个事务在同时访问和修改同一数据时保持数据的一致性和完整性。这使得 PostgreSQL 在处理高并发和复杂事务时表现出色。

PostgreSQL的缺点

  1. 内存性能：对于每个新的客户端连接，PostgreSQL都会fork一个新的进程。每个新进程会分配大约10MB的内存，对于有大量连接的数据库来说，内存会迅速增加。因此，对于简单的重读操作，PostgreSQL的性能通常不如MySQL等其他rdbms。

什么时候使用PostgreSQL

  1. 数据完整性:PostgreSQL自2001年以来就完全遵循ACID规范，并实现了多版本货币控制以确保数据保持一致，这使得它成为数据完整性至关重要的RDBMS的首选。
  2. 工具集成:PostgreSQL兼容各种编程语言和平台。这意味着，如果需要将数据库迁移到另一个操作系统或与特定工具集成，使用PostgreSQL数据库可能比使用其他DBMS更容易。
  3. 复杂操作:Postgres支持利用多个cpu来以更快的速度回答查询的查询计划。这一点，再加上它对多个并发写入器的强大支持，使它成为数据仓库和在线事务处理等复杂操作的绝佳选择。

  1. MySQL

MySQL 是一种轻量级且易于使用的关系型数据库管理系统，由瑞典 MySQL AB 公司开发。后来被 Sun Microsystems 收购，随后由 Oracle 公司继续开发和维护。MySQL 适用于许多类型的应用程序，尤其是 Web 应用程序，因为它具有良好的性能和稳定性。

MySQL的优点

  1. 易用性：简单易学，适合初学者和小型项目。
  2. 良好的性能：在处理简单查询和事务时表现优秀。
  3. 强大的社区支持：拥有庞大的用户群和活跃的社区，提供丰富的文档和支持。
  4. 多种存储引擎：提供多种存储引擎，如 InnoDB、MyISAM 等，可根据需求选择合适的引擎。
  5. 可扩展性：支持主从复制和分布式部署，便于横向扩展。

MySQL的缺点

  • 并发和大数据量：虽然MySQL通常在繁重的读操作上表现良好，但并发的读写可能会带来问题。如果你的应用程序有很多用户同时写入数据，那么像PostgreSQL这样的RDBMS可能是更好的选择。

  1. 数据类型

MysqlPgsql
分类数据类型描述数据类型描述
数值类型tinyint非常小的整数。这个数值类型的有符号范围是-128到127，而无符号范围是0到255。bigint有符号的8字节整数。
smallint小整数。此数值类型的有符号范围为-32768到32767，而无符号范围为0到65535。bigserial自动递增的8字节整数
mediumint中等大小的整数。此数值数据类型的有符号范围为-8388608到8388607，而无符号范围为0到16777215。double precision8字节双精度浮点数。
int、integer正常大小的整数。此数字数据类型的有符号范围是-2147483648到2147483647，而无符号范围是0到4294967295。integer有符号的4字节整数
bigint大整数。此数字数据类型的有符号范围是-9223372036854775808到9223372036854775807，而无符号范围是0到18446744073709551615。numeric、decimal许多可选择的精度，建议在精确性至关重要的情况下使用，例如货币金额
float小（单精度）浮点数。real4字节单精度浮点数。
double、double precision、real正常大小（双精度）浮点数。。smallint有符号的2字节整数。
dec、decimal、fixed、numeric一个压缩的定点数。此数据类型的条目的显示长度在创建列时定义，并且每个条目都遵循该长度。smallserial自动递增的2字节整数
bool、boolean布尔值是一种只有两个可能值的数据类型，通常是 true 或 false 。serial自动递增的4字节整数。
bit一种位值类型，可以为其指定每个值的位数，从1到64。
字符类型char固定长度的字符串;这种类型的条目在右边用空格填充，以在存储时满足指定的长度。character具有指定的固定长度的字符串。
varchar可变长度但有限的字符串。varchar可变长度但有限的字符串。
binary类似于 char 类型，但它是指定长度的二进制字节串，而不是非二进制字符串character varying可变长度但有限的字符串。
varbinary类似于 varchar 类型，但它是长度可变的二进制字节串，而不是非二进制字符串。text一种长度不受限制的可变字符串。
blob最大数据长度为65535（2^16 - 1）字节的二进制字符串。
tinyblob最大数据长度为255（2^8 - 1）字节的 blob 列。
mediumblob最大数据长度为16777215（2^24 - 1）字节的 blob 列。
longblob最大数据长度为4294967295（2^32 - 1）字节的 blob 列。
text最大长度为65535（2^16 - 1）个字符的字符串。
tinytext最大长度为255（2^8 - 1）个字符的 text 列
mediumtext最大长度为16777215（2^24 - 1）个字符的 text 列。
longtext最大长度为4294967295（2^32 - 1）个字符的 text 列。
enum枚举，它是一个字符串对象，从创建表时声明的值列表中获取单个值。
set与枚举类似，一个字符串对象，可以有零个或多个值，每个值必须从创建表时指定的允许值列表中选择。
日期和时间类型date日期，表示为 YYYY-MM-DD 。date日期，表示为 YYYY-MM-DD 。
datetime显示日期和时间的时间戳，显示为 YYYY-MM-DD HH:MM:SS 。interval时间跨度。
timestamp时间戳，表示自Unix纪元（1970年1月1日00：00：00）以来的时间量。timestamp日期和时间，不包括时区
time一天中的时间，显示为 HH:MM:SS 。time一天中的时间，显示为 HH:MM:SS 。不包括时区
year以2位或4位数格式表示的年份，默认为4位数。timestamptz日期和时间，包括时区
timeptz一天中的时间，显示为 HH:MM:SS 。包括时区
几何类型box平面上的长方形盒子
circle平面上的圆。
line平面上的无限长线
lseg平面上的线段。
path平面上的几何路径。
point平面上的几何点
polygon平面上的封闭几何路径。
网络地址类型cidrIPv4或IPv6网络地址。
inetIPv4或IPv6主机地址。
macaddr介质访问控制（MAC）地址。
位串类型bit固定长度的位串
bit varying一种可变长度的位串。
文本搜索类型tsquery文本搜索查询。
tsvector文本搜索文档。
JSON类型json文本JSON数据。
jsonb分解的二进制JSON数据。
其他数据类型boolean逻辑布尔值，表示 true 或 false 。
bytea是“byte array”的缩写，这种类型用于二进制数据。
money货币数量
pg_lsnPostgreSQL日志序列号。
txid_snapshot用户级事务ID快照。
uuid通用唯一标识符。
xmlXML数据。

  1. 语法与函数

  1. 函数区别

PostgreSQL 函数特点：

  1. 数组处理函数与操作符：提供丰富的数组处理函数和操作符，方便处理和操作数组数据。
  2. 文本搜索函数：支持复杂的文本搜索函数，适用于全文搜索和文本分析需求。
  3. 日期和时间处理函数：具有强大的日期和时间处理函数，包括时区处理、日期运算等功能。
  4. JSONB 函数：提供丰富的 JSONB 数据类型的处理函数，方便 JSON 数据的操作和分析。

MySQL 函数特点：

  1. 基础数组处理函数：提供基础的数组处理函数，适用于简单的数组操作需求。
  2. 基础文本搜索函数：支持基础的文本搜索函数，适用于简单的文本搜索需求。
  3. 基础日期和时间处理函数：具有基础的日期和时间处理函数，但相对于 PostgreSQL 功能较为简化。
  4. JSON 函数：支持基础的 JSON 数据类型的处理函数，但不如 PostgreSQL 中的 JSONB 类型功能丰富。

  1. 语法区别

PostgreSQL 语法特点：

  1. 复杂查询表达式：支持复杂的查询表达式，允许更灵活和复杂的查询操作。
  2. 丰富的窗口函数支持：提供丰富的窗口函数支持，允许更复杂的数据分析操作。
  3. 强大的存储过程和触发器支持：具有强大的存储过程和触发器支持，方便复杂业务逻辑的实现。

MySQL 语法特点：

  1. 简单的查询表达式：使用较为简单的查询表达式，适合基本的查询操作。
  2. 基础窗口函数支持：提供基础的窗口函数支持，但在复杂数据分析方面相对受限。
  3. 基础存储过程和触发器支持：具有基础的存储过程和触发器支持，适合简单业务逻辑的实现。

  1. SQL举例

  1. 复杂数据类型：
-- 创建一个包含 JSONB 数据类型的表
CREATE TABLE products (
    id serial PRIMARY KEY,
    info jsonb
);

-- 插入 JSON 数据
INSERT INTO products (info) VALUES ('{"name": "Product 1", "price": 29.99, "tags": ["tag1", "tag2"]}');

-- 查询包含 JSON 数据的行
SELECT * FROM products WHERE info->>'price' > '20.00';
  1. 高级的并发控制：
-- 开启一个事务
BEGIN;

-- 在事务中更新数据
UPDATE accounts SET balance = balance - 100.00 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE user_id = 2;

-- 提交事务
COMMIT;
  1. 地理空间数据处理：
-- 创建一个包含地理空间数据的表
CREATE TABLE cities (
    city_name text,
    location geography(Point,4326)
);

-- 查询附近的城市
SELECT city_name FROM cities
WHERE ST_DWithin(location, ST_MakePoint(-117.1, 32.7)::geography, 100000);
  1. 自定义函数和扩展：

-- 创建一个自定义函数
CREATE FUNCTION add(integer, integer) RETURNS integer AS $$
BEGINRETURN $1 + $2;
END;$$ LANGUAGE plpgsql;

-- 使用自定义函数
SELECT add(2, 3);
  1. 全文搜索功能：

-- 创建一个全文搜索索引
CREATE INDEX idx_fts ON documents USING GIN (to_tsvector('english', content));

-- 执行全文搜索
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'sea')

Mysql数据库迁移至pgsql

  1. 拉取 PostgreSQL 镜像：使用以下命令从 Docker Hub 上拉取 PostgreSQL 镜像：

建议使用12版本，更高版本有部分字段被移除，一些终端应用打不开连接。

docker pull postgres:12

这将拉取 PostgreSQL 12 版本的官方镜像到本地系统。

  1. 创建数据卷

创建一个数据卷以持久化PostgreSQL的数据

docker volume create postgre-data
  1. 创建并运行容器

使用以下命令运行 PostgreSQL 容器：

docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=mypassword-d postgres:12

  1. 连接容器
docker exec -it postgres psql -U postgres
  1. 验证安装

连接容器后在shell中输入

select version();

安装成功。

注：如果外部应用连接请联系运维开启5432端口，使外部应用可以通过防火墙。

## 开启端口，正式环境使用请知会运维
ptables -A INPUT -p tcp --dport 5432 -j ACCEPT
ufw allow 5432

使用Navicat连接数据库时存在访问权限限制需要修改pg_hba.conf 文件中用户 "postgres" 的访问权限设置

## 进入pgsql容器找到pg_hba.conf文件，
## "local" is for Unix domain socket connections only
local   all             all                                     trust
## IPv4 local connections:
host    all             all             127.0.0.1/32            trust
## IPv6 local connections:
host    all             all             ::1/128                 trust
## Allow replication connections from localhost, by a user with the
## replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all md5

修改 host all all all md5为 host all all all trust

  1. 数据迁移工具（Pgloader）

  1. 从源代码安装 最新版本pgloader

这里有坑，使用docker直接拉取的镜像版本存在严重bug，导致迁移无法进行，所以需要下载Git_hub上最新源码，自己编译安装。

git clone https://github.com/dimitri/pgloader.git
cd pgloader
## 编译工具
(sudo apt-get update
sudo apt-get install sbcl)
make
sudo make install
##安装完成之后，找到执行文件位置，将pgloader执行文件复制到/usr/bin目录下
find . -type f -executable -iname "pgloader*"

mv /pgloader/build/bin/pgloader /usr/bin/pgloader

pgloader安装完成

  1. 备份mysql数据

在迁移前需确保数据库数据已备份，防止在迁移过程中出错导致数据丢失

mysqldump -u username -p --all-databases > backup.sql

  1. 数据迁移

安装好pgsql，pgloader， 备份好数据库数据后开始迁移数据库数据

Pgloader提供两种迁移方式

  1. 基础版（使用默认配置迁移）

## mysql2pgsql
pgloader mysql://mysqluser:password@myhost:3306/dbname pgsql://pguser:password@pghost:5432/dbname

坑: password中如果有@等特殊符号需要double@转义，建议使用不带特殊字符的密码来连接数据库，或者新建一个临时的用户用来迁移。

  1. 高级可配置选项（使用高级选项加载命令文件）

  1. 创建命令行配置文件
## 找到当前工作目录
pwd pgloader 
## 在工作目录下创建my.load配置文件来指定加载命令
vim my.load
  1. 编写脚本文件

以下脚本仅供参考

请根据实际需要情况，参考pgloader官网文档指导编写命令行文件

官网地址：pgloader.readthedocs.io/en/latest/r…

LOAD DATABASE
     FROM      mysql://u:p@localhost:3306/egw
     INTO postgresql://u:p@localhost:5432/egw

 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'
      
  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 int8 to integer using conversion-function,
      type year to integer;

脚本命令说明可以在官网查看

在执行前在pgsql中需手动创建target数据库。

  1. 迁移前准备

修改mysql配置文件my.conf，将默认验证方式开启，否则mysql数据库会连不上，迁移完成之后再关闭

## 查看mysql容器名
docker ps -a 
## 进入mysql容器
docker exec -it 容器名称 bash
## 找到mysql配置文件位置
find / -name "my.cnf"
## 如果容器内没有安装编辑器可以将文件复制到宿主机上修改完再复制到容器内
docker cp 容器id:path 本机path
## 修改my.cnf文件 [mysqld] default_authentication_plugin=mysql_native_password
docker cp 本机path 容器id:path
  1. 执行文件
pgloader my.load

avatar
文章
阅读
粉丝