从PostgreSQL复制数据到MySQL

Question

从PostgreSQL复制数据到MySQL

mysqlpostgresqlsql-convert

3

我当前拥有一个PostgreSQL数据库，因为我们正在使用的软件之一仅支持该特定数据库引擎。我有一个查询，它将应用程序中的数据汇总并拆分为更有用的格式。

在我的MySQL数据库中，我有一个包含与上述查询输出相同模式的表格。

我想要开发一个每小时运行的cron作业，它将针对PostgreSQL数据库运行查询，然后将结果插入到MySQL数据库中。在这一小时内，我不希望看到超过10,000个新行（甚至更少），需要转移。

两个数据库位于物理上分离的服务器上，彼此相距很远。MySQL实例运行在Amazon RDS上 - 因此我们对机器本身没有太多控制。PostgreSQL实例运行在我们的服务器中的VM上，使我们具有完全的控制。

不幸的是，复制是必要的，因为PostgreSQL数据库只充当信息的收集者，而MySQL数据库上有一个应用程序需要这些数据。为了简单起见，我们希望每小时从PostgreSQL进行移动/合并和删除以保持清洁。

明确一下 - 我是一个网络/系统管理员 - 不是DBA。我真的不理解在转换一种格式到另一种格式所需的所有复杂性。我知道的是，正在传输的数据包括1个VARCHAR、1个DATETIME和6个BIGINT列。

我猜测一种可能的方法是使用某种脚本语言来进行查询，将结果转换为内部数据结构，然后再将其拆分到MySQL中。

在这样做时，写脚本时应该注意哪些好的或坏的做法？或者 - 我应该查看哪些可能有用于进行此类转换的文档？我找到了许多计划作业似乎非常可管理且有良好的文档，但这个脚本的持续性（每小时运行）似乎不太常见和/或文档化。

欢迎任何建议。

- bdx

1

复制数据的原因是什么？你不能只使用PostgreSQL来处理你使用MySQL的东西吗？ - user330315

1

一种脚本语言是一个不必要的依赖项。只需将其输出到文本文件中，然后从那里导入即可。 - Clodoaldo Neto

有很多种方法可以完成这个任务。我认为要得到一个好的答案，你需要提供更多的信息。比如你使用的是什么环境？服务器是否在同一台机器上等等。如果是我，我会设置一个cron job每小时运行一个Python脚本。但是，我熟悉Linux并且是一个Python开发者。 - David S

大家提出的观点都很中肯，我会在底部编辑我的问题以提供澄清。 - bdx

PostgreSQL和MySQL都有适当的工具来导入/导出文本文件。PostgreSQL的工具是COPY，而MySQL的工具是mysqlimport。除了特殊情况，我想这只是一个正确使用语法的问题。 - Clodoaldo Neto

2个回答

0

简而言之，你有两种情况：

1）使目标从源中拉取数据到自己的结构中

2）使源将数据从其结构推送到目标

我更喜欢尝试第二个方案，找到一种方法来创建postgresql触发器或某些特殊的“虚拟”表，或者可能是pl / pgsql函数 - 然后，您将能够通过执行cron中的某个查询或可能来执行该过程。从内部postgres，在那里有一些操作调度的可能性。我选择第2种情况，因为postgres更加灵活，并以一些特殊的DIY方式操纵数据-您将只需更多的可能性。

外部脚本可能不是一个好的解决方案，例如，您需要特别小心处理二进制数据，或将日期和时间从DATE转换为VARCHAR，然后再转换为DATE。在外部脚本中，各种文本存储的数据可能只是字符串，您还需要对其进行引用。

- Piotr Wadas

再说一句 - 从PGSQL内部进行操作还有一个优点 - 正如您所说，应用程序一直将数据放入数据库中，因此最好能够从至少在理论上知道何时同步/推出数据的侧面推出数据。 - Piotr Wadas

你能提供任何解释如何在PGSQL内部完成这个操作的文档链接吗？我已经搜索了一下，但是我看不到如何在其中建立与完全独立的数据库引擎的数据库连接。 - bdx

显然，你需要使用pl/PgSQL。在这里，你可以找到一些关于任务调度的信息：http://www.postgresonline.com/journal/archives/19-Setting-up-PgAgent-and-Doing-Scheduled-Backups.html，以及关于pl/PgSQL的信息：http://www.postgresql.org/docs/9.2/interactive/plperl-funcs.html。 - Piotr Wadas

提示：您可以使用外部 Perl 模块连接到其他数据库，当您创建“不受信任”的 plperl（实际上意味着它可以访问外部文件）时。然而，正如 Clodoaldo 建议的那样，这将需要一些研究和工作，并且会相当复杂，因此取决于您可以花费多少时间以及任务的重要性。 - Piotr Wadas

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Craig Ringer · Accepted Answer

使用相同的数据库系统并使用复制

如果您的远程端也是PostgreSQL，则可以使用流复制和热备份来透明且自动地将远程端与本地端同步。

如果本地端和远程端都是MySQL，则可以使用MySQL的各种复制功能，如binlog复制，进行类似的操作。

使用外部脚本进行同步

使用外部脚本没有问题。实际上，即使您使用DBI-Link或类似工具（见下文），您可能仍需要从cron作业中使用外部脚本（或psql）来启动复制，除非您要使用PgAgent来执行此操作。

要么通过由触发程序维护的队列表累积行，要么确保您可以编写一个始终可靠地仅选择新行的查询。然后连接到目标数据库并INSERT新行。

如果要复制的行太大而无法轻松放入内存，则可以使用游标并使用FETCH读取行，这在要复制的行太大而无法轻松放入内存时非常有用。

我会按照以下顺序完成工作：

连接到PostgreSQL
连接到MySQL
开始一个PostgreSQL事务
开始一个MySQL事务。如果您的MySQL使用MyISAM，请立即修复。
通过游标或使用DELETE FROM queue_table RETURNING *从PostgreSQL读取行
将它们插入到MySQL中
DELETE在PostgreSQL中的队列表中的任何行（如果您尚未这样做）。
COMMIT MySQL事务。
如果MySQL COMMIT成功，则COMMIT PostgreSQL事务。如果失败，则ROLLBACK PostgreSQL事务并重试整个过程。

由于它是本地数据库，因此PostgreSQL COMMIT极不可能失败，但如果您需要完美的可靠性，则可以在PostgreSQL端使用two-phase commit，其中：

在PostgreSQL中使用PREPARE TRANSACTION
在MySQL中使用COMMIT
然后根据MySQL提交的结果，在PostgreSQL中使用COMMIT PREPARED或ROLLBACK PREPARED。

这可能对您来说太复杂了，但这是确保更改在两个数据库上同时发生或都不发生的唯一方法。

顺便说一句，如果您的MySQL使用MyISAM表存储，您应该解决这个问题。它容易在崩溃时丢失数据，并且无法进行事务更新。转换为InnoDB。

在PostgreSQL中使用DBI-Link

也许是因为我对PostgreSQL比较熟悉，所以我会使用一个使用 DBI-link 通过 PL/Perlu 完成任务的 PostgreSQL 函数来实现此操作。

当需要进行复制时，我会运行一个使用 DBI-Link 连接到 MySQL 数据库并将数据插入队列表中的 PL/PgSQL 或 PL/Perl 程序。

DBI-Link 有很多示例，因此我在这里不再重复了。这是一个常见的用例。

使用触发器将更改排队，并使用 DBI-link 同步。

如果您只想复制新行并且您的表仅追加，您可以编写一个触发器过程，将所有新插入的行附加到具有与主表相同定义的单独队列表中。当您想要同步时，您的同步过程可以在单个事务中 LOCK TABLE the_queue_table IN EXCLUSIVE MODE;，复制数据，并 DELETE FROM the_queue_table;。这保证不会丢失任何行，但仅适用于仅 INSERT 的表。在目标表上处理 UPDATE 和 DELETE 是可能的，但更加复杂。

使用外部数据包装器将MySQL添加到PostgreSQL

如果使用PostgreSQL 9.1或更高版本，可以考虑使用MySQL Foreign Data Wrapper、ODBC FDW或JDBC FDW，使PostgreSQL能够将远程MySQL表视为本地表。然后可以使用writable CTE来复制数据。

WITH moved_rows AS (
    DELETE FROM queue_table RETURNING *
)
INSERT INTO mysql_table
SELECT * FROM moved_rows;