cockroachdb / cockroachdb/cockroach

backupccl: allow table renames during restore

Open
#96,830 4 comments 0 reactions 0 assignees View on GitHub
A-disaster-recovery C-enhancement T-disaster-recovery
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

It is a requirement that, when restoring tables into a database, tables (and views) of those names [must not exist in the target database](https://www.cockroachlabs.com/docs/stable/restore.html#tables). Besides being inconvenient, it can lead users down a surprisingly [frustrating road](https://cockroachlabs.slack.com/archives/C2C5FKPPB/p1675893556633089).

Users would like to do these table restores “in place”, next to the existing tables. We should enable this by allowing renaming the incoming tables at restore time.

Possible syntax using a new `AS` clause:

```
RESTORE TABLE bank.customers AS customers2, bank.accounts AS accounts2
FROM LATEST IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';
```

After completion, users will end up with two tables. They then have the normal SQL primitives they need to merge (UPSERT etc).

While we are in the neighborhood, should we offer `AS` for [restoring databases](https://github.com/cockroachdb/cockroach/issues/55791) as well? This would be semantically equivalent to `WITH new_db_name`.

@dt Also mentions the idea of a “swap” option -- the existing identically-named table stays online during restore, which may take a while. Upon restore completion, the newly-restored table replaces the previous table, presumably atomically.

See also https://github.com/cockroachdb/cockroach/issues/55791

See also [this Docs issue](https://cockroachlabs.atlassian.net/browse/DOC-6903) (internal) to be more prescriptive on when one should restore a table vs a database.

Jira issue: CRDB-24353

Contributor guide

Open the contributing guide

Research direction

Start with the RESTORE TABLE syntax and the linked RESTORE database issue #55791, then review the restore documentation describing name conflicts. Clarify whether the goal is only an AS clause for tables or also database renaming and table swapping; done should include an agreed syntax and behavior for restoring renamed tables alongside existing tables.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.