告别MySQL的阵痛：使用FDW将数据迁移到PostgreSQL，并实现双向同步

嘿，老铁们！我是老码农，今天咱们聊聊数据库迁移这事儿。特别是从MySQL跳槽到PostgreSQL，这可是个技术活儿，搞不好就掉坑里。别担心，我这儿有实战经验，带你一步步搞定它！

为什么要迁移？

首先，咱们得搞清楚为什么要迁移。MySQL好用是好用，但有些时候它确实力不从心了。PostgreSQL呢，它更稳定，更符合SQL标准，功能也更强大。尤其是在处理复杂查询、事务、以及数据完整性方面，PostgreSQL的优势非常明显。而且，PostgreSQL的生态系统也越来越完善，各种扩展和工具层出不穷，能满足各种需求。

当然，迁移数据库不是件小事，需要仔细规划和执行。但是，为了更稳定、更强大的数据库系统，咱们还是得咬咬牙，把这个坎儿迈过去。

迁移方案：FDW闪亮登场

那么，怎么迁移呢？当然不是直接把数据dump出来再导入那么简单粗暴。这样做效率低，而且在迁移过程中，系统停机时间会非常长。今天，咱们要介绍的是一种更优雅、更灵活的方案——使用FDW（Foreign Data Wrapper）。

什么是FDW？

FDW是PostgreSQL的一个强大特性，它可以让你像访问本地表一样访问其他数据库的数据。简单来说，它就像一个桥梁，让PostgreSQL可以直接读取MySQL的数据，甚至可以在PostgreSQL里对MySQL的数据进行增删改查操作。是不是很神奇？

FDW的优势

• 不停机迁移： 使用FDW，你可以边迁移边使用，最大程度地减少停机时间，对业务的影响降到最低。
• 双向同步： FDW不仅能让你读取MySQL的数据，还能让你在PostgreSQL里修改MySQL的数据，实现双向同步，这在某些场景下非常有用。
• 灵活： FDW可以让你逐步迁移数据，你可以先迁移一部分表，测试一段时间，没问题再迁移其他的。
• 方便： FDW是PostgreSQL自带的功能，不需要额外的软件或复杂的配置，上手比较容易。

实战演练：从MySQL到PostgreSQL的迁移

好，废话不多说，咱们直接进入实战环节。下面我将通过一个具体的例子，一步步教你如何使用FDW将数据从MySQL迁移到PostgreSQL，并实现双向同步。

1. 环境准备

首先，你需要准备好以下环境：

• MySQL数据库： 假设你的MySQL数据库已经安装并运行，并且你有一个可以访问的MySQL用户。
• PostgreSQL数据库： 确保你的PostgreSQL数据库已经安装并运行，并且你有一个可以访问的PostgreSQL用户。
• 网络连接： 确保你的PostgreSQL服务器可以访问MySQL服务器。

2. 创建FDW扩展

在PostgreSQL数据库中，我们需要先创建mysql_fdw扩展。如果你的PostgreSQL版本比较新，可能已经安装了，如果没有，可以按照以下步骤安装：

CREATE EXTENSION mysql_fdw;

如果遇到问题，请检查你的PostgreSQL服务器是否已经安装了相应的库文件，例如libmysqlclient。如果没有，请安装它，比如在Debian/Ubuntu上可以使用sudo apt-get install libmysqlclient-dev。

3. 创建MySQL服务器的外部服务器

接下来，我们需要在PostgreSQL中创建一个外部服务器，用于连接到MySQL数据库。你需要提供MySQL服务器的IP地址、端口号、用户名和密码。

CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (
  host '你的MySQL服务器IP地址',
  port '3306',
  dbname '你的MySQL数据库名'
);

注意：

• 将你的MySQL服务器IP地址替换成你的MySQL服务器的IP地址。
• 将3306替换成你的MySQL服务器的端口号，一般是3306。
• 将你的MySQL数据库名替换成你要连接的MySQL数据库的名称。

4. 创建用户映射

为了让PostgreSQL用户能够访问MySQL数据库，我们需要创建一个用户映射。你需要提供PostgreSQL用户名、MySQL用户名和MySQL密码。

CREATE USER MAPPING FOR postgres
SERVER mysql_server
OPTIONS (
  username '你的MySQL用户名',
  password '你的MySQL密码'
);

注意：

• 将postgres替换成你要使用的PostgreSQL用户名。
• 将你的MySQL用户名替换成你的MySQL用户名。
• 将你的MySQL密码替换成你的MySQL密码。

