How can I troubleshoot table restore errors from snapshot in Amazon Redshift?

Short description

Amazon Redshift allows you to restore a table from your existing available snapshots. You don't have to restore an entire cluster.

When you restore a single table from a snapshot, specify the source snapshot, database, schema, and table name. For a restored table, specify the target database, schema, and a new name.

Use the Amazon Redshift console to initiate a table restore from snapshot. Or, use the RestoreTableFromClusterSnapshot API operation. For more information, see Restoring a table from a snapshot.

A table level restore fails for some of the following common errors:

• Amazon Redshift can't retrieve snapshot metadata from Amazon Simple Storage Service (Amazon S3) for tables with metadata files over the 16 MB limit. You receive the following error message: "Internal Error: Snapshot metadata doesn't exist."
• If you can't find the specified schema in the snapshot, then the schema had no content when you created the snapshot and has no backup. Then, you receive the "Target schema 'schema name' doesn't exist in the snapshot. Consider using one of the following: <list of schemas available in the snapshot>" error message.
• If you can't find the specified database in the snapshot, then the database had no content when you created the snapshot and has no backup. Then, you receive the "Target database '<database name>' doesn't exist in snapshot. Consider using: <list of databases available in snapshot>" error message.
• If you try to restore a table to a different cluster or after you resize different node types, then you receive an InvalidClusterState error.
• If you try to restore multiple concurrent tables, then you receive an InProgressTableRestoreQuotaExceededFault error.
• If you try to restore a nonexistent table from a snapshot, then you receive an InvalidTableRestoreArgument error.
• If you try to restore from a non-existent or incorrect snapshot, then you receive a ClusterSnapshotNotFound error.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Check snapshot details

Review the snapshot details in the AWS Management Console to confirm that the table is included and that the cluster version is compatible.

Verify permissions

Review your AWS Identity and Access Management (IAM) user permissions to make sure that the policy allows table restores from snapshots. To check the role permissions, complete the following steps:

1. Open the IAM console.
2. In the navigation panel, choose Roles.
3. Select the role that is associated with the cluster.
4. Verify that the role permissions include redshift:RestoreTableFromClusterSnapshot under allowed actions. If the permissions don't include, then update the permissions.

AWS CLI

Use the AWS CLI to restore the table and identify the errors.

Example:

aws redshift restore-table-from-cluster-snapshot —cluster-identifier mycluster-example
--new-table-name my-new-table \
--snapshot-identifier my-snapshot-id \
--source-database-name sample-database \
--source-table-name my-source-table

Check cluster compatibility

Check that your current Amazon Redshift cluster matches the cluster where you took the snapshot. If you used a different cluster, then the table restore fails.

Overwrite existing tables

If a table with the same name already exists, then you can't restore your table to an Amazon Redshift cluster. To check if the table name already exists, connect to the Amazon Redshift database. Then, run the following SQL query:

select * from svv_table_info where “table”='tablename';

Note: Replace tablename with the table name that you want to restore from the snapshot.

Related information

Excluding tables from snapshots