Postgres external schemas with Redshift: can I use a foreign data wrapper?

0
votes

I have a Postgres DB that I want to query from my Redshift cluster. I've successfully created the schema with CREATE EXTERNAL SCHEMA.

But the schema itself is empty. I don't see any tables in the schema drop-down in the query editor, and when I attempt to query a table I know exists, I get the error message:

error: Table may not exist or is unsupported relation type code: 25000 context: query: 0 location: pgclient.cpp:189 process: padbmaster [pid=19735]

I suspect this is because the tables are foreign tables (replicating from a vendor's database). Is there a way (either a config change in Postgres or something different in Redshift) to get Redshift to recognize these tables and let me query them?

1
postgresqlamazon-redshift

1 Answers

0
votes

The reason why your query is returning an error message is likely because it can never access your remote DB. I assume you've followed all of the appropriate steps here: https://docs.aws.amazon.com/redshift/latest/dg/getting-started-federated.html

Try enabling Enhanced VPC Routing in the "Network and security" configuration on your Redshift cluster.

I ran into the same issue where even though my Postgres RDS instance and Redshift cluster were in the same VPC and subnet group, I could not create an external schema connection until enhanced VPC routing was enabled.

Relevant thread: https://forums.aws.amazon.com/thread.jspa?threadID=323013