CODEWITHSUNDEEP
mysqldatabasepostgresql
helsont
helsont

Reputation: 4952

Convert MySQL Database to Postgres Database using pgloader

I have a MySQL database that I'd like to convert into a Postgres Database. I found that pgloader.io does this. How can I accomplish this task?

Upvotes: 1

Views: 1721

Answers (1)

helsont
helsont

Reputation: 4952

  1. Create a file which defines (in the pgloader schema) how the data transfer will function. Here is my sample:
LOAD DATABASE
     FROM      mysql://root:password@localhost/development
     INTO postgresql://helson@localhost/development

 WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1,
      multiple readers per thread, rows per range = 50000,
      prefetch rows = 10000

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '128MB',
      work_mem to '12MB',
      search_path to 'sakila, public, "$user"'

  SET MySQL PARAMETERS
      net_read_timeout  = '120',
      net_write_timeout = '120'
;

There are other options to limit the tables, such as 

including only table names matching 'users'

which you can include before the semicolon.

  1. Go ahead and run execute the command
pgloader schema

where schema is the path of the file with the pgloader schema.

  1. You may get some errors. I personally experienced an issue where there wasn't enough heap memory to perform the task. To fix this, you'll need to build the project from source.

It's simple enough. Clone the repository and make with the following command

make DYNSIZE=20000 pgloader

This will create a new executable with a much larger heap size. You can execute the binary with 

./build/bin/pgloader ~/schema

or wherever your schema is located.

  1. Rerun with the increased heap size.
  2. You may experience issues with rows failing in the transfer. For me, this was due to strict mode issues in MySQL. I had to manually fix these issues (setting new defaults for columns, setting datetime values to NULL instead of 0000-00-00 00:00:00). Fix these issues and rerun pgloader, but this time only include the specific tables you need.

And that should be it!

Upvotes: 1

Related Questions