How to upgrade the jobs table from v10 to v12?

[michie1]

I want to upgrade from v10 to v12. I've read the following:

MAJOR: Automatic migration from v10 or lower is not supported due to significant job partitioning changes. As with the advice in the v10 release notes, the safest option is to manually move jobs via the API or SQL via INSERT ... SELECT. I always prefer automatic migrations whenever they are easy to run in both directions without data loss, but unfortunately v11 did not pass this test.

Source: Release notes V11

This pieces references to v10 release notes:

MAJOR: Automatic migration from v9 or lower is not currently available. A new partitioned job table was created, as well as new queue storage policies that make it difficult to honor the previous unique constraints in an upgrade scenario. The safest option is to manually move jobs from pgboss 9 to v10 via the API, or at least use the API to prepare for a manual migration via INSERT ... SELECT.

I'm allowed to stop the producers and empty the jobs table. Do I need to remove the jobs table or is emptying it enough?

[timgit]

Drop the schema to uninstall and then let pg-boss recreate it

[michie1]

Ah, thanks. 👌 And then create the queues / schedules again I understand.

[bcomnes]

Dropping breadcrumbs: I found this discussion here was helpful as well: #590