PostgreSQL 联手 MySQL：FDW + 触发器实现实时数据同步，再也不怕数据不一致！

引言

哥们，你有没有遇到过这种情况：你的应用同时用着 PostgreSQL 和 MySQL，然后你得费劲巴拉地保证两边数据一致？数据同步这事儿，说简单也简单，说难也真能让人头大。不过别担心，今天咱就来聊聊怎么用 PostgreSQL 的 FDW (Foreign Data Wrapper) 和触发器，实现 PostgreSQL 和 MySQL 之间数据的实时同步，让数据一致性不再是难题！

为什么选择 FDW + 触发器？

市面上数据同步方案不少，为啥偏偏要选 FDW + 触发器呢？

• 实时性： 触发器这玩意儿，就像个哨兵，只要 PostgreSQL 这边数据一变动，它立马就能“感知”到，然后把变动“告诉” MySQL，保证数据同步的及时性。
• 灵活性： FDW 就像个“翻译官”，能让 PostgreSQL 直接“读懂” MySQL 的数据。你想同步哪些表、哪些字段，都由你说了算，非常灵活。
• 性能： FDW 是 PostgreSQL 的原生功能，性能有保障。触发器也是数据库内置的机制，效率杠杠的。
• 易用性： 相比其他方案，FDW + 触发器的配置相对简单，上手容易。

当然，金无足赤，这个方案也有它的局限性：

• 单向同步： 目前主要支持从 PostgreSQL 到 MySQL 的单向同步。如果需要双向同步，可能需要更复杂的配置或者借助其他工具。
• 冲突处理： 如果两边同时修改同一条数据，可能会出现冲突。需要提前考虑好冲突解决策略。
• 性能影响： 触发器会增加数据库的负担，如果数据变动非常频繁，可能会对性能产生一定影响。

方案详解

接下来，咱们一步步看看怎么实现这个方案。

1. 环境准备

首先，你得有 PostgreSQL 和 MySQL 数据库，并且都得能正常运行。版本方面，建议：

• PostgreSQL：9.3 或更高版本（FDW 功能在 9.3 版本引入）
• MySQL：5.5 或更高版本

2. 安装 FDW 扩展

在 PostgreSQL 中，我们需要安装 mysql_fdw 扩展。这个扩展能让 PostgreSQL 访问 MySQL 数据库。

-- 连接到 PostgreSQL 数据库
\c your_database_name
 
-- 安装 mysql_fdw 扩展
CREATE EXTENSION mysql_fdw;

3. 创建服务器和用户映射

接下来，我们需要在 PostgreSQL 中创建一个“外部服务器”，用来连接 MySQL 数据库。同时，我们还需要创建一个“用户映射”，把 PostgreSQL 的用户和 MySQL 的用户对应起来。

-- 创建外部服务器
CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (
host 'your_mysql_host',
port '3306',
dbname 'your_mysql_database'
);
 
-- 创建用户映射
CREATE USER MAPPING FOR your_postgresql_user
SERVER mysql_server
OPTIONS (
username 'your_mysql_user',
password 'your_mysql_password'
);

把上面代码中的 your_mysql_host、your_mysql_database、your_postgresql_user、your_mysql_user 和 your_mysql_password 替换成你自己的实际信息。

4. 创建外部表

现在，我们可以在 PostgreSQL 中创建“外部表”了。外部表看起来像 PostgreSQL 的普通表，但实际上数据存储在 MySQL 中。

-- 创建外部表
CREATE FOREIGN TABLE your_external_table (
 id INT,
 name VARCHAR(255),
 -- 其他字段...
) SERVER mysql_server
OPTIONS (
table_name 'your_mysql_table'
);

把 your_external_table 和 your_mysql_table 替换成你自己的表名。注意，外部表的字段类型需要和 MySQL 表的字段类型对应。

5. 创建触发器和触发器函数

重头戏来了！我们要创建触发器和触发器函数，实现数据同步。

-- 创建触发器函数
CREATE OR REPLACE FUNCTION sync_to_mysql()
RETURNS TRIGGER AS $$
BEGIN
 -- 数据插入
 IF (TG_OP = 'INSERT') THEN
  INSERT INTO your_external_table (id, name, ...)
  VALUES (NEW.id, NEW.name, ...);
 -- 数据更新
 ELSIF (TG_OP = 'UPDATE') THEN
  UPDATE your_external_table
  SET name = NEW.name, ...
  WHERE id = OLD.id;
 -- 数据删除
 ELSIF (TG_OP = 'DELETE') THEN
  DELETE FROM your_external_table
  WHERE id = OLD.id;
 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- 创建触发器
CREATE TRIGGER your_trigger_name
AFTER INSERT OR UPDATE OR DELETE
ON your_postgresql_table
FOR EACH ROW
EXECUTE PROCEDURE sync_to_mysql();

把 your_trigger_name、your_postgresql_table 和外部表中的字段名替换成你自己的。

这个触发器函数做了什么呢？

• TG_OP：表示触发器的操作类型，可以是 INSERT、UPDATE 或 DELETE。
• NEW：表示新插入或更新的行。
• OLD：表示被更新或删除的旧行。

当 PostgreSQL 的 your_postgresql_table 表发生插入、更新或删除操作时，触发器 your_trigger_name 就会被触发，执行 sync_to_mysql() 函数。这个函数会根据操作类型，向 MySQL 的 your_external_table 表执行相应的插入、更新或删除操作，从而实现数据同步。

6. 测试

现在，你可以试试在 PostgreSQL 的 your_postgresql_table 表中插入、更新或删除数据，然后看看 MySQL 的 your_external_table 表中的数据是否同步更新了。

常见问题及解决方案

在实际使用中，你可能会遇到一些问题。下面是一些常见问题及解决方案：

1. 数据类型不匹配

PostgreSQL 和 MySQL 的数据类型可能不完全一样。如果直接同步，可能会出现错误。你需要仔细检查外部表的字段类型，确保它们和 MySQL 表的字段类型兼容。必要时，可以使用类型转换函数进行转换。

2. 主键冲突

如果两边都插入了相同主键的数据，可能会导致主键冲突。你可以在 MySQL 表中设置自增主键，或者在 PostgreSQL 的触发器函数中处理主键冲突。

3. 同步延迟

由于网络等原因，数据同步可能会有一定的延迟。如果对实时性要求非常高，你可能需要考虑其他同步方案。

4. 触发器死循环

如果你的触发器逻辑有问题，可能会导致死循环。在编写触发器函数时，一定要小心，避免出现死循环。

###5. 性能调优

如果数据同步导致性能下降，你可以尝试以下方法进行优化：

• 批量操作，减少单次操作对系统的影响。
• 在不影响数据一致性的前提下，增加同步间隔。
• 对经常进行同步操作的表建立索引。
• 升级硬件。

总结

通过 FDW + 触发器，我们可以方便地实现 PostgreSQL 和 MySQL 之间的数据实时同步。这种方案具有实时性、灵活性、性能和易用性等优点。当然，它也有一些局限性，比如单向同步、冲突处理和性能影响等。在使用时，我们需要根据实际情况进行权衡和选择。

希望这篇文章能帮助你解决数据同步的难题！如果你有任何问题或者更好的方案，欢迎在评论区留言交流。