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

介绍 (Introduction)

PostgreSQL, also known as “Postgres,” is an open-source relational database management system (RDBMS). It has seen a drastic growth in popularity in recent years, with many developers and companies migrating their data to Postgres from other database solutions.

PostgreSQL ，也称为“ Postgres”，是一种开源关系数据库管理系统(RDBMS)。 近年来，它的受欢迎程度急剧增长 ，许多开发人员和公司将其数据从其他数据库解决方案迁移到Postgres。

The prospect of migrating a database can be intimidating, especially when migrating from one database management system to another. pgLoader is an open-source database migration tool that aims to simplify the process of migrating to PostgreSQL. It supports migrations from several file types and RBDMSs — including MySQL and SQLite — to PostgreSQL.

迁移数据库的前景令人生畏，尤其是从一个数据库管理系统迁移到另一个数据库时。 pgLoader是一个开源数据库迁移工具，旨在简化迁移到PostgreSQL过程。 它支持从多种文件类型和RBDMS(包括MySQL和SQLite )到PostgreSQL迁移。

This tutorial provides instructions on how to install pgLoader and use it to migrate a remote MySQL database to PostgreSQL over an SSL connection. Near the end of the tutorial, we will also briefly touch on a few different migration scenarios where pgLoader may be useful.

本教程提供了有关如何安装pgLoader以及如何使用它通过SSL连接将远程MySQL数据库迁移到PostgreSQL说明。 在本教程快要结束时，我们还将简要介绍pgLoader可能有用的几种不同的迁移方案。

先决条件 (Prerequisites)

To complete this tutorial, you’ll need the following:

要完成本教程，您将需要以下内容：

• Access to two servers, each running Ubuntu 18.04. Both servers should have a firewall and a non-root user with sudo privileges configured. To set these up, you can follow our Initial Server Setup guide for Ubuntu 18.04.

  访问两台服务器，每台服务器都运行Ubuntu 18.04。 两台服务器都应具有防火墙和配置了sudo特权的非root用户。 要进行设置，您可以遵循我们的Ubuntu 18.04初始服务器设置指南 。

• MySQL installed on one of the servers. To set this up, follow Steps 1, 2, and 3 of our guide on How To Install MySQL on Ubuntu 18.04. Please note that in order to complete all the prerequisite tutorials linked here, you will need to configure your root MySQL user to authenticate with a password, as described in Step 3 of the MySQL installation guide.

  MySQL安装在其中一台服务器上 。 要进行设置，请遵循我们的指南如何在Ubuntu 18.04上安装MySQL的 步骤1、2和3 。 请注意，为了完成此处链接的所有先决条件教程，您将需要配置MySQL 根用户以使用密码进行身份验证，如MySQL安装指南的步骤3中所述。

• PostgreSQL installed on the other server. To set this up, complete Step 1 of our guide How To Install and Use PostgreSQL on Ubuntu 18.04.

  PostgreSQL安装在另一台服务器上 。 要进行此设置，请完成我们的指南如何在Ubuntu 18.04上安装和使用PostgreSQL 步骤1 。

• Your MySQL server should also be configured to accept encrypted connections. To set this up, complete every step of our tutorial on How To Configure SSL/TLS for MySQL on Ubuntu 18.04, including the optional Step 6. As you follow this guide, be sure to use your PostgreSQL server as the MySQL client machine, as you will need to be able to connect to your MySQL server from your Postgres machine in order to migrate the data with pgLoader.

  您的MySQL服务器也应配置为接受加密连接。 要进行设置，请完成我们关于如何在Ubuntu 18.04上为MySQL配置SSL / TLS的教程的每个步骤，包括可选的步骤6 。 遵循本指南时，请确保将PostgreSQL服务器用作MySQL客户端计算机，因为您将需要能够从Postgres计算机连接到MySQL服务器，以便使用pgLoader迁移数据。

Please note that throughout this guide, the server on which you installed MySQL will be referred to as the “MySQL server” and any commands that should be run on this machine will be shown with a blue background, like this:

请注意，在本指南中，安装了MySQL的服务器将称为“ MySQL服务器 ”，并且应在该计算机上运行的所有命令都将以蓝色背景显示，如下所示：

Similarly, this guide will refer to the other server as the “PostgreSQL” or “Postgres” server and any commands that must be run on that machine will be shown with a red background:

