Postgresql CREATE PARTITION from MySQL?

[jamesbriggs]

Hi Dimitri.

I'm migrating MySQL 5.6 partitions (CREATE PARTITION) to Postgresql 10.1, and pgloader is not automatically creating the partitions in PG using a recent version of pgloader. Should it work?

Thanks, James.

[dimitri]

Hi James,

It should not work as in “there's no code anywhere in pgloader that knows about partitions in either MySQL or PostgreSQL”. It should work as in “let's implement that missing code”. So I'm adding the label “Wish List” to that issue, and I ask people who need that feature to consider (crowd?) funding it, by having their company buying a pgloader Moral License (see https://pgloader.io/moral-licence/).

You're using pgloader in order to complete your job, paid by a company, right? Either have them pay you to contribute the feature (yes please) or buy a licence to the same effect, so that I can spend my time on your needs ;-)

[dimitri]

I have updated https://pgloader.io/roadmap/ to add Native Partitioning Support in PostgreSQL 10, and also reviewed the contributing options so that it's now a monthly based subscription that you can cancel anytime you want, aiming to make it easier for interested patrons. Quarterly, Bi-Yearly and Yearly pre-paid subscriptions are an option too. Also I'm offering more Custom services for Patrons, consider reading https://pgloader.io/moral-licence/ to know more.

[jcetchri]

Hi Dimitri
First of all thank for PgLoder it's very helpfull for migration.
I'm using it to migrate from MySql 5.5 to PgSql 10.4
I have a partitionned mysql table with 1 300 000 000 rows .
PgLoader has migrate it as a simple table
Before loading I have create the partitionned table at PgSql site but i got an error.
With debugger i've found the error .
The error is in this control query

select n.nspname, c.relname, nf.nspname, cf.relname as frelname,
        r.oid, conname,
        pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
        (select string_agg(attname, ',')
           from pg_attribute
          where attrelid = r.conrelid
            and array[attnum::integer] <@ conkey::integer[]
        ) as conkey,
        (select string_agg(attname, ',')
           from pg_attribute
          where attrelid = r.confrelid
            and array[attnum::integer] <@ confkey::integer[]
        ) as confkey,
        confupdtype, confdeltype, confmatchtype,
        condeferrable, condeferred
   from pg_catalog.pg_constraint r
        JOIN pg_class c on r.conrelid = c.oid
        JOIN pg_namespace n on c.relnamespace = n.oid
        JOIN pg_class cf on r.confrelid = cf.oid
        JOIN pg_namespace nf on cf.relnamespace = nf.oid
   where r.contype = 'f'
         AND c.relkind = 'r' and cf.relkind = 'r'
         AND n.nspname !~ '^pg_' and n.nspname <> 'information_schema'
         AND nf.nspname !~ '^pg_' and nf.nspname <> 'information_schema'
         and ((n.nspname = 'SchemaName' and c.relname ~ '^TablePartitionned1st$')
           or (n.nspname = 'SchemaName' and c.relname ~ '^TablePartitionned2nd$'))
         and ((nf.nspname = 'SchemaName' and cf.relname ~ '^TablePartitionned1st$')
           or (nf.nspname = 'SchemaName' and cf.relname ~ '^TablePartitionned2nd$'))
;

AND c.relkind = 'r' and cf.relkind = 'r' <=== relkind for partitionned is 'p'

Best regard
JC

[dimitri]

Hi @jcetchri ; thanks for debugging this first step. Even if we'd fix this query, we would have more code to write in order to fully support targeting a partitioned table. That said, if that's all we need to be able to target a pre-created partitioned table that would be good.

What happens when you try to edit the query and recompile pgloader from sources? does it then load the data as you would expect?