CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-rds
Gurmokh
Gurmokh

Reputation: 2081

Cannot create a Foreign Data Wrapper in RDS Postgres

So I'm trying to create a FDW in a running v12 Postgres RDS instance.

The extension already exists, but for some reason I cannot create one. This seems crazy simple having come from an on premises set up.

What am I missing here ?

   sourceDatabase=> create extension postgres_fdw ; 
ERROR:  extension "postgres_fdw" already exists
sourceDatabase=> create foreign data wrapper testwrap ; 
ERROR:  permission denied to create foreign-data wrapper "testwrap"
HINT:  Must be superuser to create a foreign-data wrapper.
sourceDatabase=> \du
                                                               List of roles
         Role name         |                   Attributes                   |                          Member of                           
---------------------------+------------------------------------------------+--------------------------------------------------------------
 rds_superuser             | Cannot login                                   | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin                  | Superuser, Create role, Create DB, Replication+| {}
                           | Password valid until infinity                  | 
 rdsrepladmin              | No inheritance, Cannot login, Replication      | {}
 sourceUser                | Create role, Create DB                        +| {rds_superuser}
                           | Password valid until infinity                  |

Upvotes: 0

Views: 3542

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247235

If you have the postgres_fdw extension installed, you already have a foreign data wrapper called postgres_fdw, so there is no need to create another one.

Use \dew in psql to verify that there is already a foreign data wrapper.

What you need to do now is create a foreign server that contains the connect string to the remote PostgreSQL database, a user mapping that contains the authentication information for that database and foreign tables which point to tables in the remote database.

Upvotes: 3

TheDataGuy
TheDataGuy

Reputation: 3118

What Adrian Klaver said is correct. (What is different is whether you want to create a wrapper or a server).

Foreign data wrapper:

This is Postgres's native feature to access the data across the multiple databases including databases like Postgres, Oracle, Redis and etc. The funny fact is you can read your Twitter messages via the foreign-data wrapper. Here is the list of active wrappers.

But you have to write the handlers and etc to access the data.

https://www.postgresql.org/docs/10/sql-createforeigndatawrapper.html

Postgres_FDW:

postgres_fdw is an extension developed under the postgres's contrib library and it's only specific to Postgres to make the Postgres integration much easier. All the necessary handlers and the functions are bundled with this extension.

Creating the foreign data wrappers needs the real superuser permission and it won't work rds_superuser.

The functionality provided by this module overlaps substantially with the functionality of the older dblink module. But postgres_fdw provides more transparent and standards-compliant syntax for accessing remote tables, and can give better performance in many cases.

So please refer to the postgres_fdw documentation instead of the Foreign data wrapper document.

OR better refer to the official AWS RDS doc which is already shared by VynlJunkie(in the comments section)

Upvotes: 1

Related Questions