PostgreSQL 从其他数据库迁移

将数据从其他数据库迁移到 PostgreSQL 是一个常见的需求，可能是为了降低成本、获得更好的性能或利用 PostgreSQL 的高级特性。不同数据库之间的迁移需要考虑数据类型差异、SQL 语法差异、架构差异和应用兼容性等因素。本文将详细介绍从主流数据库（MySQL、Oracle、SQL Server）迁移到 PostgreSQL 的方法、步骤和最佳实践。

迁移前准备

1. 源数据库评估

在开始迁移前，需要对源数据库进行全面评估，了解其结构、数据量和应用依赖。

数据库结构评估

sql

-- MySQL: 查看数据库结构
SHOW DATABASES;
USE dbname;
SHOW TABLES;
DESCRIBE tablename;

-- Oracle: 查看数据库结构
SELECT table_name FROM user_tables;
DESCRIBE tablename;

-- SQL Server: 查看数据库结构
SELECT name FROM sys.databases;
USE dbname;
SELECT name FROM sys.tables;
EXEC sp_columns tablename;

数据量评估

sql

-- MySQL: 查看数据量
SELECT table_schema, table_name, data_length + index_length AS total_size
FROM information_schema.tables
WHERE table_schema = 'dbname'
ORDER BY total_size DESC;

-- Oracle: 查看数据量
SELECT table_name, num_rows
FROM user_tables
ORDER BY num_rows DESC;

-- SQL Server: 查看数据量
SELECT t.name AS table_name, SUM(p.rows) AS row_count
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE t.name NOT LIKE 'sys%'
GROUP BY t.name
ORDER BY row_count DESC;

应用依赖评估

分析应用程序的数据库访问模式
确定应用程序的性能要求
检查应用程序与数据库的兼容性
识别应用程序中使用的特定数据库特性

2. 目标环境准备

安装 PostgreSQL

根据操作系统安装合适版本的 PostgreSQL，建议使用最新的稳定版本。

配置 PostgreSQL

修改 postgresql.conf 和 pg_hba.conf 文件，根据实际需求配置参数：

ini

# postgresql.conf
listen_addresses = '*'  # 允许远程连接
max_connections = 200  # 根据需求调整
shared_buffers = 4GB   # 通常设置为系统内存的 25%
work_mem = 16MB        # 根据需求调整
maintenance_work_mem = 256MB  # 根据需求调整

ini

# pg_hba.conf
# 允许从特定IP连接
host all all 192.168.1.0/24 md5

安装必要的扩展

sql

-- 安装常用扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

3. 迁移工具选择

迁移工具	支持的源数据库	特点	适用场景
pgloader	MySQL, SQLite, CSV	开源，速度快，支持并行迁移	从MySQL、SQLite迁移
ora2pg	Oracle	开源，专门用于Oracle迁移，支持复杂对象	从Oracle迁移
sqlserver2pgsql	SQL Server	开源，转换SQL Server架构和数据	从SQL Server迁移
AWS DMS	多种数据库	云服务，支持全量和增量迁移	云环境迁移
Azure DMS	多种数据库	云服务，支持全量和增量迁移	Azure环境迁移
Foreign Data Wrapper	多种数据库	允许直接访问其他数据库	数据同步和集成

从 MySQL 迁移到 PostgreSQL

1. 使用 pgloader 迁移

pgloader 是一个开源的迁移工具，专门用于将数据从其他数据库迁移到 PostgreSQL，支持 MySQL、SQLite、CSV 等数据源。

安装 pgloader

bash

# Ubuntu/Debian
sudo apt-get install pgloader

# CentOS/RHEL
sudo yum install pgloader

# macOS
brew install pgloader

创建迁移配置文件

创建 mysql2pg.load 文件：

yaml

LOAD DATABASE
     FROM mysql://user:password@mysql_host/mysql_db
     INTO postgresql://user:password@pg_host/pg_db

 WITH include drop, create tables, create indexes, reset sequences,
      workers = 4, concurrency = 10

 CAST type datetime to timestamp with time zone drop default drop not null,
      type timestamp to timestamp with time zone drop default drop not null,
      type tinyint to boolean using tinyint-to-boolean,
      type int unsigned to bigint,
      type varchar to text,
      type mediumtext to text,
      type longtext to text,
      type blob to bytea,
      type tinyblob to bytea,
      type mediumblob to bytea,
      type longblob to bytea;

执行迁移

bash

pgloader mysql2pg.load