5. 创建外部表

现在，我们可以创建外部表了。外部表就像本地表一样，但是它的数据实际上存储在MySQL数据库中。你需要指定外部表的列名、数据类型，以及对应的MySQL表名和列名。

CREATE FOREIGN TABLE your_table (
  id INT,
  name VARCHAR(255),
  age INT
) SERVER mysql_server
OPTIONS (
  table_name 'your_mysql_table'
);

注意：

• 将your_table替换成你想要的外部表名。
• 将id INT, name VARCHAR(255), age INT替换成你的MySQL表的列名和数据类型。
• 将your_mysql_table替换成你的MySQL表的表名。

6. 查询外部表

现在，你可以像查询本地表一样查询外部表了。

SELECT * FROM your_table;

你将会看到从MySQL数据库中读取的数据。

7. 双向同步的实现

要实现双向同步，我们需要在PostgreSQL端创建触发器（Trigger），用于监听外部表的更新操作，并将这些操作同步到MySQL数据库。

7.1 创建触发器函数

首先，我们需要创建一个触发器函数，用于将PostgreSQL的更新操作同步到MySQL。

CREATE OR REPLACE FUNCTION sync_to_mysql()
RETURNS TRIGGER
AS $$
DECLARE
  mysql_host TEXT := '你的MySQL服务器IP地址';
  mysql_port INT := 3306;
  mysql_db TEXT := '你的MySQL数据库名';
  mysql_user TEXT := '你的MySQL用户名';
  mysql_password TEXT := '你的MySQL密码';
  mysql_table TEXT := 'your_mysql_table';
  sql TEXT;
BEGIN
  -- 判断操作类型，INSERT、UPDATE、DELETE
  IF TG_OP = 'INSERT' THEN
    sql := format(
      'INSERT INTO %I.%I (%s) VALUES (%s)',
      mysql_db, mysql_table,
      (SELECT string_agg(quote_ident(attname), ',') FROM pg_attribute WHERE attrelid = TG_RELID AND attnum > 0 AND NOT attisdropped),  
      (SELECT string_agg(quote_literal(NEW.attname::TEXT), ',') FROM pg_attribute WHERE attrelid = TG_RELID AND attnum > 0 AND NOT attisdropped)
    );
    EXECUTE dblink_connect(format('host=%s port=%s dbname=%s user=%s password=%s', mysql_host, mysql_port, mysql_db, mysql_user, mysql_password));
    EXECUTE dblink_exec(format('host=%s port=%s dbname=%s user=%s password=%s', mysql_host, mysql_port, mysql_db, mysql_user, mysql_password),sql);
    PERFORM dblink_disconnect();
  ELSIF TG_OP = 'UPDATE' THEN
    sql := format(
      'UPDATE %I.%I SET %s WHERE id = %L', -- 假设有id列作为主键
      mysql_db, mysql_table,
      (SELECT string_agg(quote_ident(attname) || ' = ' || quote_literal(NEW.attname::TEXT), ',') FROM pg_attribute WHERE attrelid = TG_RELID AND attnum > 0 AND NOT attisdropped),
      OLD.id
    );
    EXECUTE dblink_connect(format('host=%s port=%s dbname=%s user=%s password=%s', mysql_host, mysql_port, mysql_db, mysql_user, mysql_password));
    EXECUTE dblink_exec(format('host=%s port=%s dbname=%s user=%s password=%s', mysql_host, mysql_port, mysql_db, mysql_user, mysql_password),sql);
    PERFORM dblink_disconnect();
  ELSIF TG_OP = 'DELETE' THEN
    sql := format(
      'DELETE FROM %I.%I WHERE id = %L', -- 假设有id列作为主键
      mysql_db, mysql_table,
      OLD.id
    );
    EXECUTE dblink_connect(format('host=%s port=%s dbname=%s user=%s password=%s', mysql_host, mysql_port, mysql_db, mysql_user, mysql_password));
    EXECUTE dblink_exec(format('host=%s port=%s dbname=%s user=%s password=%s', mysql_host, mysql_port, mysql_db, mysql_user, mysql_password),sql);
    PERFORM dblink_disconnect();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

注意：

• 修改mysql_host, mysql_port, mysql_db, mysql_user, mysql_password, mysql_table 变量为你的MySQL服务器信息。
• 代码中使用dblink扩展来连接MySQL，所以你需要确保PostgreSQL已经安装了dblink扩展。
• 假设MySQL表的主键是id，如果你的主键不是id，需要修改WHERE子句。
• 这个函数动态生成 SQL 语句，需要根据你的表结构调整。

