Migration from MySQL to Postgresql: pgload fails with ERROR Database error 42703: column "adsrc" does not exist when creating schema

[marc-leinweber]

Problem and impact

Hey all,

when using the pgloader script as listed here, the pgloader fails with errror

2023-02-05T11:17:56.714000Z ERROR Database error 42703: column "adsrc" does not exist

QUERY: -- params: table-type-name
--         including
--         filter-list-to-where-clause for including
--         excluding
--         filter-list-to-where-clause for excluding

with seqattr as
 (
   select adrelid, 
          adnum,
          adsrc,
          case when adsrc ~ 'nextval'
               then substring(pg_get_expr(d.adbin, d.adrelid)
                              from '''([^'']+)'''
                    )
               else null
           end as seqname
     from pg_attrdef d
 )
    select nspname, relname, c.oid, attname,
           t.oid::regtype as type,
           case when atttypmod > 0
                then substring(format_type(t.oid, atttypmod) from '\d+(?:,\d+)?')
                else null
            end as typmod,
           attnotnull,
           case when atthasdef then def.adsrc end as default,
           case when s.seqname is not null then 'auto_increment' end as extra
      from pg_class c
           join pg_namespace n on n.oid = c.relnamespace
           left join pg_attribute a on c.oid = a.attrelid
           join pg_type t on t.oid = a.atttypid and attnum > 0
           left join pg_attrdef def on a.attrelid = def.adrelid
                                   and a.attnum = def.adnum
                                   and a.atthasdef
           left join seqattr s on def.adrelid = s.adrelid
                              and def.adnum = s.adnum
     where nspname !~ '^pg_' and n.nspname <> 'information_schema'
           and relkind in ('r', 'f', 'p')
           and ((n.nspname = 'public' and c.relname = 'auth_group')
           or (n.nspname = 'public' and c.relname = 'auth_group_permissions')
           or (n.nspname = 'public' and c.relname = 'auth_permission')
           or (n.nspname = 'public' and c.relname = 'badges_badgeitem')
           or (n.nspname = 'public' and c.relname = 'badges_badgelayout')
           or (n.nspname = 'public' and c.relname = 'banktransfer_bankimportjob')
           or (n.nspname = 'public' and c.relname = 'banktransfer_banktransaction')
           or (n.nspname = 'public' and c.relname = 'banktransfer_refundexport')
           or (n.nspname = 'public' and c.relname = 'django_content_type')
           or (n.nspname = 'public' and c.relname = 'django_migrations')
           or (n.nspname = 'public' and c.relname = 'django_session')
           or (n.nspname = 'public' and c.relname = 'otp_static_staticdevice')
           or (n.nspname = 'public' and c.relname = 'otp_static_statictoken')
           or (n.nspname = 'public' and c.relname = 'otp_totp_totpdevice')
           or (n.nspname = 'public' and c.relname = 'paypal_referencedpaypalobject')
           or (n.nspname = 'public' and c.relname = 'pretixapi_apicall')
           or (n.nspname = 'public' and c.relname = 'pretixapi_oauthaccesstoken')
           or (n.nspname = 'public' and c.relname = 'pretixapi_oauthaccesstoken_organizers')
           or (n.nspname = 'public' and c.relname = 'pretixapi_oauthapplication')
           or (n.nspname = 'public' and c.relname = 'pretixapi_oauthgrant')
           or (n.nspname = 'public' and c.relname = 'pretixapi_oauthgrant_organizers')
           or (n.nspname = 'public' and c.relname = 'pretixapi_oauthidtoken')
           or (n.nspname = 'public' and c.relname = 'pretixapi_oauthidtoken_organizers')
           or (n.nspname = 'public' and c.relname = 'pretixapi_oauthrefreshtoken')
           or (n.nspname = 'public' and c.relname = 'pretixapi_webhook')
           or (n.nspname = 'public' and c.relname = 'pretixapi_webhookcall')
           or (n.nspname = 'public' and c.relname = 'pretixapi_webhookcallretry')
           or (n.nspname = 'public' and c.relname = 'pretixapi_webhookeventlistener')
           or (n.nspname = 'public' and c.relname = 'pretixapi_webhook_limit_events')
           or (n.nspname = 'public' and c.relname = 'pretixbase_attendeeprofile')
           or (n.nspname = 'public' and c.relname = 'pretixbase_cachedcombinedticket')
           or (n.nspname = 'public' and c.relname = 'pretixbase_cachedfile')
           or (n.nspname = 'public' and c.relname = 'pretixbase_cachedticket')
           or (n.nspname = 'public' and c.relname = 'pretixbase_cancellationrequest')
           or (n.nspname = 'public' and c.relname = 'pretixbase_cartposition')
           or (n.nspname = 'public' and c.relname = 'pretixbase_checkin')
           or (n.nspname = 'public' and c.relname = 'pretixbase_checkinlist')
           or (n.nspname = 'public' and c.relname = 'pretixbase_checkinlist_gates')
           or (n.nspname = 'public' and c.relname = 'pretixbase_checkinlist_limit_products')
           or (n.nspname = 'public' and c.relname = 'pretixbase_customer')
           or (n.nspname = 'public' and c.relname = 'pretixbase_customerssoaccesstoken')
           or (n.nspname = 'public' and c.relname = 'pretixbase_customerssoclient')
           or (n.nspname = 'public' and c.relname = 'pretixbase_customerssogrant')
           or (n.nspname = 'public' and c.relname = 'pretixbase_customerssoprovider')
           or (n.nspname = 'public' and c.relname = 'pretixbase_device')
           or (n.nspname = 'public' and c.relname = 'pretixbase_device_limit_events')
           or (n.nspname = 'public' and c.relname = 'pretixbase_discount')
           or (n.nspname = 'public' and c.relname = 'pretixbase_discount_condition_limit_products')
           or (n.nspname = 'public' and c.relname = 'pretixbase_event')
           or (n.nspname = 'public' and c.relname = 'pretixbase_eventfooterlink')
           or (n.nspname = 'public' and c.relname = 'pretixbase_eventlock')
           or (n.nspname = 'public' and c.relname = 'pretixbase_eventmetaproperty')
           or (n.nspname = 'public' and c.relname = 'pretixbase_eventmetavalue')
           or (n.nspname = 'public' and c.relname = 'pretixbase_event_settingsstore')
           or (n.nspname = 'public' and c.relname = 'pretixbase_gate')
           or (n.nspname = 'public' and c.relname = 'pretixbase_giftcard')
           or (n.nspname = 'public' and c.relname = 'pretixbase_giftcardacceptance')
           or (n.nspname = 'public' and c.relname = 'pretixbase_giftcardtransaction')
           or (n.nspname = 'public' and c.relname = 'pretixbase_globalsettingsobject_settingsstore')
           or (n.nspname = 'public' and c.relname = 'pretixbase_invoice')
           or (n.nspname = 'public' and c.relname = 'pretixbase_invoiceaddress')
           or (n.nspname = 'public' and c.relname = 'pretixbase_invoiceline')
           or (n.nspname = 'public' and c.relname = 'pretixbase_item')
           or (n.nspname = 'public' and c.relname = 'pretixbase_itemaddon')
           or (n.nspname = 'public' and c.relname = 'pretixbase_itembundle')
           or (n.nspname = 'public' and c.relname = 'pretixbase_itemcategory')
           or (n.nspname = 'public' and c.relname = 'pretixbase_itemmetaproperty')
           or (n.nspname = 'public' and c.relname = 'pretixbase_itemmetavalue')
           or (n.nspname = 'public' and c.relname = 'pretixbase_itemvariation')
           or (n.nspname = 'public' and c.relname = 'pretixbase_itemvariationmetavalue')
           or (n.nspname = 'public' and c.relname = 'pretixbase_itemvariation_require_membership_types')
           or (n.nspname = 'public' and c.relname = 'pretixbase_item_require_membership_types')
           or (n.nspname = 'public' and c.relname = 'pretixbase_logentry')
           or (n.nspname = 'public' and c.relname = 'pretixbase_membership')
           or (n.nspname = 'public' and c.relname = 'pretixbase_membershiptype')
           or (n.nspname = 'public' and c.relname = 'pretixbase_notificationsetting')
           or (n.nspname = 'public' and c.relname = 'pretixbase_order')
           or (n.nspname = 'public' and c.relname = 'pretixbase_orderfee')
           or (n.nspname = 'public' and c.relname = 'pretixbase_orderpayment')
           or (n.nspname = 'public' and c.relname = 'pretixbase_orderposition')
           or (n.nspname = 'public' and c.relname = 'pretixbase_orderrefund')
           or (n.nspname = 'public' and c.relname = 'pretixbase_organizer')
           or (n.nspname = 'public' and c.relname = 'pretixbase_organizerfooterlink')
           or (n.nspname = 'public' and c.relname = 'pretixbase_organizer_settingsstore')
           or (n.nspname = 'public' and c.relname = 'pretixbase_question')
           or (n.nspname = 'public' and c.relname = 'pretixbase_questionanswer')
           or (n.nspname = 'public' and c.relname = 'pretixbase_questionanswer_options')
           or (n.nspname = 'public' and c.relname = 'pretixbase_questionoption')
           or (n.nspname = 'public' and c.relname = 'pretixbase_question_items')
           or (n.nspname = 'public' and c.relname = 'pretixbase_quota')
           or (n.nspname = 'public' and c.relname = 'pretixbase_quota_items')
           or (n.nspname = 'public' and c.relname = 'pretixbase_quota_variations')
           or (n.nspname = 'public' and c.relname = 'pretixbase_revokedticketsecret')
           or (n.nspname = 'public' and c.relname = 'pretixbase_scheduledeventexport')
           or (n.nspname = 'public' and c.relname = 'pretixbase_scheduledorganizerexport')
           or (n.nspname = 'public' and c.relname = 'pretixbase_seat')
           or (n.nspname = 'public' and c.relname = 'pretixbase_seatcategorymapping')
           or (n.nspname = 'public' and c.relname = 'pretixbase_seatingplan')
           or (n.nspname = 'public' and c.relname = 'pretixbase_staffsession')
           or (n.nspname = 'public' and c.relname = 'pretixbase_staffsessionauditlog')
           or (n.nspname = 'public' and c.relname = 'pretixbase_subevent')
           or (n.nspname = 'public' and c.relname = 'pretixbase_subeventitem')
           or (n.nspname = 'public' and c.relname = 'pretixbase_subeventitemvariation')
           or (n.nspname = 'public' and c.relname = 'pretixbase_subeventmetavalue')
           or (n.nspname = 'public' and c.relname = 'pretixbase_taxrule')
           or (n.nspname = 'public' and c.relname = 'pretixbase_team')
           or (n.nspname = 'public' and c.relname = 'pretixbase_teamapitoken')
           or (n.nspname = 'public' and c.relname = 'pretixbase_teaminvite')
           or (n.nspname = 'public' and c.relname = 'pretixbase_team_limit_events')
           or (n.nspname = 'public' and c.relname = 'pretixbase_team_members')
           or (n.nspname = 'public' and c.relname = 'pretixbase_transaction')
           or (n.nspname = 'public' and c.relname = 'pretixbase_u2fdevice')
           or (n.nspname = 'public' and c.relname = 'pretixbase_user')
           or (n.nspname = 'public' and c.relname = 'pretixbase_user_groups')
           or (n.nspname = 'public' and c.relname = 'pretixbase_user_user_permissions')
           or (n.nspname = 'public' and c.relname = 'pretixbase_voucher')
           or (n.nspname = 'public' and c.relname = 'pretixbase_waitinglistentry')
           or (n.nspname = 'public' and c.relname = 'pretixbase_webauthndevice')
           or (n.nspname = 'public' and c.relname = 'pretixhelpers_thumbnail')
           or (n.nspname = 'public' and c.relname = 'pretixmultidomain_knowndomain')
           or (n.nspname = 'public' and c.relname = 'sendmail_rule')
           or (n.nspname = 'public' and c.relname = 'sendmail_rule_limit_products')
           or (n.nspname = 'public' and c.relname = 'sendmail_scheduledmail')
           or (n.nspname = 'public' and c.relname = 'stripe_referencedstripeobject')
           or (n.nspname = 'public' and c.relname = 'stripe_registeredapplepaydomain')
           or (n.nspname = 'public' and c.relname = 'ticketoutputpdf_ticketlayout')
           or (n.nspname = 'public' and c.relname = 'ticketoutputpdf_ticketlayoutitem'))

          
  order by nspname, relname, attnum;

