将PostgreSQL复制到MySQL的3种简单方法

方石剑
3,510 阅读15分钟

将PostgreSQL复制到MySQL：3种简单方法

PostgreSQL 是一个免费的数据库管理系统，可以处理关系型（单值）和非关系型（多值、分层）数据类型。

MySQL 是Oracle开发的开源关系型数据库管理系统。它是最早的数据库解决方案之一，具有一系列有用的功能。MariaDB是一个现代解决方案，作为MySQL的轻量级版本，具有更多的功能和更大的处理复杂数据的能力。

在某些情况下，企业更喜欢使用PostgreSQL而不是MySQL，传输数据有助于他们更快速地检查和处理数据。

这篇文章谈到了你可以用不同的方法将PostgreSQL与MySQL 无缝连接。在深入探讨 PostgreSQL到MySQL的数据传输方法之前，它还对PostgreSQL和MySQL服务器进行了简要介绍。

什么是PostgreSQL？

PostgreSQL 也被称为Postgres。它是一个免费使用的RDBMS 平台，支持SQL并有很多可扩展性。1996年7月8日，它在加利福尼亚大学伯克利分校的一个实验室里开发后被发布。它是为了取代以前的Ignes数据库而做的。结构化对象被用来在PostgreSQL中存储数据。

它是用C语言编写的，有一个单片机的架构，这意味着所有的组件都是连接在一起的，并且以对称和系统的方式工作。它有一个庞大的社区，并为其付费会员提供大量的社区援助以及额外帮助。医疗保健、银行和制造业只是雇用它的几个企业。它也使得安装新的备份解决方案成为可能。

单片机架构意味着所有的组件都是联系在一起的，并按照一个逻辑顺序工作。它既提供社区支持，也为一些付费客户提供额外的帮助。由于其创新的备份方法，它经常被用于医疗保健、银行和工业领域。

PostgreSQL的主要特点

PostgreSQL提供了一些独特的特性，使它成为其他主流RDBMS的可行替代品。其中一些特质如下。

  • PostgreSQL能够处理各种各样的数据和文件类型。
  • 由于架构是单片式的，各组件以自动化的方式共同运作。
  • 它可以在银行系统中用于风险管理、商业智能和其他事务性工作流程。
  • 它提供多种故障保护 和冗余，以保证存储的可靠性。
  • 它是开源的，免费的，并且有很多功能。
  • 它的可扩展性有限，因为它的处理能力依赖于它所运行的机器。
  • 它符合ACID（原子性、一致性、隔离性和持久性） 标准，并在各种操作系统上运行。

什么时候应该使用PostgreSQL。主要的使用案例

  • 金融业。PostgreSQL非常适用于金融业。因为PostgreSQL完全符合ACID标准，它是OLTP工作负载（在线交易处理）的理想选择。另一方面，PostgreSQL不仅是一个伟大的OLTP数据库；它也是一个有能力的分析数据库，与Matlab和R配合得很好。
  • GIS信息。P ostgreSQL不仅仅是一个金融数据库；它还包含一个叫做 "PostGIS "的GIS扩展，有数百个函数用于处理各种格式的几何数据。由于它具有极强的标准兼容性，PostGIS已经成为开源GIS领域的事实标准之一。
  • 科学数据。研 究和科学项目可以产生数千兆字节的数据，所有这些数据都必须以最经济和有效的方式进行管理。PostgreSQL具有强大的分析能力和强大的SQL引擎，使处理大量数据变得简单。

什么是MySQL？

MySQL 是一个关系型数据库管理系统（RDBMS），被广泛用于管理关系型数据库。结构化查询语言（SQL）被MySQL用来创建、更新和查询数据库。对于访问和管理任何数据库中的记录，SQL是使用最广泛的语言。MySQL是一个开源和免费的数据库软件，得到了甲骨文的支持，并在GNU许可证下发布。

与Microsoft SQL Server和Oracle数据库相比，MySQL是一个更快、更可扩展、更容易使用的数据库管理系统。它基于客户-服务器概念，这意味着数据库通常托管在服务器上，数据通过网络客户和工作站访问。服务器对客户的图形用户界面（GUI）查询做出响应，并提供所需的结果。MySQL可与各种操作系统和语言一起使用，包括PHP、PERL、JAVA、C++和C语言。

MySQL的主要特点