7.2 创建触发器

接下来，我们需要为外部表创建触发器，触发器会在外部表发生更新操作时，调用sync_to_mysql函数，将更新同步到MySQL。

CREATE TRIGGER your_table_sync
AFTER INSERT OR UPDATE OR DELETE
ON your_table
FOR EACH ROW
EXECUTE FUNCTION sync_to_mysql();

现在，当你在PostgreSQL的外部表上进行插入、更新或删除操作时，这些操作就会被同步到MySQL数据库。

7.3 验证双向同步

为了验证双向同步是否成功，你可以：

1. 在PostgreSQL中向外部表插入一条数据。
2. 在MySQL中查询对应的表，看看数据是否同步过去。
3. 在PostgreSQL中更新外部表的一条数据。
4. 在MySQL中查询对应的表，看看数据是否同步更新。
5. 在PostgreSQL中删除外部表的一条数据。
6. 在MySQL中查询对应的表，看看数据是否同步删除。

8. 注意事项

• 数据类型兼容性： 在创建外部表时，需要确保PostgreSQL的数据类型与MySQL的数据类型兼容。如果不兼容，可能需要进行类型转换。
• 性能： FDW的性能可能不如本地表，尤其是在处理大量数据时。你需要根据实际情况进行优化，例如使用索引、限制查询范围等。
• 错误处理： 在实际使用中，需要考虑各种错误情况，例如网络中断、MySQL服务器故障等。你需要编写相应的错误处理代码，确保数据一致性。
• 事务： FDW不支持跨数据库的事务。这意味着，如果你在PostgreSQL中执行了一个事务，其中包含了对外部表的更新操作，那么这个事务可能无法完全保证原子性。
• dblink： 在使用 dblink 进行双向同步时，需要确保服务器可以连接到MySQL服务器，同时，MySQL 用户需要有相应的权限。如果遇到连接错误，请检查网络配置和 MySQL 用户权限。
• 安全： 在配置FDW和双向同步时，要注意安全问题。不要在代码中硬编码密码，应该使用安全的配置方式。同时，要限制用户对外部表的访问权限，避免未授权的访问。

进阶：优化和问题排查

1. 性能优化

• 索引： 在MySQL的表中创建索引，可以提高查询效率。
• 查询优化： 尽量减少对外部表的查询次数，可以使用WHERE子句限制查询范围，避免全表扫描。
• 缓存： 可以考虑使用缓存来减少对MySQL的访问，例如使用PostgreSQL的pg_buffercache扩展来缓存外部表的数据。
• FDW配置： 根据MySQL服务器的配置，调整FDW的连接参数，例如fetch_size，batch_size等，可以提高数据传输效率。

2. 问题排查

• 连接问题： 检查PostgreSQL服务器是否可以连接到MySQL服务器，包括网络连接、防火墙设置等。
• 权限问题： 检查PostgreSQL用户是否有访问MySQL数据库的权限。
• 数据类型问题： 检查PostgreSQL和MySQL的数据类型是否兼容。如果不兼容，需要进行类型转换。
• 错误日志： 查看PostgreSQL的错误日志，可以帮助你找到问题所在。
• SQL语句： 在执行SQL语句时，可以尝试使用EXPLAIN命令来分析查询计划，找出性能瓶颈。

总结

通过FDW，你可以轻松地将数据从MySQL迁移到PostgreSQL，并实现双向同步。虽然这个过程可能需要一些时间和精力，但最终的结果绝对是值得的。

希望今天的分享能帮助你解决数据库迁移的问题。记住，技术之路永无止境，不断学习，不断实践，才能成为真正的技术高手！

附录：常用命令

• 创建FDW扩展： CREATE EXTENSION mysql_fdw;
• 创建外部服务器： CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'xxx', port '3306', dbname 'xxx');
• 创建用户映射： CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'xxx', password 'xxx');
• 创建外部表： CREATE FOREIGN TABLE your_table (id INT, name VARCHAR(255)) SERVER mysql_server OPTIONS (table_name 'your_mysql_table');
• 查询外部表： SELECT * FROM your_table;
• 删除外部表： DROP FOREIGN TABLE your_table;
• 删除外部服务器： DROP SERVER mysql_server;
• 删除用户映射： DROP USER MAPPING FOR postgres SERVER mysql_server;

加油，老铁们！让我们一起在技术的海洋里乘风破浪！