怎么将数据从SQL Server迁移到PostgreSQL

发布时间：2022-02-28 17:15:52 作者：iii
来源：亿速云阅读：302

# 怎么将数据从SQL Server迁移到PostgreSQL

## 前言

在企业数字化转型过程中，数据库迁移是常见的需求。将数据从SQL Server迁移到PostgreSQL可能出于成本、性能或开源策略的考虑。PostgreSQL作为功能强大的开源关系型数据库，与商业数据库SQL Server的迁移需要系统化的方法。本文将详细介绍迁移全流程，包括工具选择、步骤详解和常见问题处理。

---

## 一、迁移前的准备工作

### 1.1 环境评估与兼容性分析

- **版本兼容性检查**：
  - SQL Server版本（2008/2012/2016+）
  - PostgreSQL版本（建议9.5+，推荐12+）
  
- **数据类型映射表**：
  | SQL Server类型 | PostgreSQL类型 |
  |----------------|----------------|
  | INT            | INTEGER        |
  | VARCHAR(n)     | VARCHAR(n)     |
  | DATETIME       | TIMESTAMP      |
  | BIT            | BOOLEAN        |
  | UNIQUEIDENTIFIER| UUID           |

### 1.2 工具选型对比

| 工具名称          | 类型       | 优点                     | 缺点                  |
|-------------------|------------|--------------------------|-----------------------|
| pgLoader          | 开源工具   | 支持直接转换数据类型     | 大表迁移可能需分片   |
| SSIS             | 微软官方   | 可视化操作               | 需Windows环境        |
| Talend Open Studio | ETL工具    | 企业级功能               | 学习曲线陡峭         |
| 手动SQL导出导入   | -          | 完全可控                 | 耗时易错             |

---

## 二、详细迁移步骤

### 2.1 使用pgLoader迁移（推荐方案）

#### 安装配置
```bash
# Ubuntu安装示例
sudo apt-get install pgloader

# 验证版本
pgloader --version

编写控制文件（.load）

LOAD DATABASE
    FROM mssql://username:password@sqlserver_host/dbname
    INTO postgresql://username:password@postgres_host/dbname

WITH include no drop, create tables, create indexes, reset sequences

CAST type datetime to timestamptz,
     type uniqueidentifier to uuid using uuid-str

执行迁移

pgloader migration.load

2.2 使用SSIS迁移

创建Integration Services项目
配置数据流任务：
- OLE DB源（SQL Server）
- PostgreSQL ADO.NET目标
类型映射设置：
- 在”映射”选项卡中手动调整类型对应关系
错误处理：
- 配置错误输出重定向

2.3 手动SQL导出导入

导出SQL Server数据

-- 生成CSV文件
bcp "SELECT * FROM table" queryout "C:\export.csv" -c -t"," -S server -U user -P password

PostgreSQL导入

-- 创建目标表
CREATE TABLE target_table (...);

-- 导入数据
\copy target_table FROM '/path/to/export.csv' WITH DELIMITER ',' CSV;

三、迁移后验证

3.1 数据一致性检查

-- SQL Server计数
SELECT COUNT(*) FROM source_table;

-- PostgreSQL验证
SELECT COUNT(*) FROM target_table;

3.2 性能测试建议

查询响应时间对比
并发压力测试：
- 使用pgbench进行基准测试
```
pgbench -c 10 -j 2 -t 1000 dbname
```

3.3 常见问题解决方案

问题1：字符集不匹配

现象：中文字符显示乱码
解决：

-- PostgreSQL端设置编码
SET client_encoding = 'UTF8';

问题2：自增ID冲突

解决方案：

-- 重置序列
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

四、高级迁移场景

4.1 大表分片迁移策略

-- pgLoader分片示例
LOAD DATABASE
    WITH batch rows = 100000

4.2 增量同步方案

使用逻辑解码：
- SQL Server：CDC功能
- PostgreSQL：逻辑复制槽
Debezium实现实时同步

4.3 存储过程转换

SQL Server示例：

CREATE PROCEDURE sp_example
AS
BEGIN
    SELECT * FROM table
END

PostgreSQL转换：

CREATE OR REPLACE FUNCTION sp_example()
RETURNS SETOF table AS $$
BEGIN
    RETURN QUERY SELECT * FROM table;
END;
$$ LANGUAGE plpgsql;

五、迁移后优化建议

索引重建：
```
REINDEX TABLE large_table;
```
统计信息更新：
```
ANALYZE VERBOSE table_name;
```
参数调优：
- 调整shared_buffers
- 优化work_mem

结语

SQL Server到PostgreSQL的迁移需要系统化的方法，本文介绍了从前期准备到后期优化的全流程。建议在测试环境充分验证后再进行生产迁移，对于复杂业务系统可考虑分阶段迁移策略。PostgreSQL强大的扩展功能（如JSON支持、GIS扩展等）能为应用带来新的可能性。

注意事项：
- 生产环境迁移建议在业务低峰期进行
- 重要数据迁移前务必做好完整备份
- 考虑使用pg_dumpall备份PostgreSQL全局对象 “`