Restoring an Amazon Keyspaces table to a point in time
Amazon Keyspaces (for Apache Cassandra) point-in-time recovery (PITR) allows you to restore Amazon Keyspaces table data to any point in time within the last 35 days. The first part of this tutorial shows you how to restore a table to a point in time by using the Amazon Keyspaces console, the AWS Command Line Interface (AWS CLI), and Cassandra Query Language (CQL). The second part shows you how to restore a deleted table using the AWS CLI and CQL.
Topics
Before you begin
If you haven't already done so, you must configure the appropriate permissions for
the user to restore Amazon Keyspaces tables. In AWS Identity and Access Management (IAM), the
AWS managed policy AmazonKeyspacesFullAccess
includes the permissions to restore
Amazon Keyspaces tables. For detailed steps to implement a policy with minimum required permissions, see Permissions required to restore a table.
Restoring a table to a point in time (console)
The following example demonstrates how to use the Amazon Keyspaces console to restore an
existing table named mytable
to a point in time.
Note
This procedure assumes that you have enabled point-in-time recovery. To enable
PITR for the mytable
table, follow the steps in Enabling PITR with the
console.
Sign in to the AWS Management Console, and open the Amazon Keyspaces console at https://console.aws.amazon.com/keyspaces/home
. -
In the navigation pane on the left side of the console, choose Tables.
-
In the list of tables, choose the
mytable
table. -
On the Backups tab of the
mytable
table, in the Point-in-time recovery section, choose Restore. -
For the new table name, enter
mytable_restored
. -
To define the point in time for the restore operation, you can choose between two options:
Select the preconfigured Earliest time.
Select Specify date and time and enter the date and time you want to restore the new table to.
Note
You can restore to any point in time within Earliest time and the current time. Amazon Keyspaces restores your table data to the state based on the selected date and time (day:hour:minute:second).
Choose Restore to start the restore process.
The table that is being restored is shown with the status Restoring. After the restore process is finished, the status of the
mytable_restored
table changes to Active.Important
While a restore is in progress, don't modify or delete the AWS Identity and Access Management (IAM) policies that grant the IAM entity (for example, user, group, or role) permission to perform the restore. Otherwise, unexpected behavior can result. For example, suppose that you removed write permissions for a table while that table was being restored. In this case, the underlying
RestoreTableToPointInTime
operation can't write any of the restored data to the table.You can modify or delete permissions only after the restore operation is completed.
Restoring a table to a point in time with the AWS CLI
The following procedure shows how to use the AWS CLI to restore an existing table named
myTable
to a point in time.
In the first step, you create a simple table named
myTable
that has PITR enabled. The command has been broken up into separate lines for readability.aws keyspaces create-table --keyspace-name 'myKeyspace' --table-name 'myTable' --schema-definition 'allColumns=[{name=id,type=int},{name=name,type=text},{name=date,type=timestamp}],partitionKeys=[{name=id}]' --point-in-time-recovery 'status=ENABLED'
Confirm the properties of the new table and review the
earliestRestorableTimestamp
for PITR.aws keyspaces get-table --keyspace-name 'myKeyspace' --table-name 'myTable'
The output of this command returns the following.
{ "keyspaceName": "myKeyspace", "tableName": "myTable", "resourceArn": "arn:aws:cassandra:us-east-1:111222333444:/keyspace/myKeyspace/table/myTable", "creationTimestamp": "2022-06-20T14:34:57.049000-07:00", "status": "ACTIVE", "schemaDefinition": { "allColumns": [ { "name": "id", "type": "int" }, { "name": "date", "type": "timestamp" }, { "name": "name", "type": "text" } ], "partitionKeys": [ { "name": "id" } ], "clusteringKeys": [], "staticColumns": [] }, "capacitySpecification": { "throughputMode": "PAY_PER_REQUEST", "lastUpdateToPayPerRequestTimestamp": "2022-06-20T14:34:57.049000-07:00" }, "encryptionSpecification": { "type": "AWS_OWNED_KMS_KEY" }, "pointInTimeRecovery": { "status": "ENABLED", "earliestRestorableTimestamp": "2022-06-20T14:35:13.693000-07:00" }, "defaultTimeToLive": 0, "comment": { "message": "" } }
You can restore an active table to any point-in-time between the
earliestRestorableTimestamp
and the current time in one second intervals. Current time is the default.-
To restore a table to a point in time, specify a
restore_timestamp
in ISO 8601 format. You can chose any point in time during the last 35 days in one second intervals. For example, the following command restores the table to theEarliestRestorableDateTime
.aws keyspaces restore-table --source-keyspace-name 'myKeyspace' --source-table-name 'myTable' --target-keyspace-name 'myKeyspace' --target-table-name 'myTable_restored' --restore-timestamp "2022-06-20 21:35:14.693"
The output of this command returns the ARN of the restored table.
{ "restoredTableARN": "arn:aws:cassandra:us-east-1:111222333444:/keyspace/myKeyspace/table/myTable_restored" }
To restore the table to the current time, you can omit the
restore-timestamp
.aws keyspaces restore-table --source-keyspace-name 'myKeyspace' --source-table-name 'myTable' --target-keyspace-name 'myKeyspace' --target-table-name 'myTable_restored1'"
Important
While a restore is in progress, don't modify or delete the AWS Identity and Access Management (IAM)
policies that grant the IAM entity (for example, user, group, or role) permission
to perform the restore. Otherwise, unexpected behavior can result. For example,
suppose that you removed write permissions for a table while that table was being
restored. In this case, the underlying RestoreTableToPointInTime
operation can't write any of the restored data to the table.
You can modify or delete permissions only after the restore operation is completed.
Restoring a table to a point in time with CQL
The following procedure shows how to use CQL to restore an existing table named
mytable
to a point in time.
Note
This procedure assumes that you have enabled point-in-time recovery. To enable PITR on the table, follow the steps in Enabling PITR using CQL.
-
You can restore an active table to a point-in-time between
earliest_restorable_timestamp
and the current time. Current time is the default.To confirm that point-in-time recovery is enabled for the
mytable
table, query thesystem_schema_mcs.tables
as follows.SELECT custom_properties FROM system_schema_mcs.tables WHERE keyspace_name = 'mykeyspace' AND table_name = 'mytable';
Point-in-time recovery is enabled as shown in the following sample output.
custom_properties ----------------- { ..., "point_in_time_recovery": { "earliest_restorable_timestamp":"2020-06-30T19:19:21.175Z" "status":"enabled" } }
-
Restore the table to a point in time, specified by a
restore_timestamp
in ISO 8601 format. In this case, themytable
table is restored to the current time. You can omit theWITH restore_timestamp = ...
clause. Without the clause, the current timestamp is used.RESTORE TABLE mykeyspace.mytable_restored FROM TABLE mykeyspace.mytable;
You can also restore to a specific point in time. You can specify any point in time during the last 35 days. For example, the following command restores the table to the
EarliestRestorableDateTime
.RESTORE TABLE mykeyspace.mytable_restored FROM TABLE mykeyspace.mytable WITH restore_timestamp = '2020-06-30T19:19:21.175Z';
For a full syntax description, see RESTORE TABLE in the language reference.
To verify that the restore of the table was successful, query the
system_schema_mcs.tables
to confirm the status of the table.
SELECT status FROM system_schema_mcs.tables WHERE keyspace_name = 'mykeyspace' AND table_name = 'mytable_restored'
The query shows the following output.
status ------ RESTORING
The table that is being restored is shown with the status
Restoring. After the restore process is finished, the status of
the mytable_restored
table changes to
Active.
Important
While a restore is in progress, don't modify or delete the AWS Identity and Access Management (IAM)
policies that grant the IAM entity (for example, user, group, or role) permission
to perform the restore. Otherwise, unexpected behavior can result. For example,
suppose that you removed write permissions for a table while that table was being
restored. In this case, the underlying RestoreTableToPointInTime
operation can't write any of the restored data to the table.
You can modify or delete permissions only after the restore operation is completed.
Restoring a deleted table with the AWS CLI
The following procedure shows how to use the AWS CLI to restore a deleted table named
myTable
to the time of deletion.
Note
This procedure assumes that PITR was enabled on the deleted table.
-
Delete the table that you created in the previous tutorial.
aws keyspaces delete-table --keyspace-name 'myKeyspace' --table-name 'myTable'
-
Restore the deleted table to the time of deletion with the following command.
aws keyspaces restore-table --source-keyspace-name 'myKeyspace' --source-table-name 'myTable' --target-keyspace-name 'myKeyspace' --target-table-name 'myTable_restored2'
The output of this command returns the ARN of the restored table.
{ "restoredTableARN": "arn:aws:cassandra:us-east-1:111222333444:/keyspace/myKeyspace/table/myTable_restored2" }
Restoring a deleted table with CQL
The following procedure shows how to use CQL to restore a deleted table named
mytable
to the time of deletion.
Note
This procedure assumes that PITR was enabled on the deleted table.
-
To confirm that point-in-time recovery is enabled for a deleted table, query the system table. Only tables with point-in-time recovery enabled are shown.
SELECT custom_properties FROM system_schema_mcs.tables_history WHERE keyspace_name = 'mykeyspace' AND table_name = 'my_table';
The query shows the following output.
custom_properties ------------------ { ..., "point_in_time_recovery":{ "restorable_until_time":"2020-08-04T00:48:58.381Z", "status":"enabled" } }
-
Restore the table to the time of deletion with the following sample statement.
RESTORE TABLE mykeyspace.mytable_restored FROM TABLE mykeyspace.mytable;