Migration from MariaDB to PostgreSQL

[Aleksa1996]

Hello i am trying to migrate data from MariaDB to PostgreSQL

• [ ] MariaDB--version

    10.6.11-MariaDB

• [ ] PostgreSQL --version

     PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

• [ ] pgloader --version

    pgloader version "3.6.7~devel"
    compiled with SBCL 2.1.11.debian

  • [ ] did you test a fresh compile from the source tree?

  I have succesfully compiled with the following command: make DYNSIZE=4096 pgloader

  • [ ] did you search for other similar issues?

  Yes but i couldn't find anything related.

LOAD DATABASE
    FROM mysql://db:pw@ip/database
    INTO postgresql://db:pw@127.0.0.1/database

    WITH include no drop, truncate, data only, 
    prefetch rows = 50000, workers = 8, concurrency = 1
;

• [ ] pgloader output you obtain

This is the output i get when i run with --debug

2023-09-25T15:39:10.738777Z DEBUG  stop schema.table   | 1009706779 .. 1010134777 = 0.427998d0
2023-09-25T15:39:10.738777Z DEBUG Writer[1] for schema.table is done in 0.000000s
2023-09-25T15:39:10.738777Z DEBUG Finished processing WRITER for "schema.table"  0.000000s
2023-09-25T15:39:10.738777Z DEBUG writers-counts[schema.table] = 0
2023-09-25T15:39:10.742777Z DEBUG  stop schema.table   | 1009686779 .. 1010138777 = 0.451998d0
2023-09-25T15:39:10.742777Z DEBUG Writer[3] for schema.table is done in 0.004000s
2023-09-25T15:39:10.742777Z DEBUG Finished processing WRITER for "schema.table"  0.004000s
2023-09-25T15:39:10.742777Z DEBUG writers-counts[schema.table] = 0
2023-09-25T15:39:10.746777Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://user@127.0.0.1:5432/database {105F3ADC83}>
2023-09-25T15:39:10.746777Z DEBUG SET client_encoding TO 'utf8'
2023-09-25T15:39:10.746777Z DEBUG SET application_name TO 'pgloader'
2023-09-25T15:39:10.746777Z SQL SET search_path TO schema;
2023-09-25T15:39:10.750777Z INFO pgsql:copy-rows-from-queue[2]: schema.table (id
                                                                                       schematic_collection_map_id
                                                                                       position
                                                                                       schematic_collection_map_object_source_type
                                                                                       schematic_collection_map_object_source_identifier)
2023-09-25T15:39:10.754777Z DEBUG  stop schema.table   | 1009710779 .. 1010146777 = 0.435998d0
2023-09-25T15:39:10.754777Z DEBUG Writer[2] for schema.table is done in 0.000000s
2023-09-25T15:39:10.754777Z DEBUG Finished processing WRITER for "schema.table"  0.000000s
2023-09-25T15:39:10.754777Z DEBUG writers-counts[schema.table] = 0
2023-09-25T15:39:10.754777Z INFO Done with COPYing data, waiting for indexes
2023-09-25T15:39:10.758777Z NOTICE Completing PostgreSQL database.
2023-09-25T15:39:10.758777Z NOTICE Reset sequences
2023-09-25T15:39:10.770777Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://user@127.0.0.1:5432/database {102250F923}>
2023-09-25T15:39:10.770777Z DEBUG SET client_encoding TO 'utf8'
2023-09-25T15:39:10.770777Z DEBUG SET application_name TO 'pgloader'
2023-09-25T15:39:10.770777Z DEBUG SET client_encoding TO 'utf8'
2023-09-25T15:39:10.770777Z DEBUG SET application_name TO 'pgloader'
KABOOM!
2023-09-25T15:39:10.890778Z ERROR Database error 22004: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function inline_code_block line 25 at EXECUTE

Another try:

KABOOM!
DATA-EXCEPTION: Database error 22004: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function inline_code_block line 25 at EXECUTE
QUERY:
DO $$
DECLARE
  n integer := 0;
  r record;
