Does Amazon Redshift support extension dblink?

Question

CREATE EXTENSION dblink;

I tried to create an extension dblink in AWS, but I receive this error.

ERROR: syntax error at or near "EXTENSION" LINE 1: create extension dblink;

Landon

Answer 1

Yes and No.

Amazon Redshift itself does not provide dblink. However, you can install dblink on a different PostgreSQL server and use it to connect to Amazon Redshift.

See: JOIN Amazon Redshift AND Amazon RDS PostgreSQL WITH dblink

Lafollette

for completeness I add that in the same way is supported in Redshift Spectrum too – Reactor

Is this supported cross account also? – Guerra

@Guerra As long as the two databases can communicate with each other, it will work. – Lafollette

Answer 2

While AWS Redshift is based on Postgresql, it has various enhancements/alterations to integrate both AWS infrastructure and also a very different backend from standard Postgresql. In order to create a DB Link, you don't need to (in fact, you can't) load the DB Link extension.

Instead, AWS have replaced the standard Postgresql CREATE SERVER AND IMPORT FOREIGN SCHEMA statement with their unique CREATE EXTERNAL SCHEMA - this creates your DB links for you:

CREATE EXTERNAL SCHEMA [IF NOT EXISTS] local_schema_name
FROM { [ DATA CATALOG ] | HIVE METASTORE | POSTGRES | MYSQL | KINESIS }
[ DATABASE 'database_name' ]
[ REGION 'aws-region' ]
[ URI 'hive_metastore_uri' [ PORT port_number ] ]
IAM_ROLE { default | 'arn:aws:iam::<AWS account-id>:role/<role-name>' }
[ SECRET_ARN 'ssm-secret-arn' ]          
[ CATALOG_ROLE 'catalog-role-arn-string' ] 
[ CREATE EXTERNAL DATABASE IF NOT EXISTS ]

This does everything in one statement. Full documentation can be found here

Dissemble

This should be the current answer. – Numbers

Answer 3

The answer is "Yes", but you have to use Amazon RDS as a ‘cache’ by leveraging the Dblink feature of RDS. This allows us to make any Amazon Redshift table available as a relation in RDS, without the need to explicitly copy that data over. Dblink handles moving the data at the block level.

We've used the approach described in the link that John has provided above. We did a detailed write up of how we did it in a blog post "Have Your Postgres Cake with Amazon Redshift and eat it, too."

https://www.intermix.io/blog/using-amazon-redshift-with-amazon-rds/

Rie