Skip to content

Instantly share code, notes, and snippets.

@back-2-95
Created March 16, 2024 09:43
Show Gist options
  • Save back-2-95/55cb48adb989e61c76b675be0e5563a9 to your computer and use it in GitHub Desktop.
Save back-2-95/55cb48adb989e61c76b675be0e5563a9 to your computer and use it in GitHub Desktop.
Pgloader load file for migrating from PlanetScale MySQL to Neon PostgreSQL
LOAD DATABASE
FROM mysql://MY_PLANETSCALE_USERNAME:MY_PLANETSCALE_PASSWORD@aws.connect.psdb.cloud/MY_DATABASE?sslmode=require
INTO postgres://MY_NEON_USERNAME:endpoint=ep-MY_ENDPOINT_ID;MY_NEON_PASSWORD@ep-MY_ENDPOINT_ID.eu-central-1.aws.neon.tech/MY_DATABASE?sslmode=require
WITH include drop, create tables, quote identifiers
ALTER schema 'MY_DATABASE' rename to 'public'
;
@back-2-95
Copy link
Author

back-2-95 commented Mar 16, 2024

Tip

You get those connection strings from PlanetScale and Neon services.

Note

BUT for the Neon you need to define that endpoint for the pgloader

See https://neon.tech/docs/import/migrate-mysql

I struggled with:

  • Using Pgloader on my M1 Mac - I needed to create Ubuntu virtual machine and install Pgloader from source
  • Setting endpoint in the INTO line - Found the info from that URL ^^
  • Getting table names as they were in the source (e.g. TableName instead tablename) - sorted with that WITH line

@back-2-95
Copy link
Author

back-2-95 commented Mar 16, 2024

What I did on Ubuntu virtual machine (roughly):

apt remove pgloader -y
git clone https://github.com/dimitri/pgloader.git
apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev
cd pgloader
make pgloader
./build/bin/pgloader --help
pgloader version "3.6.2079646"
compiled with SBCL 2.2.9.debian

@back-2-95
Copy link
Author

Running the migration:

/usr/local/bin/pgloader migrate.load

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment