RESTORE SNAPSHOT into new table name

CrateDB
1

I’m moving a table from an old cluster to a new one.

In the new cluster, I have already created a new version of the table with updated fields indexes etc.

I would like to restore a backed up version of the table into something like “tablename_bak”, then copy the relevant fields over to the new table.

Is it possible to specify the table name you want to restore the table into, or can it only be restored into the original table name, and restore would fail if a table of that name already exists.

Would it be possible to do something like this:

RESTORE SNAPSHOT s3_crate_snaps."snapshot1" TABLE schemaname.tablename AS tablename_bak WITH (wait_for_completion=true);
2

Hi, this feature is available starting from CrateDB 5.6

See docs for options schema_rename_pattern, schema_rename_replacement, table_rename_pattern and table_rename_replacement in

1 Like
3

Hi,

Would it be possible to have a practical example with the related SQL code?

For example, in S3 I have the snapshot of the ‘Alpha’ table.

I would like to do a restore but in a different table, called ‘Beta’.

Could you give me the correct RESTORE SNAPSHOT command to do this?

Thank you.
Luca

4

Not sure if you can directly change the name. You could try
"table_rename_replacement" = 'Beta'

otherwise use a prefix, e.g.:

RESTORE SNAPSHOT my_repo.my_snap
TABLE my_schema.my_table
WITH ("table_rename_replacement" = 'copy_$1');

→ my_schema.copy_my_table

and rename later

ALTER TABLE RENAME copy_my_table TO "Beta";
5

Hi, yes, you can restore into completely different table.

As @proddata mentioned above

You could try
"table_rename_replacement" = 'Beta'

would do the trick

RESTORE SNAPSHOT my_repo.my_snap
TABLE my_schema.Alpha
WITH ("table_rename_replacement" = 'Beta');

Please note, that schema won’t be affected:
Alpha → Beta (doc is implicit but fqn is doc.Alpha → doc.Beta)
my_schema.Alpha → my_schema.Beta

If you want to change both schema and name, you can combine schema/table rename parameters.