2. 使用 MySQL Foreign Data Wrapper 迁移

MySQL Foreign Data Wrapper 允许 PostgreSQL 直接访问 MySQL 数据库，可以用于数据同步和迁移。

安装 MySQL Foreign Data Wrapper

bash

# Ubuntu/Debian
sudo apt-get install postgresql-14-mysql-fdw

# CentOS/RHEL
sudo yum install postgresql14-mysql-fdw

创建 Foreign Server 和 User Mapping

sql

-- 创建扩展
CREATE EXTENSION mysql_fdw;

-- 创建 foreign server
CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host 'mysql_host', port '3306');

-- 创建 user mapping
CREATE USER MAPPING FOR postgres
    SERVER mysql_server
    OPTIONS (username 'mysql_user', password 'mysql_password');

-- 创建 foreign table
CREATE FOREIGN TABLE mysql_table (
    id INT,
    name VARCHAR(255),
    created_at TIMESTAMP
) SERVER mysql_server
OPTIONS (dbname 'mysql_db', table_name 'mysql_table');

-- 迁移数据到 PostgreSQL 表
INSERT INTO pg_table SELECT * FROM mysql_table;

3. 数据类型映射

MySQL 数据类型	PostgreSQL 数据类型
INT	INTEGER
BIGINT	BIGINT
TINYINT	SMALLINT 或 BOOLEAN
VARCHAR	TEXT 或 VARCHAR
TEXT	TEXT
MEDIUMTEXT	TEXT
LONGTEXT	TEXT
DATETIME	TIMESTAMP WITH TIME ZONE
TIMESTAMP	TIMESTAMP WITH TIME ZONE
DATE	DATE
TIME	TIME
BLOB	BYTEA
TINYBLOB	BYTEA
MEDIUMBLOB	BYTEA
LONGBLOB	BYTEA
DECIMAL	NUMERIC
FLOAT	REAL
DOUBLE	DOUBLE PRECISION
ENUM	TEXT 或 CHECK 约束
SET	TEXT 或 ARRAY

从 Oracle 迁移到 PostgreSQL

1. 使用 ora2pg 迁移

ora2pg 是一个开源的迁移工具，专门用于将数据从 Oracle 迁移到 PostgreSQL，支持复杂对象、存储过程、触发器等。

安装 ora2pg

bash

# Ubuntu/Debian
sudo apt-get install ora2pg

# CentOS/RHEL
sudo yum install ora2pg

# 从源码安装
git clone https://github.com/darold/ora2pg.git
cd ora2pg
perl Makefile.PL
make && make install

配置 ora2pg

创建 ora2pg.conf 文件：

ini

# 基本配置
ORACLE_HOME /usr/lib/oracle/19.3/client64
ORACLE_DSN dbi:Oracle:host=oracle_host;sid=oracle_sid
ORACLE_USERNAME oracle_user
ORACLE_PASSWORD oracle_password
PG_DSN dbi:Pg:dbname=pg_db;host=pg_host;port=5432
PG_USERNAME pg_user
PG_PASSWORD pg_password

# 迁移选项
SCHEMA_TYPE TABLE
TYPE TABLE,VIEW,SEQUENCE,INDEX,CONSTRAINT,TRIGGER,FUNCTION,PROCEDURE,PACKAGE

# 并行迁移
PARALLEL 4

执行迁移

bash

# 生成迁移报告
ora2pg -t SHOW_REPORT -c ora2pg.conf

# 生成 SQL 脚本
ora2pg -t SCHEMA -c ora2pg.conf -o schema.sql
ora2pg -t DATA -c ora2pg.conf -o data.sql

# 直接执行迁移
ora2pg -t COPY -c ora2pg.conf

2. 使用 Oracle Foreign Data Wrapper 迁移

Oracle Foreign Data Wrapper 允许 PostgreSQL 直接访问 Oracle 数据库，可以用于数据同步和迁移。

安装 Oracle Foreign Data Wrapper

bash

# 安装 Oracle 客户端
download and install Oracle Instant Client

# 安装 Oracle Foreign Data Wrapper
git clone https://github.com/laurenz/oracle_fdw.git
cd oracle_fdw
make && make install

创建 Foreign Server 和 User Mapping

sql

-- 创建扩展
CREATE EXTENSION oracle_fdw;

-- 创建 foreign server
CREATE SERVER oracle_server
    FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (dbserver '//oracle_host:1521/oracle_sid');

