Skip to content

Why does my Zero ETL integration between AWS Aurora Postgres RDS and Redshift Serverless silently fail to sync tables?

0

We have 125 tables in the source cluster DB. The filter is set to include: core_analytics.*.* (but we've tried other filters as well). For a few days the integration syncs all 125 tables but then it all of a sudden will show only 61 tables synced and 0 failed!

Once that happens, it will stay at only 61 forever and I haven't found a way to fix it except to delete & rebuild the integration.

Where did the 64 tables go? They are not marked as failed in the integration state (when running SELECT * FROM SVV_INTEGRATION_TABLE_STATE;). It's like they never existed. `

2 Answers
0

This issue with your Zero-ETL integration between Aurora PostgreSQL and Redshift Serverless could be happening for several reasons.

First, check if your tables have primary keys. Tables without primary keys won't replicate to Amazon Redshift, and the integration status might change to "Needs attention" with those tables showing as "Failed" in the monitoring dashboard.

Some specific PostgreSQL data types and structures aren't supported in Zero-ETL integrations, including:

  • Unlogged tables and materialized views
  • Geometry data types
  • Data greater than 64KB
  • Tables with declarative partitioning transactions

If you've performed certain operations on your source database, tables might enter a "ResyncRequired" state and become temporarily unavailable in Redshift. These operations include adding columns to specific positions, adding timestamp columns with CURRENT_TIMESTAMP default values, or performing multiple column operations in a single command.

To fix tables that aren't appearing in Redshift, you can try resynchronizing them with:

ALTER DATABASE dbname INTEGRATION REFRESH TABLES table1, table2;

You should also check the SVV_INTEGRATION_TABLE_STATE view to identify any tables with issues. If tables have unsupported data types, you may need to remove them from the source and recreate them with supported data types.

If none of these solutions work, as you've discovered, deleting and recreating the integration might be the only option. For persistent issues, consider reviewing the data type compatibility between Aurora PostgreSQL and Redshift to ensure all your tables can be properly replicated.
Sources
Troubleshooting Aurora zero-ETL integrations - Amazon Aurora
Troubleshooting zero-ETL integrations - Amazon Redshift
Troubleshooting Amazon RDS zero-ETL integrations - Amazon Relational Database Service
Amazon RDS zero-ETL integrations - Amazon Relational Database Service

answered a year ago

0

Enter image description here Normally in Zero ETL tables with constraint or data type issues show as 'failed' in the SVV integration state but in this case, the tables simply vanish and are not mentioned as having succeeded or failed. The integration reports that it has successfully synced all table, even after an all tables SVV refresh BUT it's only showing 61 tables. The postgres database has 125 tables all of which have PKs. Redshift shows 61 tables. I've attached a chart of the cloudwatch metrics to show what I mean...

Why does zero etl not state what the issue is ? And why does it lack observability of any kind?

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.