<think>我们需要将MongoDB 4.x的数据迁移到PostgreSQL。迁移过程包括以下步骤：
1. 分析MongoDB数据结构，设计PostgreSQL的表结构。
2. 导出MongoDB数据为中间格式（如JSON）。
3. 将中间格式的数据转换为PostgreSQL的插入格式。
4. 导入数据到PostgreSQL。
注意：由于MongoDB是文档数据库，PostgreSQL是关系型数据库，数据结构需要重新设计。
步骤1：设计PostgreSQL表结构
- 将MongoDB的集合（collections）映射为PostgreSQL的表（tables）。
- 将文档（documents）中的字段映射为表的列（columns）。
- 处理嵌套文档和数组：可以将其展平为多个表（关系型设计）或使用PostgreSQL的JSONB类型（如果保留文档结构）。
步骤2：导出MongoDB数据
- 使用`mongoexport`工具导出数据为JSON。
步骤3：数据转换
- 编写脚本（如Node.js、Python等）将JSON数据转换为SQL插入语句或CSV格式。
步骤4：导入到PostgreSQL
- 使用`psql`命令或`pg_restore`导入数据。
下面是一个详细的示例，假设我们有一个MongoDB集合`users`，其文档结构如下：
```json
{
"_id": ObjectId("5f9d1a2d7e4a3b3d6c8b9a1f"),
"name": "Alice",
"email": "alice@example.com",
"age": 30,
"addresses": [
{
"street": "123 Main St",
"city": "New York",
"zip": "10001"
},
{
"street": "456 Elm St",
"city": "Boston",
"zip": "02134"
}
],
"created_at": ISODate("2020-10-30T00:00:00Z")
}
```
在PostgreSQL中，我们可以设计两个表：
- `users`表：存储用户基本信息。
- `addresses`表：存储用户的多个地址，与用户表通过外键关联。
设计SQL：
```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
mongo_id VARCHAR(24) UNIQUE, -- 保留原MongoDB的_id，可选
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
age INTEGER,
created_at TIMESTAMPTZ
);
CREATE TABLE addresses (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
street VARCHAR(100),
city VARCHAR(50),
zip VARCHAR(10)
);
```
步骤2：导出MongoDB数据
```bash
mongoexport --db mydb --collection users --out users.json
```
步骤3：数据转换（使用Node.js脚本）
安装依赖：
```bash
npm install pg
```
转换脚本（convert.js）：
```javascript
const fs = require('fs');
const { Client } = require('pg');
const usersData = JSON.parse(fs.readFileSync('users.json', 'utf8'));
// PostgreSQL连接配置
const client = new Client({
user: 'postgres',
host: 'localhost',
database: 'mydb',
password: 'password',
port: 5432,
});
async function migrate() {
await client.connect();
try {
for (const user of usersData) {
// 插入用户表
const userRes = await client.query(
`INSERT INTO users (mongo_id, name, email, age, created_at)
VALUES ($1, $2, $3, $4, $5) RETURNING id`,
[user._id.$oid, user.name, user.email, user.age, user.created_at.$date]
);
const userId = userRes.rows[0].id;
// 插入地址表
if (user.addresses && user.addresses.length > 0) {
for (const address of user.addresses) {
await client.query(
`INSERT INTO addresses (user_id, street, city, zip)
VALUES ($1, $2, $3, $4)`,
[userId, address.street, address.city, address.zip]
);
}
}
}
} catch (err) {
console.error('Error during migration:', err);
} finally {
await client.end();
}
}
migrate();
```
步骤4：运行脚本
```bash
node convert.js
```
注意：如果数据量很大，可以使用批量插入和事务来提高性能。
对于大数据量的情况，我们可以优化脚本：
- 使用事务：将整个迁移过程放在一个事务中，或者分批次使用事务。
- 批量插入：将用户数据和地址数据分别收集，然后使用`INSERT INTO ... VALUES (..), (..), ...`一次插入多条。
优化后的批量插入示例：
```javascript
async function migrateBatch() {
await client.connect();
try {
await client.query('BEGIN');
const userValues = [];
const addressValues = [];
// 收集用户数据
for (const user of usersData) {
userValues.push([
user._id.$oid,
user.name,
user.email,
user.age,
user.created_at.$date
]);
// 收集地址数据
if (user.addresses && user.addresses.length) {
for (const addr of user.addresses) {
// 注意：这里还没有用户的数据库id，所以先记录mongo_id，后面再处理
addressValues.push({
mongo_id: user._id.$oid,
street: addr.street,
city: addr.city,
zip: addr.zip
});
}
}
}
// 批量插入用户
const userPlaceholders = userValues.map((_, i) =>
`($${i*5+1}, $${i*5+2}, $${i*5+3}, $${i*5+4}, $${i*5+5})`
).join(',');
const userQueryText = `
INSERT INTO users (mongo_id, name, email, age, created_at)
VALUES ${userPlaceholders} RETURNING id, mongo_id
`;
const userFlatValues = userValues.flat();
const userRes = await client.query(userQueryText, userFlatValues);
const insertedUsers = userRes.rows; // 返回的包括id和mongo_id
// 创建mongo_id到数据库id的映射
const userMap = new Map();
insertedUsers.forEach(u => userMap.set(u.mongo_id, u.id));
// 批量插入地址
const addrValues = [];
for (const addr of addressValues) {
const userId = userMap.get(addr.mongo_id);
if (userId) {
addrValues.push([userId, addr.street, addr.city, addr.zip]);
}
}
if (addrValues.length) {
const addrPlaceholders = addrValues.map((_, i) =>
`($${i*4+1}, $${i*4+2}, $${i*4+3}, $${i*4+4})`
).join(',');
const addrQueryText = `
INSERT INTO addresses (user_id, street, city, zip)
VALUES ${addrPlaceholders}
`;
await client.query(addrQueryText, addrValues.flat());
}
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
await client.end();
}
}
```
注意事项：
- 如果数据量极大，可能需要分批处理，每批几千条，避免内存溢出。
- 对于日期类型，MongoDB导出的JSON中是`{"$date": "ISO日期字符串"}`，需要转换为PostgreSQL的`TIMESTAMPTZ`。
- 对于ObjectId，我们通常不需要在PostgreSQL中使用，但可以保留为字符串以便于追踪。
其他复杂结构的处理：
- 数组：如果数组元素是基本类型，可以使用PostgreSQL的数组类型（如`TEXT[]`）。
- 嵌套文档：如果嵌套文档结构固定，可以展平为多个列；如果变化大，可以使用JSONB类型存储整个文档。
例如，如果不想拆分地址表，可以在用户表中使用JSONB存储地址：
```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
... 其他字段,
addresses JSONB
);
```
然后在转换脚本中直接存储整个地址数组。
总结：迁移过程需要根据具体的数据结构来设计关系表，并编写转换脚本。以上提供了一个基本框架，可以根据实际情况调整。
§§相关问题§§:
1. 如何将MongoDB中的嵌套文档数组转换为PostgreSQL的JSONB类型？
2. 在迁移大数据量时，如何优化转换脚本的性能？
3. 如何处理MongoDB中的特殊数据类型（如ObjectId, ISODate）？
4. 在PostgreSQL中如何查询JSONB字段？</think># MongoDB 4.x 迁移到 PostgreSQL 的完整方案
将 MongoDB 4.x 迁移到 PostgreSQL 需要处理两种数据库模型的转换：文档模型到关系模型的映射。以下是完整的迁移方案，包含详细步骤和代码实现。
## 迁移流程图
```mermaid
graph TD
A[MongoDB 4.x] --> B[分析数据结构]
B --> C[设计PostgreSQL模式]
C --> D[数据提取]
D --> E[数据转换]
E --> F[数据加载]
F --> G[验证与测试]
```
## 迁移步骤详解
### 1. 数据结构分析与模式设计
**MongoDB 文档示例**：
```json
{
"_id": ObjectId("5f9d1a2d7e4a3b3d6c8b9a1f"),
"name": "Alice",
"email": "alice@example.com",
"age": 30,
"addresses": [
{
"street": "123 Main St",
"city": "New York",
"zip": "10001"
},
{
"street": "456 Elm St",
"city": "Boston",
"zip": "02134"
}
],
"created_at": ISODate("2020-10-30T00:00:00Z")
}
```
**PostgreSQL 模式设计**：
```sql
-- 主表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
mongo_id VARCHAR(24) UNIQUE, -- 保留原始MongoDB ID
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
age INTEGER,
created_at TIMESTAMPTZ
);
-- 地址子表（1:N关系）
CREATE TABLE addresses (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
street VARCHAR(100),
city VARCHAR(50),
zip VARCHAR(10)
);
```
### 2. 数据提取脚本（Node.js）
```javascript
// extract.js
const { MongoClient } = require('mongodb');
const fs = require('fs');
async function extractData() {
const client = new MongoClient('mongodb://localhost:27017');
try {
await client.connect();
const db = client.db('your_database');
const collections = await db.listCollections().toArray();
const data = {};
for (const collection of collections) {
const collName = collection.name;
const cursor = db.collection(collName).find({});
data[collName] = await cursor.toArray();
}
fs.writeFileSync('mongodb_data.json', JSON.stringify(data, null, 2));
console.log('数据导出完成！');
} catch (err) {
console.error('导出错误:', err);
} finally {
await client.close();
}
}
extractData();
```
### 3. 数据转换与加载脚本（Node.js + PostgreSQL）
```javascript
// migrate.js
const { Pool } = require('pg');
const fs = require('fs');
const data = JSON.parse(fs.readFileSync('mongodb_data.json'));
// PostgreSQL 连接配置
const pool = new Pool({
user: 'postgres',
host: 'localhost',
database: 'your_database',
password: 'your_password',
port: 5432,
});
// 数据类型映射
const typeMappings = {
'ObjectId': 'VARCHAR(24)',
'String': 'VARCHAR(255)',
'Number': 'NUMERIC',
'Boolean': 'BOOLEAN',
'Date': 'TIMESTAMPTZ',
'Array': 'JSONB',
'Object': 'JSONB'
};
async function migrate() {
const client = await pool.connect();
try {
await client.query('BEGIN');
for (const [collection, documents] of Object.entries(data)) {
// 创建表（如果不存在）
await createTableIfNotExists(client, collection, documents);
// 插入数据
for (const doc of documents) {
await insertDocument(client, collection, doc);
}
}
await client.query('COMMIT');
console.log('迁移成功！');
} catch (err) {
await client.query('ROLLBACK');
console.error('迁移失败:', err);
} finally {
client.release();
await pool.end();
}
}
async function createTableIfNotExists(client, tableName, documents) {
if (documents.length === 0) return;
const sampleDoc = documents[0];
const columns = [];
// 添加主键
columns.push('id SERIAL PRIMARY KEY');
columns.push('mongo_id VARCHAR(24) UNIQUE');
// 分析文档结构
for (const [key, value] of Object.entries(sampleDoc)) {
if (key === '_id') continue;
const pgType = getPostgresType(value);
const columnDef = `${key} ${pgType}`;
columns.push(columnDef);
// 处理嵌套数组（1:N关系）
if (Array.isArray(value) && typeof value[0] === 'object') {
await createRelatedTable(client, tableName, key, value[0]);
}
}
const createTableSQL = `
CREATE TABLE IF NOT EXISTS ${tableName} (
${columns.join(',\n')}
)
`;
await client.query(createTableSQL);
}
function getPostgresType(value) {
if (value === null) return 'TEXT';
if (Array.isArray(value)) {
return typeof value[0] === 'object' ? 'INTEGER[]' : 'JSONB';
}
const type = value.constructor.name;
return typeMappings[type] || 'TEXT';
}
async function createRelatedTable(client, mainTable, relationName, sampleItem) {
const tableName = `${mainTable}_${relationName}`;
const columns = ['id SERIAL PRIMARY KEY'];
columns.push(`${mainTable}_id INTEGER REFERENCES ${mainTable}(id) ON DELETE CASCADE`);
for (const [key, value] of Object.entries(sampleItem)) {
const pgType = getPostgresType(value);
columns.push(`${key} ${pgType}`);
}
const createTableSQL = `
CREATE TABLE IF NOT EXISTS ${tableName} (
${columns.join(',\n')}
)
`;
await client.query(createTableSQL);
}
async function insertDocument(client, tableName, doc) {
const keys = Object.keys(doc).filter(k => k !== '_id');
const values = [];
const placeholders = [];
const subInserts = [];
// 主文档值
keys.forEach((key, index) => {
const value = doc[key];
if (Array.isArray(value) && typeof value[0] === 'object') {
// 处理嵌套数组（1:N关系）
subInserts.push({ key, values: value });
} else {
// 处理普通值
values.push(transformValue(value));
placeholders.push(`$${values.length}`);
}
});
// 添加原始MongoDB ID
values.push(doc._id.toString());
placeholders.push(`$${values.length}`);
const insertSQL = `
INSERT INTO ${tableName} (${keys.join(', ')}, mongo_id)
VALUES (${placeholders.join(', ')})
RETURNING id
`;
const result = await client.query(insertSQL, values);
const mainId = result.rows[0].id;
// 插入关联数据
for (const { key, values: nestedDocs } of subInserts) {
for (const nestedDoc of nestedDocs) {
await insertRelatedDocument(
client,
`${tableName}_${key}`,
mainId,
nestedDoc
);
}
}
}
async function insertRelatedDocument(client, tableName, mainId, doc) {
const keys = Object.keys(doc);
const values = [mainId];
const placeholders = ['$1'];
keys.forEach((key, index) => {
values.push(transformValue(doc[key]));
placeholders.push(`$${values.length}`);
});
const insertSQL = `
INSERT INTO ${tableName} (${tableName.split('_')[0]}_id, ${keys.join(', ')})
VALUES (${placeholders.join(', ')})
`;
await client.query(insertSQL, values);
}
function transformValue(value) {
if (value === null) return null;
if (value instanceof Date) return value.toISOString();
if (typeof value === 'object' && !Array.isArray(value)) return JSON.stringify(value);
return value;
}
migrate();
```
## 迁移后数据验证
### 1. 数据完整性检查
```sql
-- 检查记录数
SELECT
(SELECT COUNT(*) FROM users) AS pg_users,
(SELECT COUNT(*) FROM mongodb_data->'users') AS mongo_users;
-- 检查关联数据
SELECT
(SELECT COUNT(*) FROM addresses) AS pg_addresses,
(SELECT SUM(JSONB_ARRAY_LENGTH(addresses))
FROM mongodb_data->'users') AS mongo_addresses;
```
### 2. 数据一致性检查
```sql
-- 比较随机样本
SELECT
u.name AS pg_name,
(SELECT name FROM jsonb_array_elements(
(SELECT data FROM mongodb_data WHERE collection = 'users')
-> jsonb_array_elements->'name'
)) AS mongo_name
FROM users u
ORDER BY RANDOM()
LIMIT 10;
```
## 迁移最佳实践
1. **分阶段迁移**：
```mermaid
graph LR
A[生产MongoDB] --> B[只读副本]
B --> C[迁移脚本]
C --> D[测试环境PostgreSQL]
D --> E[验证]
E --> F[生产PostgreSQL]
```
2. **增量迁移**：
```javascript
// 增量迁移示例
async function incrementalMigration(lastMigratedId) {
const cursor = db.collection('users').find({
_id: { $gt: new ObjectId(lastMigratedId) }
}).sort({ _id: 1 }).limit(1000);
// ...迁移逻辑...
return lastDocument._id.toString();
}
```
3. **性能优化**：
```javascript
// 批量插入优化
async function batchInsert(client, tableName, documents) {
const values = [];
const placeholders = [];
documents.forEach((doc, idx) => {
const rowValues = [];
Object.entries(doc).forEach(([key, value]) => {
if (key !== '_id') {
rowValues.push(transformValue(value));
}
});
rowValues.push(doc._id.toString());
values.push(...rowValues);
const ph = rowValues.map((_, i) =>
`$${idx * rowValues.length + i + 1}`
);
placeholders.push(`(${ph.join(', ')})`);
});
const insertSQL = `
INSERT INTO ${tableName} (${columns.join(', ')})
VALUES ${placeholders.join(', ')}
`;
await client.query(insertSQL, values);
}
```
## 常见问题解决方案
1. **处理ObjectID**：
```sql
-- 添加自定义函数处理ObjectID
CREATE OR REPLACE FUNCTION to_timestamp(object_id TEXT)
RETURNS TIMESTAMP AS $$
SELECT to_timestamp(('x' || lpad(substring(object_id, 1, 8), 8, '0'))::bit(32)::int)
$$ LANGUAGE SQL IMMUTABLE;
```
2. **处理BSON特殊类型**：
```javascript
// 特殊类型转换
function transformBSON(value) {
if (value instanceof Binary) {
return Buffer.from(value.buffer);
}
if (value instanceof Decimal128) {
return parseFloat(value.toString());
}
if (value instanceof ObjectId) {
return value.toString();
}
if (value instanceof Timestamp) {
return new Date(value.toNumber() * 1000);
}
return value;
}
```
3. **处理数组差异**：
```sql
-- PostgreSQL数组操作
SELECT
id,
ARRAY(SELECT jsonb_array_elements_text(tags)) AS tags_array
FROM users;
```
## 迁移后优化
1. **索引优化**：
```sql
-- 为常用查询字段创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
-- GIN索引用于JSONB字段
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);
```
2. **查询重写**：
```sql
-- MongoDB查询
db.users.find({ age: { $gt: 25 } })
-- PostgreSQL等效
SELECT * FROM users WHERE age > 25;
```
3. **事务处理**：
```javascript
// PostgreSQL事务示例
async function transferFunds(from, to, amount) {
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
[amount, from]
);
await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
[amount, to]
);
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
```
扫一扫
305
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
