Migrate from MySQL to Neon Postgres
This topic describes how to migrate your MySQL database to Neon Postgres using pgloader.
The pgloader utility transforms data to a Postgres-compatible format as it is read from your MySQL database. It uses the COPY Postgres protocol to stream the data into your Postgres database.
Prerequisites
Before you begin, make sure that you have the following:
- A Neon account and a project. See Sign up.
- A properly named database. For example, if you are migrating a database named
sakila, you might want to create a database of the same name in Neon. See Create a database for instructions. - Neon's Free Plan supports 500 MiB of data. If your data size is more than 500 MiB, you'll need to upgrade to one of Neon's paid plans. See Neon plans for more information.
Also, a close review of the Pgloader MySQL to Postgres Guide guide is recommended before you start. This guide will provide you with a good understanding of pgloader capabilities and how to configure your pgloader configuration file, if necessary.
Retrieve Your MySQL database credentials
Before starting the migration process, collect your MySQL database credentials:
- Log into your MySQL database provider.
- Identify and record the following details or grab your MySQL database connection string.
- Hostname or IP address
- Database name
- Username
- Password
Keep your MySQL database connection details handy for later use.
Retrieve your Neon database connection string
Log in to the Neon Console and navigate to the Connection Details section on the Dashboard to find your Postgres database connection string. It should look similar to this:
postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=requireNow, modify the connection string as follows to pass your endpoint ID (
ep-cool-darkness-123456in this example) to Neon with your password using theendpointkeyword, as shown here:postgresql://alex:endpoint=ep-cool-darkness-123456;AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=requirenote
Passing the
endpoint IDwith your password is a required workaround for some Postgres drivers, including the one used bypgloader. For more information about this workaround and why it's required, refer to our connection workaround documentation.Keep your Neon connection string handy for later use.
Install pgloader
Here's how you can set up
pgloaderfor your database migration:-
Install the
pgloaderutility using your preferred installation method. Debian (apt), RPM package, and Docker methods are supported, as well as Homebrew for macOS (brew install pgloader). If your macOS has an ARM processor, use the Homebrew installation method.See Installing pgloader for Debian (apt), RPM package, and Docker installation instructions.
-
Create a
pgloaderconfiguration file (e.g.,config.load). Use your MySQL database credentials to define the connection string for your database source. Use the Neon database connection string you retrieved and modified in the previous step as the destination.note
If you need to specify an SSL mode in your connection string, the following format is recommended:
sslmode=require. Other formats may not work.Example configuration in
config.load:load database from mysql://user:password@host/source_db?sslmode=require into postgresql://alex:endpoint=ep-cool-darkness-123456;AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require;
-
Run the migration with pgloader
To initiate the migration process, run:
pgloader config.loadThe command output will look similar to this:
LOG report summary reset table name errors rows bytes total time ----------------------- --------- --------- --------- -------------- fetch meta data 0 2 0.727s Create Schemas 0 0 0.346s Create SQL Types 0 0 0.178s Create tables 0 2 0.551s Set Table OIDs 0 1 0.094s ----------------------- --------- --------- --------- -------------- "db-test".dbname 0 1 0.0 kB 0.900s ----------------------- --------- --------- --------- -------------- COPY Threads Completion 0 4 0.905s Index Build Completion 0 1 0.960s Create Indexes 0 1 0.257s Reset Sequences 0 0 1.083s Primary Keys 0 1 0.263s Create Foreign Keys 0 0 0.000s Create Triggers 0 0 0.169s Set Search Path 0 1 0.427s Install Comments 0 0 0.000s ----------------------- --------- --------- --------- -------------- Total import time ✓ 1 0.0 kB 4.064s
SSL verify error
If you encounter an SSL verify error: 20 X509_V_ERR_UNABLE_TO_GET_ISSUER_CERT_LOCALLY error while attempting the instructions described above using pgloader from a Docker container, try the solution identified in this GitHub issue, which involves specifying sslmode=allow in the Postgres connection string and using the --no-ssl-cert-verification option with pgloader.
The following configuration file and Docker command were verified to work with Docker on Windows but may apply generally when using pgloader in a Docker container. In your pgloader config file, replace the MySQL and Postgres connection string values with your own. In the Docker command, specify the path to your pgloader config file, and replace the container ID value (the long alphanumeric string) with your own.
pgloader config.load file:
load database
from mysql://user:password@host/source_db?sslmode=require
into postgresql://alex:endpoint=ep-cool-darkness-123456;AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/neondb?sslmode=allow;Docker command:
docker run -v C:\path\to\config.load:/config.load d183dc100d3af5e703bd867b3b7826c117fa16b7ee2cd360af591dc895b121dc pgloader --no-ssl-cert-verification /config.load