以下是MySQL大受欢迎的一些原因。

  • MySQL是一个开源的关系型数据库，这意味着你可以免费使用它。
  • 它以SQL语言为基础，是众所周知和广泛使用的语言。你可以用它运行对表、行、列和索引的查询。
  • 它将数据保存在表里，也被称为关系，是行和列的集合。
  • 它能很好地处理巨大的数据集，可以容纳多达5000万行或更多的表。
  • MySQL的适应性极强，开源的GPL许可证允许开发者和程序员快速修改SQL软件以满足他们的特定需求。

什么时候应该使用MySQL。关键用例

MySQL是企业在网络开发中最广泛使用的开源数据库。它是软件栈模型的一个重要方面，使创建网络应用程序和网站更加容易。下面列出了MySQL的正确用途。

  • MySQL是一个数据库管理系统，可以处理巨大的数据库，并有一个精心设计的用户管理系统。因此，它对于处理大量的数据和需要多个用户访问的情况是非常好的。
  • MySQL最普遍的应用是用于网络开发。MySQL与PHP或Perl等脚本语言结合使用，构建与MySQL数据库实时互动的网站。
  • MySQL是一个数据库管理系统，可用于各种原因，包括电子商务。它可以用来存储从客户信息到在线业务的大型产品库存的一切。
  • MySQL使数据库专业人员能够定制数据库服务器以满足其个人需求。由于MySQL的高速加载能力、独特的内存缓存和其他增强性能的特性，它可以满足任何系统的性能需求。
  • MySQL以其可扩展性而闻名，它可以管理高度嵌入的应用程序和大型数据仓库。

探索这些方法来连接PostgreSQL和MySQL

PostgreSQL是一个广泛使用的数据库管理系统，可以轻松地处理大量的数据。MySQL是一个关系型数据库管理系统。它是最早的数据库解决方案之一。当你需要将数据从PostgreSQL转移到MySQL时，你可以使用以下方法来实现。

方法1：使用Hevo连接PostgreSQL到MySQL服务器

Hevo Data，一个自动化的数据管道，为你提供了一个无忧无虑的解决方案，在几分钟内将数据从 PostgreSQL到**MySQL**，提供了一个无障碍的解决方案，在几分钟内通过一个易于使用的无代码界面。Hevo是完全管理和完全自动化的过程，同时也丰富了数据并将其转化为可分析的形式，而不需要写一行代码。

方法2：使用CSV文件手动连接PostgreSQL到MySQL

这种方法实施起来会很费时，而且有些乏味。你必须先从PostgreSQL手动导出数据到CSV，然后再次使用Shell工具将数据导入MySQL服务器。

方法3：使用pg2mysql php脚本手动连接PostgreSQL到MySQL

这种方法也很耗时，而且实施起来有些繁琐。你将不得不首先下载pg2mysql脚本，并手动从PostgreSQL导出数据到MySQL。在这种方法中，你还需要映射所有表的索引。

连接PostgreSQL到MySQL的方法

方法1：使用Hevo连接PostgreSQL到MySQL服务器

Hevo帮助你直接从 各种来源传输数据，如 PostgreSQL 数据库，商业智能工具，数据仓库，或你选择的目的地，如 MySQL 以一种完全无忧无虑和自动化的方式。Hevo是完全管理和完全自动化的过程，不仅从你想要的来源加载数据，而且还丰富了数据并将其转化为可分析的形式，而不需要写一行代码。它的容错架构确保以安全、一致的方式处理数据，实现零数据损失。

可以通过以下步骤来实现用Hevo连接PostgreSQL到MySQL。

  • 配置源。将Hevo数据与PostgreSQL连接起来，为你的管道提供一个独特的名称，以及数据库主机、数据库端口、数据库用户、数据库密码、数据库名称和关于你的数据源的细节。在配置PostgreSQL为源时，你也可以选择数据摄取方法。

  • 配置目的地。通过提供有关MySQL证书的信息，如目的地名称、数据库主机、数据库端口、数据库用户、数据库密码、数据库名称和模式名称，建立一个与MySQL的连接。

