Migration from MySQL 8 to Postgres Database

Troubleshooting
2

The mentioned error was because of apt version of pgloader not supporting the MySQL 8 default charset/collation utf8mb4_0900_ai_ci, It was found from this stackoverflow answer. By removing the apt version and installing pgloader by building from source fixed the issue.

sudo apt remove --purge pgloader
git clone https://github.com/dimitri/pgloader.git
cd pgloader/

sudo apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev
make save
./build/bin/pgloader --help


export PATH=$PATH:/home/$USER/tmp/pgloader/build/bin

pgloader migration.load > migration.log

But now the migration do start, but pgloader giving server errors Heap exhausted (no more space for allocation).

After referencing the pgloader documentation adjusted the WITH conditions given in the default migration.load file in this Documentation

WITH data only,
     workers = 4, concurrency = 1,
     multiple readers per thread, rows per range = 5000,
     create no tables, create no indexes,
     batch size = 50MB, prefetch rows = 500,
     preserve index names

And the pgloader command to.

pgloader --dynamic-space-size 1564 migration.load > migration.log

(For my 2 Core 4 GB server)

Now the errors gone, and the pgloader starts running, I have the original MySQL database sizes about 6 GB (MySQL data-directory size)…

But after running some time the pgloader CPU usage reaches very high and it stops with error…

Database error: Connection to database server lost.

It is happening after 2GB of data transferred to postgress data directory, I’ve tried tuning the values in the WITH, reducing --dynamic-space-size also even tried increasing the server capacity,
But getting the connection lost error when reaching the 1.8 GB - 2 GB mark.

Any Suggestions for fixing this issue.

1 Like