backupccl: backup/restore does not handle comments

[knz]

Since 19.2 CockroachDB supports the COMMENT statement to decorate database, tables and columns. We plan to extend it for sequences, views, indexes as well (#44135).

Comments are stored in a separate table system.comments.

cockroach dump in the CLI handles this by emitting the COMMENT statements alongside CREATE in the output.

However BACKUP does not capture the comments and they are thus not restorable.

Jira issue: CRDB-5231

[knz]

cc @hueypark FYI

[knz]

@jseldess do you still use the "docs-known-limitation" label? Otherwise what's the current process to report and document known limitations?

[dt]

On the bright side, I think the new "full-cluster backup" in 20.1 will preserve comments just fine (indeed, there is a test of this in the current PR h/t @pbardea), but backing up individual tables or databases currently will not right now. Doing so will require adding some special-purpose code to BACKUP to gather the applicable rows from system.comments for the set of IDs being backed up, and then code in RESTORE to re-key those rows to the new IDs and insert them into the existing system.comments on the RESTOREing cluster.

[jseldess]

@knz, yes, we use that label to identify new known limitations to document for a GA release. Once we have in the docs, we add docs-done.

[kenliu]

Nice catch @knz

[knz]

@dt caught it. I merely filed the issue.

[jseldess]

Documented in cockroachdb/docs#7118.

[hueypark]

@knz I think we already resolved this issue(#43152) and merged it to 20.1 branch. Am I wrong?

[knz]

yes, correct. thanks for noticing!

[hueypark]

@jseldess We should also reflect on the changes in the 20.1 documentation.

[pbardea]

Hm, unfortunately I don't think that #43152 quite resolves this issue. A BACKUP of a table or database will not by default include system.comments. This table would need to be backed up and manually restored and re-inserted (by first restoring it into a temporary db). So upon restoring there is no way to retrieve the comments.

[hueypark]

Interesting. Let me look a little further.

[adityamaru]

We are considering changing the format we store backup metadata as a project for our 21.2 release. This new format would unlock the ability to store larger types of metadata egs: comments, stats and so we should off on addressing this issue until then!

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!