如何使用 pgLoader 将 MySQL 数据库迁移到 PostgreSQL

介绍

PostgreSQL,也被称为Postgres,是一个开源的关系式数据库管理系统(RDBMS)。它在近几年中出现了急剧的普及增长(https://db-engines.com/en/ranking_trend/system/PostgreSQL),许多开发人员和公司将数据从其他数据库解决方案迁移到Postgres。

将数据库迁移的前景可能令人恐惧,尤其是在从一个数据库管理系统迁移到另一个时。 pgLoader是一个开源数据库迁移工具,旨在简化迁移到PostgreSQL的过程。

本教程提供有关如何安装 pgLoader 并使用它将远程 MySQL 数据库迁移到 PostgreSQL 通过 SSL 连接的说明。

前提条件

要完成本教程,您将需要以下内容:

访问2个服务器,每个服务器运行Ubuntu 18.04。两个服务器都应该有一个防火墙和一个拥有所配置的sudo权限的非root用户. 要设置这些设置, 您可以遵循我们的 [Ubuntu 18. 04] (https://andsky.com/tech/tutorials/initial-server-setup-with-ubuntu-18-04) 初始服务器设置指南。
MySQL已安装在一个服务器上. 为了建立这一机制,请遵循我们关于如何在Ubuntu 18.04上安装 MySQL的指南第1、2和3段。请注意,为了完成此处链接的所有先决条件教程,您需要配置您的root** MySQL用户用密码认证,如MySQL安装指南的[步骤3] (https://andsky.com/tech/tutorials/how-to-install-mysql-on-ubuntu-18-04#step-3-%E2%80%94-(optional)-调整-用户-认证-和-特权]所描述.
PostgreSQL 安装在其他服务器上. 要设置此功能,请填写我们的指南如何在Ubuntu 18.04上安装和使用PostgreSQL步骤1。
您的** MySQL 服务器** 也应配置以接受加密连接。要设置此选项, 请完成我们关于 [如何在 Ubuntu 18. 04 (https://andsky.com/tech/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-18-04) 为 MySQL 配置 SSL/TLS 的教程的每个步骤, 包括可选 [** Step 6** (https://andsky.com/tech/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-18-04#step-6-%E2%80%94-(optional) - 配置 - 验证 - 为 Mysql - 连接] 。当您遵循此指南时, 请务必使用您的 ** PostgreSQL 服务器 ** 作为 MySQL 客户端机, 因为您需要能够从您的 MySQL 服务器连接。 Postgres 机器,以便用 pgLoader 迁移数据。 (英语)

请注意,在本指南中,您安装 MySQL 的服务器将被称为MySQL 服务器**,并且在本机器上运行的任何命令都将显示为蓝色背景,如下:

1[environment second]

同样,本指南会将其他服务器称为PostgreSQL或Postgres服务器,并且必须在该机器上运行的任何命令都将显示在红色背景下:

1[environment third]

请记住这些,当你遵循本教程,以避免任何混淆。

步骤 1 — (可选)在MySQL中创建示例数据库和表

此步骤描述了创建测试数据库并将其填充到虚假数据的过程,我们鼓励您使用此测试案例练习使用 pgLoader,但如果您已经有想要迁移的数据库,您可以继续到下一步。

在您的 MySQL 服务器上打开 MySQL 提示:

1[environment second]
2mysql -u root -p

输入您的 root MySQL 用户密码后,您将看到 MySQL 提示。

您可以随意命名您的数据库,但在本指南中,我们将命名它为source_db:

1[environment second]
2CREATE DATABASE source_db;

然后用使用命令切换到此数据库:

1[environment second]
2USE source_db;

1[environment second]
2[secondary_label Output]
3Database changed

在此数据库中,使用以下命令创建示例表. 在这里,我们将命名此表 sample_table,但请自由给它另一个名称:

1[environment second]
2CREATE TABLE sample_table (
3    employee_id INT PRIMARY KEY,
4    first_name VARCHAR(50),
5    last_name VARCHAR(50),
6    start_date DATE,
7    salary VARCHAR(50)
8);

然后用以下命令填充此表中的一些样本员工数据:

1[environment second]
2INSERT INTO sample_table (employee_id, first_name, last_name, start_date, salary) 
3VALUES (1, 'Elizabeth', 'Cotten', '2007-11-11', '$105433.18'),
4(2, 'Yanka', 'Dyagileva', '2017-10-30', '$107540.67'),
5(3, 'Lee', 'Dorsey', '2013-06-04', '$118024.04'),
6(4, 'Kasey', 'Chambers', '2010-08-18', '$116456.98'),
7(5, 'Bram', 'Tchaikovsky', '2018-09-16', '$61989.50');

接下来,您可以关闭 MySQL 提示:

1[environment second]
2exit

现在,你有一个样本数据库载有虚假数据,你可以转到下一步,你将安装pgLoader在你的PostgreSQL服务器上。

步骤 2 – 安装 pgLoader

pgLoader 是一个可以从各种不同的来源加载数据到 PostgreSQL 数据库的程序,它使用 PostgreSQL 的 COPY 命令(http://www.postgresql.org/docs/current/interactive/sql-copy.html)将数据从源数据库或文件复制到目标 PostgreSQL 数据库,例如 comma-separated values (CSV) 文件。

但是,在本指南中,我们将利用pgLoader的useSSL选项,该功能允许通过SSL连接从MySQL迁移。此功能仅在pgLoader的版本3.5.1和更新的版本中可用,但从默认的Ubuntu存储库中安装它将从本文中安装版本3.4.1.因此,本指南将概述如何使用项目的GitHub存储库的源代码安装最新版本的pgLoader。

在安装 pgLoader 之前,您需要安装它的依赖性. 如果您最近没有这样做,请更新您的 Postgres 服务器的 ** 包索引:

1[environment third]
2sudo apt update

然后安装以下软件包:

sbcl: A Common Lisp编译器
unzip: A de-archiver for .zip files
libsqlite3-dev: A collection of files development for SQLite 3
gawk: short for "GNU awk", a pattern scanning and processing language
curl: A command line tool for transferring data from an URL
make: A utility for managing package compilation
`freet-ds-dev: A client library for MS SQL and Sybase databases
lib-dev: A library for reading, creating, and modif

使用以下命令来安装这些依赖:

1[environment third]
2sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

当被提示时,通过按ENTER来确认您想要安装这些包。

接下来,导航到gLoader GitHub项目的 Releases页面并找到最新版本. 对于本指南,我们将使用本文写作时的最新版本: 版本 3.6.2。滚到其 Assets菜单,并复制标记为 Source code的 tar.gz 文件的链接。然后将链接粘贴到下面的 curl 命令中,取代了突出的 URL:

1[environment third]
2curl -fsSLO https://github.com/dimitri/pgloader/archive/v3.6.2.tar.gz

cURL是许多用于传输数据的操作系统上可用的命令行工具,它读取任何存储在传递给它的URL上的数据,并将内容打印到系统的输出中。

这个弯曲命令包括O选项,该命令将文件内容输出到与源文件相同的本地文件 - v3.6.2.tar.gz - 而不是服务器的标准输出。请注意,该命令还包括-fsSL选项,这些选项在一起,基本上告诉cURL默默失败,这意味着如果由于某种原因,cURL无法联系GitHub,它不会输出结果的错误代码到本地文件。

在下载它弯曲后,用以下命令提取 tarball:

1[environment third]
2tar xvf v3.6.2.tar.gz

这将在您的服务器上创建一些新的目录和文件。导航到新的 pgLoader 家长目录:

1[environment third]
2cd pgloader-3.6.2/

然后使用做实用程序来编译pgloader二进制:

1[environment third]
2make pgloader

此命令可能需要几分钟才能构建pgloader二进制。

一旦完成,将二进制文件移动到 /usr/local/bin 目录,这是 Ubuntu 寻找可执行文件的位置:

1[environment third]
2sudo mv ./build/bin/pgloader /usr/local/bin/

您可以通过检查其版本来测试 pgLoader 是否正确安装,如下:

1[environment third]
2pgloader --version

1[secondary_label Output]
2[environment third]
3pgloader version "3.6.2"
4compiled with SBCL 1.4.5.debian

pgLoader 现在已安装,但在您开始迁移之前,您需要对您的 PostgreSQL 和 MySQL 实例进行一些配置更改。

步骤 3 – 创建 PostgreSQL 角色和数据库

pgloader命令通过复制源数据,无论是从文件还是直接从数据库,并将其插入到PostgreSQL数据库中。

PostgreSQL 通过使用 roles来管理数据库访问。取决于角色配置的方式,它可以被认为是数据库用户或数据库用户组。在大多数 RDBMS 中,您可以使用CREATE USER SQL 命令创建用户。

在 PostgreSQL 中,您使用 Identification Protocol或 ident,身份验证方法是默认的,而不是使用密码。这涉及 PostgreSQL 使用客户端的 Ubuntu 用户名并使用它作为允许的 Postgres 数据库用户名。

pgLoader 可以通过使用 ident 方法进行身份验证的角色将数据加载到 Postgres 数据库,只要该角色与发出pgloader命令的 Linux 用户配置文件共享相同的名称。

在您的 Postgres 服务器上运行以下命令来创建一个新的角色. 请注意-P旗帜,该旗帜说createuser要求您为新的角色输入密码:

1[environment third]
2sudo -u postgres createuser --interactive -P

您可能首先被要求提供您的sudo密码. 然后,脚本将提示您输入新角色的名称。在本指南中,我们将把这个角色称为 pgloader_pg:

1[secondary_label Output]
2[environment third]
3Enter name of role to add: pgloader_pg

接下来,‘createuser’会提示你输入并确认这个角色的密码. 请确保记住这个密码,因为你需要它来执行步骤5中的迁移。

1[secondary_label Output]
2[environment third]
3Enter password for new role: 
4Enter it again:

最后,脚本会询问您是否应该将新角色归类为超级用户。在PostgreSQL中,使用超级用户角色连接到数据库,允许您绕过数据库的所有权限检查,除了登录权限之外。因此,超级用户权限不应该轻松使用,而PostgreSQL文档(https://www.postgresql.org/docs/current/role-attributes.html)建议您将大部分数据库工作作为非超级用户角色。但是,由于pgLoader需要广泛的权限来访问和将数据加载到表中,您可以安全地授予这个新角色超级用户权限。

1[secondary_label Output]
2[environment third]
3. . .
4Shall the new role be a superuser? (y/n) y

由于pgLoader还需要一个目标数据库,它可以加载源数据,运行以下命令来创建一个。

1[environment third]
2sudo -u postgres createdb new_db

如果没有任何错误,这个命令将没有任何输出完成。

现在你有一个专用的 PostgreSQL 用户和一个空的数据库,你可以加载你的 MySQL 数据,你需要做一些更改,在完成迁移之前,你需要创建一个专用的 MySQL 用户,可以访问你的源数据库,并添加你的客户端证书到 Ubuntu 的可信证书商店。

步骤 4 — 在 MySQL 中创建专用用户并管理证书

保护数据免受黑客攻击是任何数据库管理员工作中最重要的部分之一. 将数据从一台机器迁移到另一台机器,为恶意行为者开辟了一个机会,如果没有加密,则可以通过网络连接移动的数据包。

首先,打开您的MySQL提示:

1[environment second]
2mysql -u root -p

从MySQL提示,使用以下命令CREATE USER创建一个新的MySQL用户. 我们将这个用户命名为 pgloader_my. 因为这个用户只会从您的PostgreSQL服务器访问MySQL,请确保将your_postgres_server_ip替换为您的PostgreSQL服务器的公共IP地址。

1[environment second]
2CREATE USER 'pgloader_my'@'your_postgres_server_ip' IDENTIFIED BY 'password' REQUIRE SSL;

请注意本命令末尾的要求SSL条款,这将限制用户只通过安全的SSL连接访问数据库。

接下来,允许用户访问目标数据库及其所有表. 在此,我们将指定我们在可选步骤 1 中创建的数据库,但如果您有自己的数据库,您想迁移,请使用其名称而不是 source_db:

1[environment second]
2GRANT ALL ON source_db.* TO 'pgloader_my'@'your_postgresql_server_ip';

然后运行FLUSH PRIVILEGES命令来重新加载授权表,启用权限更改:

1[environment second]
2FLUSH PRIVILEGES;

在此之后,您可以关闭 MySQL 提示:

1[environment second]
2exit

现在回到您的 Postgres 服务器并尝试登录 MySQL 服务器作为新的 pgloader_my 用户. 如果您遵循配置 SSL/TLS for MySQL上的前提指南,那么您已经在您的 PostgreSQL 服务器上安装了 mysql-client,您应该能够使用以下命令连接:

1[environment third]
2mysql -u pgloader_my -p -h your_mysql_server_ip

如果成功,你会看到 MySQL 提示:

1[environment third]

确认您的 pgloader_my用户可以成功连接后,继续并关闭提示:

1[environment third]
2exit

在这一点上,你有一个专用的MySQL用户,可以从你的Postgres机器访问源数据库,但是,如果你要尝试使用SSL迁移你的MySQL数据库,尝试就会失败。

原因是pgLoader无法读取MySQL的配置文件,因此不知道在先决条件(SSL/TLS配置指南)中复制到您的PostgreSQL服务器的CA证书或客户端证书的位置,而不是忽略SSL要求,但pgLoader在需要SSL连接到MySQL的情况下需要使用可信证书。

要做到这一点,请将 ca.pem 和 client-cert.pem 文件复制到 /usr/local/share/ca-certificates/ 目录. 请注意,您还必须更名这些文件,以便它们具有 .crt 文件扩展。如果您不更名它们,您的系统将无法识别您添加了这些新证书:

1[environment third]
2sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt
3sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt

此程序在/usr/local/share/ca-certificates中寻找证书,将任何新证书添加到/etc/ssl/certs/目录中,并根据/etc/ssl/certs/目录的内容生成一个值得信赖的SSL证书列表 - ca-certificates.crt:

1[environment third]
2sudo update-ca-certificates

1[secondary_label Output]
2[environment third]
3Updating certificates in /etc/ssl/certs...
42 added, 0 removed; done.
5Running hooks in /etc/ca-certificates/update.d...
6done.

有了这一点,你已经准备好将你的MySQL数据库迁移到PostgreSQL。

步骤5:迁移数据

现在你已经配置了从 PostgreSQL 服务器到 MySQL 服务器的远程访问,你已经准备好开始迁移。

<$>[注] 注: 在采取任何可能影响数据完整性的行动之前,备份您的数据库很重要,但在使用 pgLoader 进行迁移时,这并不必要,因为它不会删除或转换数据;它只会复制数据。

也就是说,如果你感到谨慎,想在迁移之前备份你的数据,你可以使用mysqldump实用程序这样做。

pgLoader 允许用户使用单个命令迁移整个数据库. 对于从 MySQL 数据库迁移到单独服务器上的 PostgreSQL 数据库,命令将具有以下语法:

1pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name?option_1=value&option_n=value postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value

这包括pgloader命令和两个 _connection 字符串,第一个用于源数据库,第二个用于目标数据库。这两个连接字符串都以声明连接字符串指向哪种类型的 DBMS 开始,其次是访问数据库的用户名和密码(分隔为一个密码),安装数据库的服务器的主机地址,数据库 pgLoader 应该瞄准的名称,以及影响 pgLoader 行为的各种选项。

使用本教程中早些时候定义的参数,您可以使用下列结构的命令迁移您的MySQL数据库。

1[environment third]
2pgloader mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true postgresql://pgloader_pg:postgresql_password@localhost/new_db

请注意,此命令包括 MySQL 连接字符串中的 useSSL 选项。通过将此选项设置为 true,pgLoader 将通过 SSL 连接到 MySQL。

如果此命令成功,您将看到一个输出表,描述迁移的方式:

 1[secondary_label Output]
 2[environment third]
 3. . .
 4             table name errors rows bytes total time
 5-----------------------  ---------  ---------  ---------  --------------
 6        fetch meta data 0 2 0.111s
 7         Create Schemas 0 0 0.001s
 8       Create SQL Types 0 0 0.005s
 9          Create tables 0 2 0.017s
10         Set Table OIDs 0 1 0.010s
11-----------------------  ---------  ---------  ---------  --------------
12 source_db.sample_table 0 5 0.2 kB 0.048s
13-----------------------  ---------  ---------  ---------  --------------
14COPY Threads Completion 0 4 0.052s
15 Index Build Completion 0 1 0.011s
16         Create Indexes 0 1 0.006s
17        Reset Sequences 0 0 0.014s
18           Primary Keys 0 1 0.001s
19    Create Foreign Keys 0 0 0.000s
20        Create Triggers 0 0 0.000s
21       Install Comments 0 0 0.000s
22-----------------------  ---------  ---------  ---------  --------------
23      Total import time          ✓          5 0.2 kB 0.084s

要检查数据是否正确迁移,请打开 PostgreSQL 提示:

1[environment third]
2sudo -i -u postgres psql

从那里,连接到您加载数据的数据库:

1[environment third]
2\c new_db

然后运行以下查询来测试迁移数据是否存储在您的 PostgreSQL 数据库中:

1[environment third]
2SELECT * FROM source_db.sample_table;

<$>[注] 注: 请注意本查询中的FROM条款,说明在source_db中所保留的sample_table(https://en.wikipedia.org/wiki/Database_schema):

1[environment third]
2. . . FROM source_db.sample_table;

这被称为 qualified name. 您可以进一步指定 完全合格的名称,包括数据库的名称以及图表和表中的名称:

1[environment third]
2. . . FROM new_db.source_db.sample_table;

當您在 PostgreSQL 資料庫中執行查詢時,如果表被保留在默認的「公用」計劃範圍內,您不必這樣做。您必須這樣做的原因在於,當 pgLoader 將資料載入 Postgres 時,它會創建並針對以原始資料庫命名的新計劃 - 在這種情況下,『source_db』。這是 pgLoader 對 MySQL 到 PostgreSQL 遷移的默認行為。但是,您可以使用載入檔案指示 pgLoader 將表格的計劃變更為『public』。

如果数据确实是正确加载的,您将在查询的输出中看到以下表:

 1[secondary_label Output]
 2[environment third]
 3 employee_id | first_name |  last_name  | start_date |   salary   
 4-------------+------------+-------------+------------+------------
 5           1 | Elizabeth  | Cotten      | 2007-11-11 | $105433.18
 6           2 | Yanka      | Dyagileva   | 2017-10-30 | $107540.67
 7           3 | Lee        | Dorsey      | 2013-06-04 | $118024.04
 8           4 | Kasey      | Chambers    | 2010-08-18 | $116456.98
 9           5 | Bram       | Tchaikovsky | 2018-09-16 | $61989.50
10(5 rows)

要关闭 Postgres 提示,运行以下命令:

1[environment third]
2\q

现在我们已经了解了如何在网络上迁移MySQL数据库并将其加载到PostgreSQL数据库中,我们将讨论一些其他常见的迁移场景,其中pgLoader可能有用。

步骤6:探索其他迁移选项

pgLoader 是一个非常灵活的工具,可以在各种情况下很有用,在这里,我们将快速查看一些其他方法,您可以使用 pgLoader 将 MySQL 数据库迁移到 PostgreSQL。

使用 pgLoader 加载文件迁移

在 pgLoader 的背景下,一个 load file 或 command file 是告诉 pgLoader 如何执行迁移的文件,该文件可以包含影响 pgLoader 的行为的命令和选项,为您提供更精细的控制,以便您如何将数据加载到 PostgreSQL,并允许您执行复杂的迁移。

pgLoader 的文档提供有关如何使用和扩展这些文件以支持多个迁移类型的全面指示,所以在这里我们将通过一个相对原始的例子进行工作. 我们将执行我们在步骤 5中执行的相同迁移,但还将包括一个ALTER SCHEMA命令来改变new_db数据库的方案从source_db到public。

首先,使用您喜爱的文本编辑器在 Postgres 服务器上创建一个新的加载文件:

1[environment third]
2nano pgload_test.load

然后添加以下内容,确保更新突出值以与您自己的配置一致:

 1[label pgload_test.load]
 2[environment third]
 3LOAD DATABASE
 4     FROM mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true
 5     INTO pgsql://pgloader_pg:postgresql_password@localhost/new_db
 6
 7 WITH include drop, create tables
 8
 9ALTER SCHEMA 'source_db' RENAME TO 'public'
10;

以下是这些条款中的每一个做什么:

*`LOAD DATABASE': 本行指示 pgLoader 从单独的数据库中装入数据,而不是文件或数据存档.

`FROM': 此条款指定了源数据库。在这种情况下,它指出了我们在[步骤1](https://andsky.com/tech/tutorials/how-to-migrate-mysql-database-to-postgres-using-pgloader#step-1-%E2%80%94-(optional-创建-a-sample-database-和-table-in-mysql]中创建的MySQL数据库的连接字符串.
INTO :同样,本行指定了 PostgreSQL 数据库, pgLoader 将数据加载到其中。
WITH :本条款允许您定义 pgLoader 的特定行为。您可以找到与 MySQL 迁移兼容的 WITH 选项的完整列表。在这个例子中,我们只包括两个选项: -包括倒放': 当使用此选项时, pgLoader 会将目标 PostgreSQL 数据库中同时出现在源 MySQL 数据库中的任何表格降出. 如果您在将数据迁移到现有的 PostgreSQL 数据库时使用此选项, 您应该备份整个数据库, 以避免丢失任何数据。 -创建表格': 此选项告诉 pgLoader 在目标 PostgreSQL 数据库中根据 MySQL 数据库中持有的元数据创建新的表格. 如果使用不创建表格的相反选项,则目标表格必须在迁移前在目标Postgres数据库中存在。
ALTER SCHEMA : 在WITH条款之后,您可以添加像这样的特定 SQL 命令来指示 pgLoader 执行额外的动作。在此,我们指示pgLoader将新的Postgres数据库的计划从'source_db'改为'public',但只有在它创建了这个计划之后。请注意,您还可以在BEFORE LOAD DO等其他条款中嵌入这些命令,指示pgLoader在迁移过程中的特定地点执行这些命令。 (英语)

这是一个示范的例子,你可以包括在一个加载文件,以修改pgLoader的行为. 可以添加到加载文件的条款的完整列表,以及他们做什么,可以在官方pgLoader文档中找到。

在您完成添加此内容后,保存并关闭加载文件. 要使用它,请将文件名作为一个参数添加到 pgloader 命令中:

1[environment third]
2pgloader pgload_test.load

要测试迁移是否成功,请打开 Postgres 提示:

1[environment third]
2sudo -u postgres psql

然后连接到数据库:

1[environment third]
2\c new_db

然后运行以下查询:

1[environment third]
2SELECT * FROM sample_table;

 1[secondary_label Output]
 2[environment third]
 3 employee_id | first_name |  last_name  | start_date |   salary   
 4-------------+------------+-------------+------------+------------
 5           1 | Elizabeth  | Cotten      | 2007-11-11 | $105433.18
 6           2 | Yanka      | Dyagileva   | 2017-10-30 | $107540.67
 7           3 | Lee        | Dorsey      | 2013-06-04 | $118024.04
 8           4 | Kasey      | Chambers    | 2010-08-18 | $116456.98
 9           5 | Bram       | Tchaikovsky | 2018-09-16 | $61989.50
10(5 rows)

此输出证实 pgLoader 成功迁移了数据,并且我们在加载文件中添加的ALTER SCHEMA命令按预期工作,因为我们不需要在查询中指定source_db方案来查看数据。

请注意,如果您打算使用加载文件将存储在一个数据库中的数据迁移到位于单独的计算机上的另一个数据库,您仍然需要调整任何相关的网络和防火墙规则,以便迁移成功。

将 MySQL 数据库本地迁移到 PostgreSQL

您可以使用 pgLoader 将 MySQL 数据库迁移到同一台机器上的 PostgreSQL 数据库. 您所需的只是运行从 Linux 用户配置文件的迁移命令,可以访问 root MySQL 用户:

1pgloader mysql://root@localhost/source_db pgsql://sammy:postgresql_password@localhost/target_db

执行这样的本地迁移意味着您不需要对MySQL的默认网络配置或系统防火墙规则进行任何更改。

从 CSV 文件迁移

您还可以加载一个 PostgreSQL 数据库,其中包含来自 CSV 文件的数据。

假设您有一个名为load.csv的 CSV 数据文件,将其加载到 Postgres 数据库的命令可能看起来如下:

1pgloader load.csv pgsql://sammy:password@localhost/target_db

由于 CSV 格式尚未完全标准化,所以在直接从 CSV 文件上加载数据时可能会遇到问题. 幸运的是,您可以通过使用 pgLoader 命令行选项或在加载文件中指定各种选项来纠正异常。

迁移到管理 PostgreSQL 数据库

还可以从自我管理的数据库迁移到受管理的 PostgreSQL 数据库。为了说明这种迁移的外观,我们将使用MySQL服务器和DigitalOcean Managed PostgreSQL 数据库。我们还将使用我们在步骤 1中创建的样本数据库,但如果您跳过了这一步并拥有您想要迁移的数据库,您可以指向该数据库。

<$>[注] 注: 有关如何设置数字海洋管理数据库的说明,请参阅我们的管理数据库快速启动指南。

对于此迁移,我们不需要pgLoader的useSSL选项,因为它只与远程MySQL数据库工作,我们将从本地MySQL数据库运行此迁移,但是,当我们加载并连接到DigitalOcean Managed PostgreSQL数据库时,我们将使用sslmode=require选项,这将确保您的数据保持保护。

由于我们这次不使用useSSL,您可以使用apt来安装pgLoader以及postgresql-client包,这将允许您从您的MySQL服务器访问管理的PostgreSQL数据库:

1[environment second]
2sudo apt install pgloader postgresql-client

接下来,您可以运行pgloader命令来迁移数据库. 要做到这一点,您需要管理数据库的连接字符串。

对于DigitalOcean Managed Databases,您可以从云控制面板复制连接字符串。首先,在左侧的侧栏菜单中单击 Databases,然后选择您想要迁移数据的数据库。然后向下滚动到 连接详细信息部分。点击下载菜单,然后选择 连接字符串。然后,单击 **复制字符串按钮将字符串复制到您的剪辑板并粘贴到下面的迁移命令中,取代这里所示的示例 PostgreSQL 连接字符串。这将将您的 MySQL 数据库迁移到 defaultdb PostgreSQL 数据库中作为 doadmin PostgreSQL 角色:

1[environment second]
2pgloader mysql://root:password@localhost/source_db postgres://doadmin:password@db_host/defaultdb?sslmode=require

接下来,您可以使用相同的连接字符串作为对数为psql来连接到受管理的 PostgreSQL 数据库,并确认迁移成功:

1[environment second]
2psql postgres://doadmin:password@db_host/defaultdb?sslmode=require

然后,运行以下查询来检查 pgLoader 是否正确地迁移了数据:

1[environment second]
2SELECT * FROM source_db.sample_table;

 1[secondary_label Output]
 2[environment second]
 3 employee_id | first_name |  last_name  | start_date |   salary   
 4-------------+------------+-------------+------------+------------
 5           1 | Elizabeth  | Cotten      | 2007-11-11 | $105433.18
 6           2 | Yanka      | Dyagileva   | 2017-10-30 | $107540.67
 7           3 | Lee        | Dorsey      | 2013-06-04 | $118024.04
 8           4 | Kasey      | Chambers    | 2010-08-18 | $116456.98
 9           5 | Bram       | Tchaikovsky | 2018-09-16 | $61989.50
10(5 rows)

这证实 pgLoader 已成功将您的 MySQL 数据库迁移到您管理的 PostgreSQL 实例。

结论

pgLoader是一个灵活的工具,可以执行一个数据库迁移在一个单一的命令. 通过几个配置调整,它可以通过安全的SSL / TLS连接将整个数据库从一个物理机器迁移到另一个。

将数据迁移到 PostgreSQL 后,您可能会发现以下教程感兴趣: