PostgreSQL 异构数据库迁移

迁移评估与规划

源数据库分析

在进行异构数据库迁移之前，需要对源数据库进行全面的分析。这包括了解源数据库的版本、架构、数据量、表结构、索引、视图、存储过程、触发器等对象信息。同时，还需要评估源数据库中使用的特定数据库功能和SQL扩展，以便制定合适的迁移方案。

源数据库分析应该涵盖以下内容：

数据库版本和配置信息
数据量和增长趋势
表结构和关系
索引和约束
视图和物化视图
存储过程和函数
触发器和事件
用户和权限
性能特征和优化配置

兼容性评估

兼容性评估是迁移规划中的关键步骤，用于识别源数据库和目标PostgreSQL之间的差异和不兼容之处。评估结果将直接影响迁移方案的选择和所需的工作量。

sql

-- 分析Oracle源数据库的存储过程
SELECT object_type, COUNT(*) 
FROM user_objects 
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')
GROUP BY object_type;

-- 识别使用的特定数据类型
SELECT data_type, COUNT(*) 
FROM user_tab_columns 
WHERE data_type NOT IN ('VARCHAR2', 'NUMBER', 'DATE', 'CHAR', 'CLOB') 
GROUP BY data_type;

兼容性评估应该生成详细的报告，包括需要转换的SQL语句、无法直接迁移的功能、可能需要重构的代码等信息。

迁移策略选择

根据评估结果和业务需求，可以选择不同的迁移策略：

直接迁移：适用于兼容性较高的情况，可以直接使用迁移工具进行转换
逐步迁移：分阶段进行迁移，先迁移简单对象，再迁移复杂对象
重构迁移：对源数据库进行重构，消除不兼容之处后再进行迁移
混合迁移：结合多种方法，对不同类型的对象采用不同的迁移策略

选择迁移策略时需要考虑迁移风险、时间成本、资源投入和业务影响等因素。

数据类型映射

Oracle到PostgreSQL映射

Oracle和PostgreSQL之间存在许多数据类型差异，需要进行适当的映射转换：

Oracle数据类型	PostgreSQL数据类型	说明
VARCHAR2	VARCHAR	最大长度相同
NUMBER(p,s)	NUMERIC(p,s)	精度和标度保持
DATE	TIMESTAMP	Oracle DATE包含时间，PostgreSQL TIMESTAMP更精确
CLOB	TEXT	大文本对象
BLOB	BYTEA	二进制大对象
RAW	BYTEA	二进制数据
ROWID	OID	行标识符
INTERVAL DAY TO SECOND	INTERVAL	时间间隔
TIMESTAMP WITH TIME ZONE	TIMESTAMP WITH TIME ZONE	带时区的时间戳

MySQL到PostgreSQL映射

MySQL和PostgreSQL的数据类型映射如下：

MySQL数据类型	PostgreSQL数据类型	说明
TINYINT	SMALLINT	小整数
MEDIUMINT	INTEGER	中等整数
BIGINT	BIGINT	大整数
TEXT	TEXT	大文本
MEDIUMTEXT	TEXT	中等文本
LONGTEXT	TEXT	长文本
TINYTEXT	TEXT	小文本
DATETIME	TIMESTAMP	日期时间
YEAR	SMALLINT	年份
ENUM	TEXT或CHECK约束	枚举类型
SET	TEXT或数组	集合类型
FLOAT	REAL	单精度浮点
DOUBLE	DOUBLE PRECISION	双精度浮点
DECIMAL	NUMERIC	精确数值

SQL Server到PostgreSQL映射

SQL Server和PostgreSQL的数据类型映射：

SQL Server数据类型	PostgreSQL数据类型	说明
VARCHAR(MAX)	TEXT	大文本
NVARCHAR(MAX)	TEXT	Unicode文本
DATETIME	TIMESTAMP	日期时间
DATETIME2	TIMESTAMP	高精度日期时间
SMALLDATETIME	TIMESTAMP	小日期时间
INT	INTEGER	整数
BIGINT	BIGINT	大整数
TINYINT	SMALLINT	小整数
BIT	BOOLEAN	布尔值
MONEY	NUMERIC(19,4)	货币
SMALLMONEY	NUMERIC(10,4)	小货币
ROWVERSION	BYTEA	行版本
UNIQUEIDENTIFIER	UUID	唯一标识符

