Why is my Amazon Redshift snapshot missing some tables?

2 minute read

I restored a snapshot from an Amazon Redshift cluster, but the snapshot is missing some tables. How do I back up my missing tables?

Short description

Tables created as no-backup tables are excluded from Amazon Redshift snapshots. Verify whether Amazon Redshift excluded your table because it was created using the BACKUP NO parameter. Then, perform a deep copy.


To back up missing tables from your Amazon Redshift snapshot, perform the following steps:

1.    Check the Data Definition Language (DDL) of the tables that are missing from the snapshot.

2.    If the table's DDL is unavailable, then run the following query as a superuser:

SELECT DISTINCT Rtrim(n.nspname) AS schema_name,
                Rtrim(name)      AS table_name,
FROM   stv_tbl_perm t
       join pg_class c
         ON t.id = c.oid
       join pg_namespace n
         ON n.oid = c.relnamespace
ORDER  BY 1,2;

The query above identifies tables in the connected database that aren't backed up in the snapshot. It queries STV_TBL_PERM, the system table that is only visible to superuser accounts. For more information about views that are only available to superusers, see Visibility of data in system tables and views.

Note: A value of 0 for the backup column indicates that the table was created using the BACKUP NO parameter. You cannot alter an existing table in Amazon Redshift using the BACKUP YES parameter.

3.    If your table was created as a no-backup table, then recreate the table without the BACKUP NO parameter.

4.    Perform a deep copy of your missing tables.

Related information


Amazon Redshift Snapshots

AWS OFFICIALUpdated a year ago