以下是尝试Hevo的更多理由。

  • 完全管理。它不需要管理和维护，因为Hevo是一个完全自动化的平台。
  • 数据转换。它提供了一个简单的界面来完善、修改和充实你要转移的数据。
  • 实时性：Hevo提供实时数据迁移。因此，你的数据总是准备好进行分析。
  • 模式管理。Hevo可以自动检测传入数据的模式，并将其映射到目的地模式。
  • 可扩展的基础设施。Hevo有内置的100多个来源的集成，可以帮助你根据需要扩展你的数据基础设施。
  • 实时监控。先进的监控为你提供了一个一站式的视角来观察数据管道内发生的所有活动。
  • 现场支持。Hevo团队24小时不间断地通过聊天、电子邮件和支持电话向客户提供特殊支持。

方法2：使用CSV文件手动连接PostgreSQL到MySQL

让我们在一个例子的帮助下了解这个手动连接PostgreSQL到MySQL的方法。

步骤1：提取表

将数据从你的PostgreSQL迁移到MySQL的第一步是从PostgreSQL中提取表的定义。最常见的方法是使用pg_dump，但让我们看看它是如何在第一个表（演员）上工作的。

-bash-4.2$ <strong>pg_dump -st actor dvdrental</strong>
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.8
-- Dumped by pg_dump version 12.8

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: actor; Type: TABLE; Schema: public; Owner: postgres
--

<span class="tadv-color" style="color:var(--paletteColor1, #dc9d4a)">CREATE TABLE public.actor (
    actor_id integer DEFAULT nextval('public.actor_actor_id_seq'::regclass) NOT NULL,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);</span>


ALTER TABLE public.actor OWNER TO postgres;

--
-- Name: actor actor_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

<span class="tadv-color" style="color:var(--paletteColor1, #dc9d4a)">ALTER TABLE ONLY public.actor
    ADD CONSTRAINT actor_pkey PRIMARY KEY (actor_id);</span>


--
-- Name: idx_actor_last_name; Type: INDEX; Schema: public; Owner: postgres
--

<span class="tadv-color" style="color:var(--paletteColor1, #dc9d4a)">CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name);</span>


--
-- Name: actor last_updated; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER last_updated BEFORE UPDATE ON public.actor FOR EACH ROW EXECUTE FUNCTION public.last_updated();


--
-- PostgreSQL database dump complete
--

第2步：表的定义

在从该输出中导入数据时，只有橙色的文本对我们有意义（最终也可以在以后创建索引）。

如果你熟悉MySQL，你会发现返回的SQL语法在那里不起作用。

让我们看一下表的定义。

<span class="tadv-color" style="color:var(--paletteColor1, #dc9d4a)">CREATE TABLE public.actor (
    actor_id integer DEFAULT nextval('public.actor_actor_id_seq'::regclass) NOT NULL,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);</span>

正如你所看到的。

  • 有一个前缀 是公共的。
  • 主键现在还没有定义（虽然以后会作为一个约束条件来定义）。
  • 下面是一个用户定义类型的例子**，** public.actor_id seq是序列。
  • 字符变化实际上是VARCHAR。
  • 时间戳列利用了各种MySQL特有的信息，不考虑时区。

这是一个手动步骤，必须重写语句以在MySQL中工作。

CREATE TABLE actor (
    actor_id integer auto_increment NOT NULL primary key,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update timestamp DEFAULT now() NOT NULL
);

第3步：添加二级索引

现在你可以使用新改写的语句向你的MySQL数据库服务实例添加二级索引。

你必须对dvdrental 模式中的所有表进行这一操作。

dvdrental-# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

当利用了众多的USER-DEFINED数据类型时，比如在表film中，这是最困难的部分。

rating public.mpaa_rating DEFAULT 'G'::public.mpaa_rating,

这样你就可以发现它的含义了。

dvdrental=# select enum_range(null::mpaa_rating);
      enum_range      
----------------------
 {G,PG,PG-13,R,NC-17}
(1 row)

你可以看到这是一个ENUM，所以我们要给这个列重命名如下。

rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',

第四步：将数据导出到CSV

现在是时候把PostgreSQL的数据导出到CSV了。

dvdrental=# \copy actor to '/vagrant/output/actor.csv' delimiter ',' CSV HEADER;
COPY 200
dvdrental=# \copy address to '/vagrant/output/address.csv' delimiter ',' CSV HEADER;
COPY 603
dvdrental=# \copy category to '/vagrant/output/category.csv' delimiter ',' CSV HEADER;
COPY 16
dvdrental=# \copy customer to '/vagrant/output/customer.csv' delimiter ',' CSV HEADER;
COPY 599
dvdrental=# \copy film to '/vagrant/output/film.csv' delimiter ',' CSV HEADER;
COPY 1000

第5步：将数据导入到MySQL

在你导出了所有你想导入的表之后，你可以利用MySQL Shell的 导入表工具。

importTable()可以在许多线程中使用（在这种情况下不行，因为数据太小了）。你可以为你想导入的每个表重复这个过程。**importTable()**也可以把几个文件合并成一个表。你可以通过查看以下内容来确认数据已经被导入。

你已经成功地使用CSV文件将数据从PostgreSQL迁移到MySQL。

方法3：使用pg2mysql php脚本手动连接PostgreSQL到MySQL

步骤1：下载脚本和PHP

下载脚本并解压存档是使用pg2mysql php脚本连接PostgreSQL到MySQL的第一步。

wget http://www.lightbox.ca/pg2mysql/pg2mysql-1.9.tar.bz2  (contains original script from the author)

wget https://www.easyredmine.com/ER/media/knowledge-base/pg2mysql-1.9.zip (contains our modified script for php version 7.4)

tar -xvf pg2mysql-1.9.tar.bz2(in case of original script from the author)
unzip pg2mysql-1.9.zip (in case of our modified script for php version 7.4)

安装PHP

sudo apt install php7.4-cli (in case of our modified script)

第2步：为PostgreSQL创建转储数据库并运行它

制作一个PostgreSQL数据库的**.sql** dump，确保使用**"-format p -inserts**"选项。

sudo -u postgres pg_dump --format p --inserts DBNAME_HERE > /path/to/file.sql

导航到 pg2mysql-1.9文件夹。

cd pg2mysql-1.9/

运行这个你已经创建的文件。

php pg2mysql_cli.php /path/to/pd/dump/file.sql /path/where/to/save/mysql/file.sql

会有一些像这样的行。

Completed!     30820 lines        5539 sql chunks
Notes:
 - No its not perfect
 - Yes it discards ALL stored procedures
 - Yes it discards ALL queries except for CREATE TABLE and INSERT INTO 
 - Yes you can email us suggestsions: info[AT]lightbox.org
    - In emails, please include the Postgres code, and the expected MySQL code
 - If you're having problems creating your postgres dump, make sure you use "--format p --inserts"
 - Default output engine if not specified is MyISAM"
  • 现在将创建更正后的 sql dump文件 。建议你阅读它并尽可能用InnoDB 替换MyISAM 。
  • 在这一点上，你可以将转储文件恢复到一个干净的MySQL数据库。
  • 由于该脚本不保存索引（创建者提到过），你必须手动将其添加到每个表中（没有索引也能工作，但可能会导致严重的性能问题）。你必须手动添加它们，或者使用你自己的自定义脚本来这样做。

手动连接PostgreSQL到MySQL的局限性

  • 从PostgreSQL到MySQL的数据传输只是单向的。双向 同步对于保持两个工具的更新是必不可少的。
  • 由于记录必须定期更新，手工方法需要时间。这浪费了时间和资源，可以更好地用于更重要的业务任务。
  • 在多个平台上更新当前数据和维护工作流程，需要大量的工程带宽，这可能是许多用户感到沮丧的原因。
  • 在数据传输过程中，没有转化的空间。对于那些希望在将数据从PostgreSQL导出到MySQL之前更新其数据的公司来说，这可能是一个相当大的缺点。

总结

在这篇文章中，在简单介绍了PostgreSQL 的突出特点和使用情况后，你对如何将PostgreSQL 连接到MySQL 有了一丝了解。本文谈到的方法是使用 CSV文件和pg2mysql php脚本。这个过程对初学者来说可能有点困难。此外，你将不得不每次更新数据，而这正是Hevo 的救命稻草!

Hevo Data为其用户提供了一个更简单的平台来整合来自100多个来源的数据进行分析。它是一个无代码的数据管道，可以帮助你结合来自多个来源的数据，如PostgreSQL。你可以使用它将数据从多个数据源转移到你的数据仓库、数据库或你选择的目的地，如MySQL 。它为你提供了一个一致和可靠的解决方案来实时管理数据，确保你总是在你所期望的目的地拥有可分析的数据。

avatar
文章
阅读
粉丝