同样，本指南将另一台服务器称为“ PostgreSQL ”或“ Postgres”服务器，并且必须在该计算机上运行的所有命令将以红色背景显示：

Please keep these in mind as you follow this tutorial so as to avoid any confusion.

在遵循本教程的过程中，请牢记这些，以免造成任何混淆。

步骤1 —(可选)在MySQL中创建示例数据库和表 (Step 1 — (Optional) Creating a Sample Database and Table in MySQL)

This step describes the process of creating a test database and populating it with dummy data. We encourage you to practice using pgLoader with this test case, but if you already have a database you want to migrate, you can move on to the next step.

此步骤描述了创建测试数据库并向其填充虚拟数据的过程。 我们鼓励您在此测试用例中练习使用pgLoader，但是如果您已经有要迁移的数据库，则可以继续进行下一步 。

Start by opening up the MySQL prompt on your MySQL server:

首先打开MySQL服务器上MySQL提示符：

• mysql -u root -p
  mysql -u root -p

After entering your root MySQL user’s password, you will see the MySQL prompt.

输入您的root MySQL用户密码后，您将看到My​​SQL提示。

From there, create a new database by running the following command. You can name your database whatever you’d like, but in this guide we will name it source_db:

在此处，通过运行以下命令来创建新数据库。 您可以随意命名数据库，但是在本指南中，我们将其命名为source_db ：

• CREATE DATABASE source_db;

  创建数据库source_db ;

Then switch to this database with the USE command:

然后USE命令切换到该数据库：

• USE source_db;

  使用source_db ;

   
   
   

    
    
    Output
   
   
   
Database changed

Within this database, use the following command to create a sample table. Here, we will name this table sample_table but feel free to give it another name:

在此数据库中，使用以下命令创建示例表。 在这里，我们将将此表命名为sample_table但随时可以给它起另一个名字：

• CREATE TABLE sample_table (

  创建表sample_table (

• employee_id INT PRIMARY KEY,
  employee_id INT主键，
• first_name VARCHAR(50),
  first_name VARCHAR(50)，
• last_name VARCHAR(50),
  last_name VARCHAR(50)，
• start_date DATE,
  start_date DATE，
• salary VARCHAR(50)
  薪金VARCHAR(50)
• );
  );

Then populate this table with some sample employee data using the following command:

然后使用以下命令用一些样本员工数据填充该表：

• INSERT INTO sample_table (employee_id, first_name, last_name, start_date, salary)

  INSERT INTO sample_table (employee_id，first_name，last_name，start_date，salary)

• VALUES (1, 'Elizabeth', 'Cotten', '2007-11-11', '$105433.18'),
  值(1，'Elizabeth'，'Cotten'，'2007-11-11'，'$ 105433.18')，
• (2, 'Yanka', 'Dyagileva', '2017-10-30', '$107540.67'),
  (2，'Yanka'，'Dyagileva'，'2017-10-30'，'$ 107540.67')，
• (3, 'Lee', 'Dorsey', '2013-06-04', '$118024.04'),
  (3，'Lee'，'Dorsey'，'2013-06-04'，'$ 118024.04')，
• (4, 'Kasey', 'Chambers', '2010-08-18', '$116456.98'),
  (4，'Kasey'，'Chambers'，'2010-08-18'，'$ 116456.98')，
• (5, 'Bram', 'Tchaikovsky', '2018-09-16', '$61989.50');
  (5，'布拉姆'，'柴可夫斯基'，'2018-09-16'，'$ 61989.50');

Following this, you can close the MySQL prompt:

之后，您可以关闭MySQL提示符：

• exit
  出口

Now that you have a sample database loaded with dummy data, you can move on to the next step in which you will install pgLoader on your PostgreSQL server.

现在，您已经有一个加载了虚拟数据的示例数据库，您可以继续下一步，在其中将pgLoader安装在PostgreSQL服务器上。

第2步-安装pgLoader (Step 2 — Installing pgLoader)

pgLoader is a program that can load data into a PostgreSQL database from a variety of different sources. It uses PostgreSQL’s COPY command to copy data from a source database or file — such as a comma-separated values (CSV) file — into a target PostgreSQL database.

pgLoader是一个程序，可以从各种不同的来源将数据加载到PostgreSQL数据库中。 它使用PostgreSQLCOPY命令将数据从源数据库或文件(例如， 逗号分隔值(CSV)文件