2023-02-05T11:17:56.719000Z FATAL Failed to create the schema, see above.

resulting in an incomplete data transfer, e.g., only the default user exists. Before trying to transfer the data, I executed python -m pretix migrate.

Pretix version: 4.16
MariaDB version: Ver 15.1 Distrib 10.3.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Postgresql version: (PostgreSQL) 12.13 (Ubuntu 12.13-0ubuntu0.20.04.1)

Expected behaviour

No response

Steps to reproduce

1. Create pretix user and db in postgres
2. Execute python -m pretix migrate
3. Execute sudo -u postgres pgloader /tmp/pretix.load

Screenshots

No response

Link

No response

Browser (software, desktop or mobile?) and version

No response

Operating system, dependency versions

Ubuntu 20.04.5 LTS, Kernel 5.4.0

Version

4.16

[raphaelm]

This seems to be an incompatibility between the pgloader and PostgreSQL versions, see eg dimitri/pgloader#1034

which pgloader version do you have?

I tested the migration guide with debian stable and did not run into this issue

[marc-leinweber]

pgloader version is "3.6.1" from ubuntu repository

I've now installed the version from Postgres, but this refuses to start. This is very certainly no issue with pretix. I'll keep you updated.

[raphaelm]

Yeah, I'm also pretty sure the issue is not in pretix, but we'll have more people migrating and probably running into the issue, so if there's a solution, I'd like to add it to the docs :)

