I am currently evaluating if it makes sense to migrate my existing, self-hosted MariaDB database to a managed PostgreSQL database in the cloud. For this task I am using the tool pgloader
version 3.6.9
:
pgloader mysql://user:pw@localhost/mydb postgres://user:pw@external-server/mydb?sslmode=require
This works fine and I was able to copy all the data over to the new database. Now I created a new database in postgres via:
create database newdb
grant all privileges on database newdb to admin;
But migrating to this new database with the exact same command from above (only different db name):
pgloader mysql://user:pw@localhost/mydb postgres://user:pw@external-server/newdb?sslmode=require
is not working! I get the following error (IP address redacted):
Database error: end of file on #<CL+SSL::SSL-STREAM for #<FD-STREAM for "socket 1.2.3.4, peer: 1.2.3.4">>
I can only assume that there is a difference between the newly created database "newdb" and the old "mydb" which was created initially by the host provider.
Here's the output of \l
on my postgres server:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------------+----------+------------+------------+-----------------------------------
newdb | admin | UTF8 | en_US.UTF8 | en_US.UTF8 | =Tc/admin +
| | | | | admin=CTc/admin+
| | | | | postgres=CTc/postgres
...
mydb | admin | UTF8 | en_US.UTF8 | en_US.UTF8 |
Only difference in my opinion here are the access privileges. Could this be the culprit? If so - what changes do I have to apply in order to be able to migrate to the newdb
database?
Thanks for answers!