Wondering why is Amazon Redshift snapshot missing tables? We can help you with troubleshooting this issue!

Here, at Bobcares, we often receive similar queries as a part of our AWS Support Services.

Today, let’s see the steps followed by our Support Engineers to back up the missing tables from the snapshot.

 

Amazon Redshift snapshot missing tables

 
By default, all tables are included in the Amazon Redshift snapshot. But if we were to create a no-backup table, that is we include the BACKUP NO parameter while creating a table, then those tables will be excluded from Amazon Redshift snapshots.

Now let’s see the steps followed by our Support Engineers to backup the missing tables from the Amazon redshift snapshot.

1. At first, we have to check the DDL (Data Defenition Language) of the missings tables from the snapshot.

2. If the DDL of the table is unavailable, we need to run the following query as a superuser.

SELECT DISTINCT Rtrim(n.nspname) AS schema_name,
Rtrim(name) AS table_name,
backup
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 above query recognizes the tables that are not backed up in the snapshot.

3. Now we need to recreate the table without the BACKUP NO parameter if the table was created as a no-backup table.

4. Then we should do a deep copy of the missing tables.

Steps for a deep copy using the original table DDL:

1. At first, recreate the table DDL by running a script called v_generate_tbl_ddl. This step is optional.

2. Then we should generate a copy of the table using the original CREATE TABLE DDL.

3. To populate the copy with data from the original table, we can use an INSERT INTO … SELECT statement.

4. Then drop the Original table.

5. Then to rename the copy to the original table name, we can use an ALTER TABLE statement.

The following shows the example of deep copy:

create table samplecopy ( … );
insert into samplecopy (select * from sample);
drop table sample;
alter table samplecopy rename to sample;

Here sample is the table name and samplecopy is the duplicate of sample table.

Also, note that if we create a table with the value of 0 for the backup column, it shows that the table is a no-backup table. We cannot change an existing table using the BACKUP YES parameter.

[Need help with more AWS queries? We’d be happy to assist]
 

Conclusion

 
To conclude, today we saw the steps followed by our Support Engineers to back up the missing tables from the Amazon Redshift snapshot.