-- 创建 user mapping
CREATE USER MAPPING FOR postgres
    SERVER oracle_server
    OPTIONS (user 'oracle_user', password 'oracle_password');

-- 创建 foreign table
CREATE FOREIGN TABLE oracle_table (
    id NUMBER,
    name VARCHAR2(255),
    created_at TIMESTAMP
) SERVER oracle_server
OPTIONS (schema 'ORACLE_SCHEMA', table 'ORACLE_TABLE');

-- 迁移数据到 PostgreSQL 表
INSERT INTO pg_table SELECT * FROM oracle_table;

3. 数据类型映射

Oracle 数据类型	PostgreSQL 数据类型
NUMBER	NUMERIC
INTEGER	INTEGER
BIGINT	BIGINT
VARCHAR2	TEXT 或 VARCHAR
NVARCHAR2	TEXT 或 VARCHAR
CLOB	TEXT
BLOB	BYTEA
TIMESTAMP	TIMESTAMP WITH TIME ZONE
DATE	DATE
INTERVAL	INTERVAL
BOOLEAN	BOOLEAN
RAW	BYTEA
LONG	TEXT
LONG RAW	BYTEA

从 SQL Server 迁移到 PostgreSQL

1. 使用 sqlserver2pgsql 迁移

sqlserver2pgsql 是一个开源的迁移工具，用于将数据从 SQL Server 迁移到 PostgreSQL，支持架构转换和数据迁移。

下载 sqlserver2pgsql

bash

git clone https://github.com/dalibo/sqlserver2pgsql.git
cd sqlserver2pgsql

执行迁移

bash

python sqlserver2pgsql.py --mssql-host sqlserver_host --mssql-db sqlserver_db --mssql-user sqlserver_user --mssql-pass sqlserver_password --pgsql-host pg_host --pgsql-db pg_db --pgsql-user pg_user --pgsql-pass pg_password

2. 使用 SQL Server Foreign Data Wrapper 迁移

SQL Server Foreign Data Wrapper 允许 PostgreSQL 直接访问 SQL Server 数据库，可以用于数据同步和迁移。

安装 SQL Server Foreign Data Wrapper

bash

# 安装 FreeTDS
sudo apt-get install freetds-dev freetds-bin

# 安装 SQL Server Foreign Data Wrapper
git clone https://github.com/tds-fdw/tds_fdw.git
cd tds_fdw
make && make install

创建 Foreign Server 和 User Mapping

sql

-- 创建扩展
CREATE EXTENSION tds_fdw;

-- 创建 foreign server
CREATE SERVER sqlserver_server
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername 'sqlserver_host', port '1433', database 'sqlserver_db');

-- 创建 user mapping
CREATE USER MAPPING FOR postgres
    SERVER sqlserver_server
    OPTIONS (username 'sqlserver_user', password 'sqlserver_password');

-- 创建 foreign table
CREATE FOREIGN TABLE sqlserver_table (
    id INT,
    name VARCHAR(255),
    created_at DATETIME
) SERVER sqlserver_server
OPTIONS (schema_name 'dbo', table_name 'sqlserver_table');

-- 迁移数据到 PostgreSQL 表
INSERT INTO pg_table SELECT * FROM sqlserver_table;

3. 数据类型映射

SQL Server 数据类型	PostgreSQL 数据类型
INT	INTEGER
BIGINT	BIGINT
TINYINT	SMALLINT 或 BOOLEAN
VARCHAR	TEXT 或 VARCHAR
NVARCHAR	TEXT 或 VARCHAR
TEXT	TEXT
NTEXT	TEXT
DATETIME	TIMESTAMP WITH TIME ZONE
DATETIME2	TIMESTAMP WITH TIME ZONE
DATE	DATE
TIME	TIME
BINARY	BYTEA
VARBINARY	BYTEA
IMAGE	BYTEA
DECIMAL	NUMERIC
FLOAT	DOUBLE PRECISION
REAL	REAL
BIT	BOOLEAN
UNIQUEIDENTIFIER	UUID

SQL 语法差异与转换

1. 标识符引用