迁移工具与方法

Ora2pg工具

Ora2pg是Oracle到PostgreSQL迁移的常用工具，可以自动转换数据库模式、数据和存储过程。

bash

# 安装Ora2pg
apt-get install ora2pg

# 配置文件
cat > /etc/ora2pg/ora2pg.conf << EOF
ORACLE_HOME /opt/oracle/product/19c/dbhome_1
ORACLE_DSN dbi:Oracle:host=oracle_server;sid=oracle_sid
ORACLE_USER oracle_user
ORACLE_PASSWORD oracle_password

SCHEMA oracle_schema
TYPE TABLE,VIEW,SEQUENCE,INDEX,FUNCTION,PROCEDURE,PACKAGE,TRIGGER,GRANT
OUTPUT_DIR /path/to/output
FILE_PREFIX migration_
EOF

# 执行迁移
ora2pg -c /etc/ora2pg/ora2pg.conf --project_base /path/to/project --init_project my_project

Ora2pg支持多种迁移类型，可以通过配置文件和命令行参数进行详细控制。迁移过程中生成的SQL脚本需要仔细审查和调整，以确保符合PostgreSQL的最佳实践。

pg_chameleon工具

pg_chameleon是MySQL到PostgreSQL的迁移工具，支持实时复制和批量迁移两种模式。

bash

# 安装pg_chameleon
pip3 install pg_chameleon

# 初始化配置
chameleon set_configuration_files --config my_mysql2pg
cat > ~/.pg_chameleon/my_mysql2pg/config.yaml << EOF
source:
  host: mysql_server
  port: 3306
  user: mysql_user
  password: mysql_password
  charset: utf8mb4
  schema_mappings:
    source_schema: target_schema

target:
  host: pg_server
  port: 5432
  user: pg_user
  password: pg_password
  database: target_db
EOF

# 创建复制实例
chameleon create_replica_schema --config my_mysql2pg
chameleon start_replication --config my_mysql2pg --source mysql_db

pg_chameleon的实时复制模式可以最小化迁移过程中的停机时间，适用于对业务连续性要求较高的场景。

AWS DMS服务

AWS Database Migration Service（DMS）是云端的数据库迁移服务，支持多种数据库之间的迁移。

bash

# 使用AWS CLI配置DMS
aws dms create-endpoint \
    --endpoint-identifier mysql-source \
    --endpoint-type source \
    --engine-name mysql \
    --username mysql_user \
    --password mysql_password \
    --server-name mysql_server \
    --port 3306

aws dms create-endpoint \
    --endpoint-identifier pg-target \
    --endpoint-type target \
    --engine-name postgres \
    --username pg_user \
    --password pg_password \
    --server-name pg_server \
    --port 5432 \
    --database-name target_db

aws dms create-replication-task \
    --replication-task-identifier mysql-to-pg \
    --source-endpoint-arn mysql-source-arn \
    --target-endpoint-arn pg-target-arn \
    --migration-type full-load-and-cdc \
    --table-mappings file://table_mappings.json

AWS DMS支持全量加载和变更数据捕获（CDC）两种模式，可以根据业务需求选择合适的迁移方式。

SQL语句转换

常用语法差异

不同数据库系统的SQL语法存在差异，需要进行相应的转换：

字符串连接

sql

-- Oracle/MySQL
SELECT CONCAT(col1, col2) FROM table;
SELECT col1 || col2 FROM table;

-- PostgreSQL
SELECT col1 || col2 FROM table;
SELECT CONCAT(col1, col2) FROM table;

日期函数

sql

-- Oracle
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

-- PostgreSQL
SELECT CURRENT_DATE;
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD');

分页查询

sql

-- MySQL
SELECT * FROM table LIMIT 10 OFFSET 20;

-- PostgreSQL
SELECT * FROM table LIMIT 10 OFFSET 20;
SELECT * FROM table ORDER BY id LIMIT 10 OFFSET 20;

行号

sql

-- Oracle
SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn FROM table;

-- PostgreSQL
SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn FROM table;

存储过程转换

存储过程的转换是异构数据库迁移中最复杂的部分。不同数据库系统的存储过程语法差异较大，需要仔细转换。

sql

