RESTORE ... WITH into_schema

[data-matt]

Is your feature request related to a problem? Please describe.

In light of the change that the following is deprecated for **use across databases***: ALTER TABLE test RENAME TO new_table_name

If someone was to restore a table using into_db to restore a table to a different target database, they wouldn't be able to move that table from the target database, to the original database.

Describe the solution you'd like Please provide an option to restore the table into the same database with a different schema or name, so that you can restore another copy of a table to the original database.

e.g

RESTORE bank.customers FROM '2021/03/23-213101.37' IN 's3://{bucket_name}/{path/to/backup}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}'
WITH into_schema = 'newschema';

Additional context Add any other context or screenshots about the feature request here.

Jira issue: CRDB-14455

[knz]

I think there's a misunderstanding: ALTER RENAME TO is still supported and will continue to be (like in postgres).

The only thing that's limited is the ability to rename across databases.

• rename table within schema: ALTER RENAME TO works
• rename table across user-defined schemas: ALTER SET SCHEMA (not RENAME TO)
• rename table across databases: we need to talk about this. It's midlly possible we will still able to support a way to do this without backup/restore, just in a different way than pg

[blathers-crl[bot]]

cc @cockroachdb/bulk-io

[data-matt]

@knz, that is understood. The concern comes from the point of view that a customer currently can restore a table to a different target database for staging purposes and move it. If we remove the ability to rename a table to move it from one database to another, then this type of restore isn't viable for that scenario any more. So, if we provide a way to restore a table to a different schema, we can stage tables in under a different schema right?

[knz]

yes. But I also wonder if we can support moving a table from one db to another using another mechanism than a simple rename? maybe @ajwerner has opinions on this.

[data-matt]

Don't mind 👍 We just need to be cognisant that we still leave customers with a method to restore a table some where else other.. or with a different name. Otherwise some customers will feel the pain of regression.

[dt]

I think this is a perfectly fine feature for RESTORE to have -- restore into a specific schema -- but echo @knz's point that backing it up and restoring it is probably not the best way to move an existing table; it's extremely expensive to read and write all the rows vs just update the metadata. So while I'm happy to include this in restore, it think the user-story mentioned as motivating it should go over to schema-schema.

[data-matt]

This thinking comes from the POV of a Disaster Recovery scenario. Not using backup/restore as a mechanism to move tables. Theres many scenarios, but one known scenario is that after the DR event, we restore only the schema immediately (with/without minimal data) to ensure the application is operational, then restore the table in the background, once its restored, we can move the restored table back to "production" database. Therefore, we are not coupled to the restore time, to become operational again.