MySQL: 使用反引号 ` 引用标识符
PostgreSQL: 使用双引号 " 引用标识符

2. 字符串连接

MySQL: 使用 CONCAT() 函数或 + 运算符
PostgreSQL: 使用 || 运算符或 CONCAT() 函数

3. 日期函数

功能	MySQL	PostgreSQL
当前日期	`CURDATE()`	`CURRENT_DATE`
当前时间	`CURTIME()`	`CURRENT_TIME`
当前时间戳	`NOW()`	`CURRENT_TIMESTAMP`
日期格式化	`DATE_FORMAT(date, format)`	`TO_CHAR(date, format)`
字符串转日期	`STR_TO_DATE(string, format)`	`TO_DATE(string, format)`

4. 分页查询

MySQL: 使用 LIMIT 和 OFFSET
PostgreSQL: 使用 LIMIT 和 OFFSET（兼容）或 FETCH FIRST N ROWS ONLY

5. 自增列

MySQL: 使用 AUTO_INCREMENT 关键字
PostgreSQL: 使用 SERIAL 或 GENERATED ALWAYS AS IDENTITY

6. 空值处理

MySQL: NULL 与 '' 不同，但在某些情况下会自动转换
PostgreSQL: 严格区分 NULL 与 ''

迁移后验证

1. 数据完整性验证

sql

-- 检查数据量
SELECT COUNT(*) FROM pg_table;

-- 检查关键数据
SELECT * FROM pg_table WHERE id IN (1, 100, 1000);

-- 检查数据类型
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'pg_table';

2. 应用兼容性验证

测试应用程序的核心功能
测试应用程序的数据库连接
测试应用程序的查询性能
检查应用程序日志中的错误和警告

3. 性能验证

sql

-- 运行 EXPLAIN ANALYZE 分析查询性能
EXPLAIN ANALYZE SELECT * FROM pg_table WHERE condition;

-- 检查慢查询
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

最佳实践

1. 测试迁移

在正式迁移前，一定要在测试环境中进行迁移测试，验证迁移方法的可行性和性能。

2. 逐步迁移

对于大型数据库，可以采用逐步迁移的方法：

先迁移核心表和数据
然后迁移非核心表和数据
最后迁移存储过程、触发器等

3. 数据清洗

在迁移过程中，可以对数据进行清洗和优化：

去除冗余数据
修复不一致的数据
优化表结构和索引

4. 监控迁移过程

在迁移过程中，实时监控迁移进度和状态，及时发现和解决问题。

5. 制定回滚策略

无论使用哪种迁移方法，都要制定详细的回滚策略，确保迁移失败时能够快速回滚。

案例分析

案例1：Web 应用从 MySQL 迁移到 PostgreSQL

背景：某 Web 应用使用 MySQL 5.7 数据库，数据量约为 100GB，需要迁移到 PostgreSQL 14，要求停机时间不超过 2 小时。

迁移方案：

使用 pgloader 进行迁移
先迁移架构和数据
然后迁移应用程序

实施步骤：

准备目标环境：安装 PostgreSQL 14，配置参数
创建迁移配置：创建 mysql2pg.load 配置文件
执行迁移：使用 pgloader 迁移架构和数据
验证迁移结果：检查数据完整性和应用兼容性
切换应用：更新应用连接配置，指向 PostgreSQL 数据库

实施效果：

迁移时间：1 小时 30 分钟
停机时间：30 分钟
数据完整性：100% 一致
性能提升：查询响应时间减少 30%
应用兼容性：所有功能正常运行

案例2：企业应用从 Oracle 迁移到 PostgreSQL

背景：某企业应用使用 Oracle 12c 数据库，数据量约为 500GB，包含复杂的存储过程和触发器，需要迁移到 PostgreSQL 14。

迁移方案：

使用 ora2pg 进行迁移
先迁移架构和数据
然后转换和迁移存储过程、触发器
最后测试应用兼容性

实施步骤：

准备目标环境：安装 PostgreSQL 14，配置参数
生成迁移报告：使用 ora2pg 生成迁移报告，识别复杂对象
迁移架构和数据：使用 ora2pg 迁移架构和数据
转换存储过程：手动或使用工具转换 Oracle 存储过程到 PostgreSQL 函数
验证迁移结果：检查数据完整性和应用兼容性
切换应用：更新应用连接配置，指向 PostgreSQL 数据库

实施效果：

迁移时间：5 小时
停机时间：1 小时
数据完整性：100% 一致
性能提升：查询响应时间减少 40%
运维成本：降低 60%

总结

将数据从其他数据库迁移到 PostgreSQL 需要考虑数据类型差异、SQL 语法差异和应用兼容性等因素。选择合适的迁移工具和方法是确保迁移成功的关键。常用的迁移工具包括 pgloader（从 MySQL 迁移）、ora2pg（从 Oracle 迁移）和 sqlserver2pgsql（从 SQL Server 迁移）。

在迁移过程中，需要进行充分的测试和验证，确保数据完整性和应用兼容性。迁移后，需要监控数据库性能，优化查询和架构，确保系统稳定运行。

通过遵循最佳实践和案例分析，可以帮助 DBA 顺利完成从其他数据库到 PostgreSQL 的迁移，充分利用 PostgreSQL 的高级特性和优势。

PostgreSQL 从其他数据库迁移 ​

迁移前准备 ​

1. 源数据库评估 ​

数据库结构评估 ​

数据量评估 ​

应用依赖评估 ​

2. 目标环境准备 ​

安装 PostgreSQL ​

配置 PostgreSQL ​

安装必要的扩展 ​

3. 迁移工具选择 ​

从 MySQL 迁移到 PostgreSQL ​

1. 使用 pgloader 迁移 ​

安装 pgloader ​

创建迁移配置文件 ​

执行迁移 ​

2. 使用 MySQL Foreign Data Wrapper 迁移 ​

安装 MySQL Foreign Data Wrapper ​

创建 Foreign Server 和 User Mapping ​

3. 数据类型映射 ​

从 Oracle 迁移到 PostgreSQL ​

1. 使用 ora2pg 迁移 ​

安装 ora2pg ​

配置 ora2pg ​

执行迁移 ​

2. 使用 Oracle Foreign Data Wrapper 迁移 ​

安装 Oracle Foreign Data Wrapper ​

创建 Foreign Server 和 User Mapping ​

3. 数据类型映射 ​

从 SQL Server 迁移到 PostgreSQL ​

1. 使用 sqlserver2pgsql 迁移 ​

下载 sqlserver2pgsql ​

执行迁移 ​

2. 使用 SQL Server Foreign Data Wrapper 迁移 ​

安装 SQL Server Foreign Data Wrapper ​

创建 Foreign Server 和 User Mapping ​

3. 数据类型映射 ​

SQL 语法差异与转换 ​

1. 标识符引用 ​

2. 字符串连接 ​

3. 日期函数 ​

4. 分页查询 ​

5. 自增列 ​

6. 空值处理 ​

迁移后验证 ​

1. 数据完整性验证 ​

2. 应用兼容性验证 ​

3. 性能验证 ​

最佳实践 ​

1. 测试迁移 ​

2. 逐步迁移 ​

3. 数据清洗 ​

4. 监控迁移过程 ​

5. 制定回滚策略 ​

案例分析 ​

案例1：Web 应用从 MySQL 迁移到 PostgreSQL ​

案例2：企业应用从 Oracle 迁移到 PostgreSQL ​

总结 ​

PostgreSQL 从其他数据库迁移

迁移前准备

1. 源数据库评估

数据库结构评估

数据量评估

应用依赖评估

2. 目标环境准备

安装 PostgreSQL

配置 PostgreSQL

安装必要的扩展

3. 迁移工具选择

从 MySQL 迁移到 PostgreSQL

1. 使用 pgloader 迁移

安装 pgloader

创建迁移配置文件

执行迁移

2. 使用 MySQL Foreign Data Wrapper 迁移

安装 MySQL Foreign Data Wrapper

创建 Foreign Server 和 User Mapping

3. 数据类型映射

从 Oracle 迁移到 PostgreSQL

1. 使用 ora2pg 迁移

安装 ora2pg

配置 ora2pg

执行迁移

2. 使用 Oracle Foreign Data Wrapper 迁移

安装 Oracle Foreign Data Wrapper

创建 Foreign Server 和 User Mapping

3. 数据类型映射

从 SQL Server 迁移到 PostgreSQL

1. 使用 sqlserver2pgsql 迁移

下载 sqlserver2pgsql

执行迁移

2. 使用 SQL Server Foreign Data Wrapper 迁移

安装 SQL Server Foreign Data Wrapper

创建 Foreign Server 和 User Mapping

3. 数据类型映射

SQL 语法差异与转换

1. 标识符引用

2. 字符串连接

3. 日期函数

4. 分页查询

5. 自增列

6. 空值处理

迁移后验证

1. 数据完整性验证

2. 应用兼容性验证

3. 性能验证

最佳实践

1. 测试迁移

2. 逐步迁移

3. 数据清洗

4. 监控迁移过程

5. 制定回滚策略

案例分析

案例1：Web 应用从 MySQL 迁移到 PostgreSQL

案例2：企业应用从 Oracle 迁移到 PostgreSQL

总结