BEGIN
  FOR r in
       SELECT 'select '
               || trim(trailing ')'
                  from replace(pg_get_expr(d.adbin, d.adrelid),
                               'nextval', 'setval'))
               || ', (select greatest(max(' || quote_ident(a.attname) || '), (select seqmin from pg_sequence where seqrelid = ('''
               || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only '
               || quote_ident(nspname) || '.' || quote_ident(relname) || '));' as sql
         FROM pg_class c
              JOIN pg_namespace n on n.oid = c.relnamespace
              JOIN pg_attribute a on a.attrelid = c.oid
              JOIN pg_attrdef d on d.adrelid = a.attrelid
                                 and d.adnum = a.attnum
                                 and a.atthasdef
        WHERE relkind = 'r' and a.attnum > 0
              and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval'
              and c.oid in (select oid from reloids)
  LOOP
    n := n + 1;
    EXECUTE r.sql;
  END LOOP;

  PERFORM pg_notify('seqs', n::text);
END;
$$;
An unhandled error condition has been signalled:
   Database error 22004: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function inline_code_block line 25 at EXECUTE
QUERY:
DO $$
DECLARE
  n integer := 0;
  r record;
BEGIN
  FOR r in
       SELECT 'select '
               || trim(trailing ')'
                  from replace(pg_get_expr(d.adbin, d.adrelid),
                               'nextval', 'setval'))
               || ', (select greatest(max(' || quote_ident(a.attname) || '), (select seqmin from pg_sequence where seqrelid = ('''
               || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only '
               || quote_ident(nspname) || '.' || quote_ident(relname) || '));' as sql
         FROM pg_class c
              JOIN pg_namespace n on n.oid = c.relnamespace
              JOIN pg_attribute a on a.attrelid = c.oid
              JOIN pg_attrdef d on d.adrelid = a.attrelid
                                 and d.adnum = a.attnum
                                 and a.atthasdef
        WHERE relkind = 'r' and a.attnum > 0
              and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval'
              and c.oid in (select oid from reloids)
  LOOP
    n := n + 1;
    EXECUTE r.sql;
  END LOOP;

  PERFORM pg_notify('seqs', n::text);
END;
$$;

2023-09-25T19:39:07.475664Z ERROR Database error 22004: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function inline_code_block line 25 at EXECUTE
QUERY:
DO $$
DECLARE
  n integer := 0;
  r record;
BEGIN
  FOR r in
       SELECT 'select '
               || trim(trailing ')'
                  from replace(pg_get_expr(d.adbin, d.adrelid),
                               'nextval', 'setval'))
               || ', (select greatest(max(' || quote_ident(a.attname) || '), (select seqmin from pg_sequence where seqrelid = ('''
               || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only '
               || quote_ident(nspname) || '.' || quote_ident(relname) || '));' as sql
         FROM pg_class c
              JOIN pg_namespace n on n.oid = c.relnamespace
              JOIN pg_attribute a on a.attrelid = c.oid
              JOIN pg_attrdef d on d.adrelid = a.attrelid
                                 and d.adnum = a.attnum
                                 and a.atthasdef
        WHERE relkind = 'r' and a.attnum > 0
              and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval'
              and c.oid in (select oid from reloids)
  LOOP
    n := n + 1;
    EXECUTE r.sql;
  END LOOP;

  PERFORM pg_notify('seqs', n::text);
END;
$$;

What I am doing here?

Database error 22004: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function inline_code_block line 25 at EXECUTE
QUERY:
DO $$
DECLARE
  n integer := 0;
  r record;
BEGIN
  FOR r in
       SELECT 'select '
               || trim(trailing ')'
                  from replace(pg_get_expr(d.adbin, d.adrelid),
                               'nextval', 'setval'))
               || ', (select greatest(max(' || quote_ident(a.attname) || '), (select seqmin from pg_sequence where seqrelid = ('''
               || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only '
               || quote_ident(nspname) || '.' || quote_ident(relname) || '));' as sql
         FROM pg_class c
              JOIN pg_namespace n on n.oid = c.relnamespace
              JOIN pg_attribute a on a.attrelid = c.oid
              JOIN pg_attrdef d on d.adrelid = a.attrelid
                                 and d.adnum = a.attnum
                                 and a.atthasdef
        WHERE relkind = 'r' and a.attnum > 0
              and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval'
              and c.oid in (select oid from reloids)
  LOOP
    n := n + 1;
    EXECUTE r.sql;
  END LOOP;

  PERFORM pg_notify('seqs', n::text);
END;
$$;

[Aleksa1996]

My mistake, i have created some sequences before and they collided with import from MariaDB