Error while parsing load file in Pgloader

Question

I'm trying to migrate MySql db to Postgres by Pgloader using docker-container.

I've create separate config file from_mysql_to_postgre.load:

LOAD DATABASE
  FROM      mysql://xxx
  INTO postgresql://yyy

WITH data only,
  workers = 8, concurrency = 1,
  multiple readers per thread, rows per range = 50000

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

CAST type bigint when (= precision 20) to bigserial drop typemod,
  type date drop not null drop default using zero-dates-to-null,
  type tinyint to boolean using tinyint-to-boolean,
  type year to integer;

BEFORE LOAD DO
  $$ SET session_replication_role = 'replica'; $$;

AFTER LOAD DO
  $$ SET session_replication_role = 'origin'; $$;

And starting migration by command:

docker run --rm -v C:\zzz\DbMigration:/data -it dimitri/pgloader:latest pgloader /data/from_mysql_to_postgre.load

And I get an error:

2023-07-07T12:02:13.013000Z LOG pgloader version "3.6.7~devel"
2023-07-07T12:02:13.016000Z LOG Parsing commands from file #P"/data/from_mysql_to_postgre.load"
KABOOM!
ESRAP-PARSE-ERROR: At end of input

  AFTER LOAD DO
    $$ SET session_replication_role = 'origin'; $$;
                                                   ^ (Line 23, Column 49, Position 819)

In context COMMAND:

While parsing COMMAND. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the character & (AMPERSAND)
  or the string "--"
  or the string "/*"
  or the character ; (SEMICOLON)
  or the string "after"
  or the string "alter"
  or the string "before"
  or the string "cast"
  or the string "decoding"
  or the string "distribute"
  or the string "excluding"
  or the string "including"
  or the string "materialize"
  or the string "set"
  or the string "with"
  or <end of input>
  or anything but the character & (AMPERSAND)
An unhandled error condition has been signalled: At end of input

What am I doing wrong?

Answer 1

It is not explained very clearly in the manual, but each LOAD command in the file is terminated with a single semicolon. In your case you have several lines ending in semicolons but there should only be one.

I move the semicolon to a final line on it's own to make this clearer, so your file should look like:

LOAD DATABASE
  FROM      mysql://xxx
  INTO postgresql://yyy

WITH data only,
  workers = 8, concurrency = 1,
  multiple readers per thread, rows per range = 50000

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

CAST type bigint when (= precision 20) to bigserial drop typemod,
  type date drop not null drop default using zero-dates-to-null,
  type tinyint to boolean using tinyint-to-boolean,
  type year to integer

BEFORE LOAD DO
  $$ SET session_replication_role = 'replica'; $$

AFTER LOAD DO
  $$ SET session_replication_role = 'origin'; $$

;

Note that the SQL statements inside the $$ ... $$ do still need semicolons terminating them, but that's SQL syntax, and not pgloader syntax.