- Newest
- Most votes
- Most comments
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
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