FWIW, I just installed pgloader 3.6.9 from the focal suite of the apt.postgresql.org repository (the one you linked) on a fresh 20.04 installation and it seems to start just fine for me?

root@pretix-issue3090:~# sudo -u postgres pgloader /tmp/pgloader.txt 
2023-02-05T16:17:46.009000Z LOG pgloader version "3.6.7~devel"
2023-02-05T16:17:46.059000Z LOG Migrating from #<MYSQL-CONNECTION mysql://pretix@localhost:3306/pretix {10061496A3}>
2023-02-05T16:17:46.060000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@UNIX:5432/pretix {100614A3A3}>

I did not test the full migration, though. Did you get a specific error message?

[marc-leinweber]

Sure, the erorr I got was

$ sudo -u postgres pgloader /tmp/pretix.load

FATAL: Failed to start the monitor thread.

Error opening #P"/tmp/pgloader/pgloader.log"

Installation was from deb http://apt.postgresql.org/pub/repos/apt focal-pgdg main

Solution was to wipe /tmp/pgloader and re-create it with postgres user (some chown stuff would have helped probably as well, but deleting was faster). The error obviously originates from installing pgloader from the ubuntu sources before using the postgres repository.

Migration is now completed with a bunch of warnings in the style of

2023-02-05T16:50:44.617000Z WARNING PostgreSQL warning: Constraint stripe_referencedstr_payment_id_51b1ab47_fk_pretixbas von Relation stripe_referencedstripeobject existiert nicht, wird bersprungen.

Please ignore the german locale, this is a off-the-shelf Strato VM with german localisation by default... did not invest any time to change this in a hobby project.

A very quick glance at the instance gave nothing to worry and no more deprecation warning. Seems like the migration was, albeit the warnings, successful.

[raphaelm]

Thanks for the update! I added a note to the doc. The warnings should be fine.