-- Oracle存储过程示例
CREATE OR REPLACE PROCEDURE get_employee(
    p_emp_id IN NUMBER,
    p_name OUT VARCHAR2,
    p_salary OUT NUMBER
) AS
BEGIN
    SELECT name, salary INTO p_name, p_salary
    FROM employees
    WHERE employee_id = p_emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name := NULL;
        p_salary := NULL;
END;
/

-- 转换为PostgreSQL函数
CREATE OR REPLACE FUNCTION get_employee(
    p_emp_id INTEGER,
    OUT p_name VARCHAR,
    OUT p_salary NUMERIC
) AS $$
BEGIN
    SELECT name, salary INTO p_name, p_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    IF NOT FOUND THEN
        p_name := NULL;
        p_salary := NULL;
    END IF;
END;
$$ LANGUAGE plpgsql;

转换存储过程时，需要注意异常处理机制、参数传递方式、变量声明语法等方面的差异。

迁移测试与验证

数据验证

迁移完成后，需要对数据进行全面验证，确保数据的完整性和准确性。

sql

-- 验证数据行数
SELECT 'source_table' AS table_name, COUNT(*) AS row_count FROM source_table
UNION ALL
SELECT 'target_table', COUNT(*) FROM target_table;

-- 验证关键数据
SELECT id, name, amount FROM source_table
EXCEPT
SELECT id, name, amount FROM target_table;

-- 验证汇总数据
SELECT SUM(amount) AS total_amount FROM source_table
UNION ALL
SELECT SUM(amount) FROM target_table;

数据验证应该覆盖所有关键表和关键字段，包括主键、外键、唯一约束、检查约束等。

功能测试

迁移后的数据库需要进行完整的功能测试，确保应用可以正常工作。

bash

# 执行功能测试脚本
psql -h pg_server -d target_db -U test_user -f /path/to/test_scripts/functional_tests.sql

# 运行应用测试套件
npm test --prefix /path/to/application

功能测试应该覆盖所有业务场景，包括增删改查操作、事务处理、并发控制等。

性能测试

性能测试验证迁移后的数据库是否满足性能要求。

sql

-- 执行性能测试查询
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM large_table WHERE condition = 'value'
ORDER BY created_at DESC
LIMIT 100;

-- 并发性能测试
SELECT pg_prepare('test_query', 'SELECT * FROM table WHERE id = $1');
DO $$
BEGIN
    FOR i IN 1..100 LOOP
        PERFORM test_query(i);
    END LOOP;
END;
$$;

性能测试应该在接近生产环境的条件下进行，包括数据量、并发用户数、硬件配置等方面。

回滚与应急方案

回滚策略

制定完善的回滚策略是确保迁移安全的重要保障。回滚策略应该包括回滚触发条件、回滚步骤、回滚时间预估和验证方法等内容。

bash

# 创建回滚脚本
cat > /path/to/rollback.sh << EOF
#!/bin/bash
# 回滚脚本

# 停止应用
systemctl stop application

# 切换数据库连接回源库
# (修改应用配置文件中的数据库连接)

# 验证源数据库
psql -h source_db -U app_user -c "SELECT COUNT(*) FROM critical_table;"

# 启动应用
systemctl start application

echo "回滚完成"
EOF

chmod +x /path/to/rollback.sh

回滚脚本应该经过充分测试，确保在需要时可以快速执行。

应急处理

迁移过程中可能遇到各种意外情况，需要制定相应的应急处理方案：

源数据库故障时的处理
目标数据库损坏时的处理
数据不一致时的处理
性能严重下降时的处理
网络中断时的处理

每种应急情况都应该有明确的处理流程和责任人。

常见问题与解决方案

字符集问题

异构数据库迁移经常遇到字符集相关的问题。解决字符集问题的关键是确保源数据库和目标数据库使用兼容的字符集设置。

sql

-- 检查PostgreSQL数据库字符集
SHOW server_encoding;
SHOW client_encoding;

-- 设置客户端字符集
SET client_encoding = 'UTF8';

-- 转换字符集
SELECT column_name::bytea FROM table_name;

在迁移前应该详细检查源数据库的字符集配置，并在必要时进行字符集转换。

性能下降

迁移后可能出现性能下降的情况。常见的解决方法包括调整PostgreSQL配置参数、优化查询语句、重建索引等。

sql

-- 分析慢查询
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- 优化查询计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM table WHERE complex_condition;

兼容性错误

