Migrating to Neon using PgLoader
Im trying to use
pgloader to migrate from aurora serverless mysql to Neon :neon: ?
Im basically running something like
I keep running into the endpointid is not specified error.
So then i add ?options=endpoint%3D[endpoint-id] but get the same error.
I verified that libpq => 16 which is what comes installed with homebrew when installing pgloader.
Ive seen the doc here https://neon.tech/docs/connect/connection-errors#the-endpoint-id-is-not-specified
but none of the fixes seems to work with pgloader22 Replies
helpful-purple•2y ago
Hey @axel-rex , is there any additional info if you run pgloader with the
--verbose flag? And/or --debug (i'm just scanning through their docsdependent-tanOP•2y ago
KABOOM!
SIMPLE-FILE-ERROR: Can't find the TRUENAME of wild pathname
also
2024-01-17T14:00:48.005908-05:00 LOG pgloader version "3.6.9"
2024-01-17T14:00:48.008198-05:00 FATAL Failed to parsehelpful-purple•2y ago
I see something very similar here - https://github.com/dimitri/pgloader/issues/760#issuecomment-1236870690
GitHub
Fatal error from latest docker with: pgloader SOURCE TARGET · Issue...
I'm trying to do a migration of a MySQL database to a Postgres database on the same server. I ran the basic usage of the command pgloader SOURCE TARGET using the docker image and am getting the...
dependent-tanOP•2y ago
erghh..okay i guess ill use aws dbms then 😦
helpful-purple•2y ago
there is a possible workaround in the issue to pass
sslmode=disable but I can't confirmdependent-tanOP•2y ago
okay thanks
helpful-purple•2y ago
if that doesn't work, I can check with the team to see if there are suggestions - so please let me know ...
dependent-tanOP•2y ago
okay thanks i think ill ditch pgloader in this case.. thanks though
genetic-orange•2y ago
@axel-rex have you tried passing the endpoint name in the password field (https://neon.tech/docs/connect/connection-errors#d-specify-the-endpoint-id-in-the-password-field) ?
Here is what my command_file looks like:
yanic@Yanics-MacBook-Pro bin % cat command_file
LOAD DATABASE
FROM pgsql://yanic:endpoint=ep-broken-bar-83086356;<PASSWORD_REMOVED>@ep-broken-bar-83086356.us-east-2.aws.neon.tech/neondb?sslmode=require
INTO pgsql://yanic:endpoint=ep-empty-union-92789302;<PASSWORD_REMOVED>@ep-empty-union-92789302.us-east-2.aws.neon.tech/neondb?sslmode=require;
In my case, it works like a charm:
yanic@Yanics-MacBook-Pro bin % ./pgloader command_file
2024-01-17T23:55:11.006590+01:00 LOG pgloader version "3.6.2079646"
2024-01-17T23:55:11.008048+01:00 LOG Parsing commands from file #P"/Users/yanic/pgloader/pgloader/build/bin/command_file"
2024-01-17T23:55:15.345848+01:00 LOG Migrating from #<PGSQL-CONNECTION pgsql://yanic@ep-billowing-bonus-15501238.us-east-2.aws.neon.tech:5432/neondb {700CA7EA43}>
2024-01-17T23:55:15.346052+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://yanic@ep-spring-fog-76487693.us-east-2.aws.neon.tech:5432/neondb {700CA7F5D3}>
[Output too large and truncated]
Total import time ✓ 0 6.402s
yanic@Yanics-MacBook-Pro bin % helpful-purple•2y ago
@axel-rex ^
inland-turquoise•2y ago
We've got a guide for this now:
https://neon.tech/docs/import/migrate-mysql
Neon
Migrate your MySQL database to Neon Postgres - Neon Docs
This topic describes how to migrate your MySQL database to Neon Postgres using pgloader. The pgloader utility transforms the data to a Postgres compatible format as it is read from your MySQL database...
like-gold•2y ago
Hi all - In a similar vein, I am trying to migrate from Planetscale to Neon, and I get the following error when following the above guide. Any help would be greatly appreciated.
Failed to connect to pgsql at "ep-odd-mud-a5dy61s7.us-east-2.aws.neon.tech" (port 5432) as user "<REMOVED>": SSL verify error: 20 X509_V_ERR_UNABLE_TO_GET_ISSUER_CERT_LOCALLY
I have Docker installed and I'm running the pgloader command from within the docker container ( on Windows ). Thats the only reason I can think of that I would get the error? However, I'm not sure how else to run pgloader if this isn't the correct option.inland-turquoise•2y ago
What's your sslmode setting in your Neon connection string?
like-gold•2y ago
sslmode=require
on both the mysql and the neon string
pgloader mysql://<login removed>@aws.connect.psdb.cloud/mordheim-tracker-db postgresql://<login removed>@ep-odd-mud-a5dy61s7.us-east-2.aws.neon.tech/mordheim-tracker-db?sslmode=require
I've tried with sslmode=require on both, neither, one of each...
wise-white•2y ago
I am in the same boat!
inland-turquoise•2y ago
Hey, found the answer here.
https://github.com/dimitri/pgloader/issues/768
I've validated that this works via Docker on Windows.
config.load file:
load database
from mysql://user:password@aws.connect.psdb.cloud/test1?sslmode=require
into postgresql://dtprice:endpoint=ep-gentle-bread-a12bc5d8;password@ep-gentle-bread-a12bc5d8.us-east-2.aws.neon.tech/neondb?sslmode=allow;
docker command:
docker run -v C:\Users\dprice\Downloads\config.load:/config.load d183dc1003daf5e703bd867b3b7826c117fa16b7ee2cd360af591dc895b121dc pgloader --no-ssl-cert-verification /config.load
GitHub
SSL verify error: 19 X509_V_ERR_SELF_SIGNED_CERT_IN_CHAIN · Issue #...
pgloader --version pgloader version "3.4.1" compiled with SBCL 1.4.5 did you test a fresh compile from the source tree? Compiling pgloader from sources is documented in the README, it'...
like-gold•2y ago
Thanks! I'll try it out. Not sure why searching didn't come up with this issue.. oh well. appreciate it.
Yup, can confirm that it worked. Had to use the no ssl cert option as well - so for anyone else looking at this, make sure you add that flag. 🙂
Were the docs updated after this convo, or was I just blind this whole time and it was on the 'Migrate from MySQL to Postgres' page?
inland-turquoise•2y ago
We updated it 🙂
like-gold•2y ago
Awesome! That was super fast.
wise-white•2y ago
pgloader --verbose config.load
2024-03-30T19:20:38.001642-04:00 NOTICE Starting pgloader, log system is ready.
2024-03-30T19:20:38.005335-04:00 LOG pgloader version "3.6.9"
2024-03-30T19:20:38.006444-04:00 LOG Data errors in '/private/tmp/pgloader/'
2024-03-30T19:20:38.006473-04:00 LOG Parsing commands from file #P"/Users/***/Desktop/config.load"
KABOOM!
DB-CONNECTION-ERROR: Failed to connect to pgsql at "<>.us-west-2.aws.neon.tech" (port 5432) as user "main_owner": Database error XX000: Console request failed
An unhandled error condition has been signalled:
Failed to connect to pgsql at "<>.us-west-2.aws.neon.tech" (port 5432) as user "main_owner": Database error XX000: Console request failed
What I am doing here?
Failed to connect to pgsql at "<>.us-west-2.aws.neon.tech" (port 5432) as user "main_owner": Database error XX000: Console request failed
---------
i am on a mac, tried on multiple macs. tried the sslmode=allow and no-ssl-cert-verification
i am using the default role which seems to be main_owner and my account is the one that made the DB
i also tried making a different role and that didn't work either
@Daniel I thought I was in the same boat as @Magless but seems to not be a Cert issue. It is saying DB error and not much more even with verbose
Appreciate any help 🙏🙏 Thank you!inland-turquoise•2y ago
Hey, did you modify your connection string as described here: https://neon.tech/docs/import/migrate-mysql#retrieve-your-neon-database-connection-string
Neon
Migrate your MySQL database to Neon Postgres - Neon Docs
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...
wise-white•2y ago
Hi, yes. I copied the connection string from the dashboard then added the endpoint
Ah got it working, totally my bad. I was thinking the endpoint id was the ID I saw at the top of my dashboard when looking at a branch