Redshift Federated Query error: timeout expired code: 2500

Question

After having created a 'Federated Schema' to access a remote RDS instance (different AWS account but VPC Peered):

CREATE EXTERNAL SCHEMA federated_schema
FROM POSTGRES
DATABASE 'popstgres_db' SCHEMA 'public'
URI 'my.host.com'
IAM_ROLE 'arn:aws:iam::1223456789:role/MyRole'
SECRET_ARN 'arn:aws:secretsmanager:my-region-1:1223456789:secret:my/secret';

I try to test it with a simple Federated Query:

SELECT COUNT(*)
FROM federated_schema.my_table

But after some waiting I get the following error message:

error: timeout expired code: 2500

The error message doesn't really help as I already checked there is connection between the relative 2 VPCs hosting the relative services.

What is the problem here?

Answer 1

As noted in this Enhanced VPC document https://aws.amazon.com/premiumsupport/knowledge-center/redshift-enhanced-vpc-routing/

To determine whether you should enable Amazon Redshift enhanced VPC routing, consider the following use cases:

[...]Federated queries to private Amazon Relational Database Service (Amazon RDS) instances located in a peered VPC.

So it is necessary to enable Enhanced VPC Routing on your Redshift Cluster:

from Console → Clusters → my-dwh → Properties → Network and security settings → Edit → Enhanced VPC routing)

After enabling it the Federated Query started working