迁移过程中可能遇到各种兼容性错误。解决兼容性错误的方法包括修改SQL语句、调整数据类型、重写业务逻辑等。

常见问题（FAQ）

Q1: 异构数据库迁移的停机时间如何估算？

A1: 停机时间取决于数据量、网络带宽、迁移方法等因素。使用实时复制技术可以将停机时间降到最低，但需要额外的时间进行数据同步和验证。建议预留足够的停机时间窗口，并准备应急预案。

Q2: 如何处理源数据库中的自定义函数？

A2: 自定义函数需要根据目标数据库的语法进行重写。建议先评估函数的复杂度和数量，制定详细的转换计划。对于复杂函数，可以考虑使用应用层逻辑替代或简化。

Q3: 迁移过程中如何保证数据一致性？

A3: 保证数据一致性的方法包括使用事务、锁机制、校验和验证等。在批量迁移时，应该分批次进行并验证每批数据的完整性。迁移完成后进行全面的一致性检查。

Q4: 迁移后是否需要重建索引？

A4: 迁移过程中创建的索引可能不是最优的。建议在数据迁移完成后重新创建索引，根据实际查询模式优化索引设计。对于大型表，可以考虑使用CONCURRENTLY选项避免阻塞。

Q5: 如何验证迁移后的查询性能？

A5: 验证查询性能的方法包括：

对比迁移前后的执行计划
执行相同的工作负载测试
收集慢查询日志进行分析
进行压力测试验证并发性能

Q6: 迁移工具生成的代码需要人工审核吗？

A6: 是的，迁移工具生成的代码需要人工审核和调整。自动转换可能无法覆盖所有情况，特别是复杂的业务逻辑和特定数据库功能。建议由熟悉源数据库和目标数据库的专家进行代码审核。

Q7: 如何处理跨数据库的分布式事务？

A7: 跨数据库的分布式事务在迁移后需要重新设计。可以考虑使用两阶段提交、消息队列或应用层补偿机制来处理分布式事务问题。设计新的事务处理方案时需要充分考虑业务需求和性能影响。

PostgreSQL 异构数据库迁移 ​

迁移评估与规划 ​

源数据库分析 ​

兼容性评估 ​

迁移策略选择 ​

数据类型映射 ​

Oracle到PostgreSQL映射 ​

MySQL到PostgreSQL映射 ​

SQL Server到PostgreSQL映射 ​

迁移工具与方法 ​

Ora2pg工具 ​

pg_chameleon工具 ​

AWS DMS服务 ​

SQL语句转换 ​

常用语法差异 ​

存储过程转换 ​

迁移测试与验证 ​

数据验证 ​

功能测试 ​

性能测试 ​

回滚与应急方案 ​

回滚策略 ​

应急处理 ​

常见问题与解决方案 ​

字符集问题 ​

性能下降 ​

兼容性错误 ​

常见问题（FAQ） ​

Q1: 异构数据库迁移的停机时间如何估算？ ​

Q2: 如何处理源数据库中的自定义函数？ ​

Q3: 迁移过程中如何保证数据一致性？ ​

Q4: 迁移后是否需要重建索引？ ​

Q5: 如何验证迁移后的查询性能？ ​

Q6: 迁移工具生成的代码需要人工审核吗？ ​

Q7: 如何处理跨数据库的分布式事务？ ​

PostgreSQL 异构数据库迁移

迁移评估与规划

源数据库分析

兼容性评估

迁移策略选择

数据类型映射

Oracle到PostgreSQL映射

MySQL到PostgreSQL映射

SQL Server到PostgreSQL映射

迁移工具与方法

Ora2pg工具

pg_chameleon工具

AWS DMS服务

SQL语句转换

常用语法差异

存储过程转换

迁移测试与验证

数据验证

功能测试

性能测试

回滚与应急方案

回滚策略

应急处理

常见问题与解决方案

字符集问题

性能下降

兼容性错误

常见问题（FAQ）

Q1: 异构数据库迁移的停机时间如何估算？

Q2: 如何处理源数据库中的自定义函数？

Q3: 迁移过程中如何保证数据一致性？

Q4: 迁移后是否需要重建索引？

Q5: 如何验证迁移后的查询性能？

Q6: 迁移工具生成的代码需要人工审核吗？

Q7: 如何处理跨数据库的分布式事务？