restore: fix full cluster restore after public schema migration

[RichardJCai]

As of today (21.2) IDs 52 and onwards are used by objects created by the user.

Once the public schema migration has been completed, new clusters created on CockroachDB versions >=22.1 will use IDs 52, 53 an in order to create two new public schemas for the default created dbs (defaultdb, postgres).
(defaultdb 50, defaultdb public schema 51, postgres, 52, postgres public schema 53)

This creates an issue with full cluster restore as we currently don't support re-keying.

For example, if we backed up a cluster in 21.2 where the user simply created one table t and inserted some rows, t would use ID 52. Restoring this table into a cluster where the public schema migration has been completed will be an issue since 52 will be used by postgres database.

The solution to this would be to drop both defaultdb and postgres perform the restore where we are guaranteed those IDs are not being used by the dbs, and recreate the databases with their public schemas afterwards.

This is similar to how we create the temporary system database for restore above the ID space (above the maximum id being restored).

cc @dt @ajwerner

Epic CRDB-1521

[blathers-crl]

Hi @RichardJCai, I've guessed the C-ategory of your issue and suitably labeled it. Please re-label if inaccurate.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[RichardJCai]

Should I put this under @cockroachdb/bulk-io or @cockroachdb/sql-experience since I'll probably be implementing this?

[shermanCRL]

I think it needs to start with on the SQL team. cc @adityamaru

[adityamaru]

Sorry I couldn't join the meeting so just catching up. My first reaction was ugh more static IDs 😄 but I guess there is more context here. Dropping the databases before a cluster restore does simplify stuff a lot. I'll catch up on the notes!

[dt]

@adityamaru heh, sorry, we didn't keep detailed notes, but essentially plan is to 1) first make restoring into a cluster with realized public schema descriptors work, and then 2) make restoring in a world with dynamically assigned system and default IDs work.

Doing 1) requires a) materializing new public schema descriptors if the backup is missing them (e.g. backed up pre-migration) and then re-parenting things, of course, but also, first b) moving any existing database or schemas descriptors in the restoring cluster that have ID > min-id-in-backup, to above the last ID in the backup, so as to avoid conflicting with IDs in the backup, so we're not forced to re-key to avoid them at their current IDs. We quibbled on whether or not to do this, if we're headed to a world in 2) where we might need to rekey to avoid system tables in the restoring cluster that have been created in our backup's ID space, but we decided it was worth doing it anyway, since it both unblocked the public schema thing now, without waiting for rekeying, and also because even if we could rekey, it is cheaper to move a descriptor to a new ID than a whole table, so we'd probably keep this around even when we add dynamic system table ids and rekeys.