A Guide to SetUp a Foreign Server in Amazon RDS for PostgreSQL

Introduction

Amazon RDS for PostgreSQL is a managed database service that allows seamless integration with external databases using Foreign Data Wrappers (FDW). FDWs enable PostgreSQL to access data from other databases (PostgreSQL or other sources) as local tables.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Understanding Foreign Data Wrappers (FDW)

FDWs in PostgreSQL allow database administrators to query external databases. They provide an abstraction layer, enabling remote data access with minimal performance overhead. Some common use cases include:

Accessing a remote PostgreSQL database
Integrating Amazon RDS with on-premise databases
Querying heterogeneous data sources (e.g., MySQL, Oracle, MongoDB, Redshift)

PostgreSQL supports multiple FDWs, including:

postgres_fdw (for PostgreSQL-to-PostgreSQL connections)
mysql_fdw (for MySQL integration)
odbc_fdw (for ODBC-compatible databases)
For this tutorial, we will focus on postgres_fdw to connect two PostgreSQL databases, both hosted on Amazon RDS.

Prerequisites

Before configuring a foreign server in Amazon RDS for PostgreSQL, ensure the following:

Amazon RDS PostgreSQL Instance is up and running.
Extensions (postgres_fdw) are supported in your PostgreSQL version.
Security Groups allow connections between the RDS instances.
A database user with the required privileges is available.

You can check your PostgreSQL version by running:

SELECT version();

1	SELECT version();

Step-by-Step Guide

Step 1: Enable the postgres_fdw Extension

First, enable the postgres_fdw extension on the primary database:

CREATE EXTENSION postgres_fdw;

1	CREATE EXTENSION postgres_fdw;

Verify the installation:

SELECT * FROM pg_extension WHERE extname = 'postgres_fdw';

1	SELECT * FROM pg_extension WHERE extname = 'postgres_fdw';

Step 2: Create a Foreign Server

A foreign server represents the remote PostgreSQL database. Use the following syntax:

CREATE SERVER remote_postgres_server  
FOREIGN DATA WRAPPER postgres_fdw  
OPTIONS (host '<REMOTE_DB_ENDPOINT>', port '5432', dbname '<REMOTE_DB_NAME>');

CREATE SERVER remote_postgres_server

FOREIGN DATA WRAPPER postgres_fdw

OPTIONS (host '<REMOTE_DB_ENDPOINT>', port '5432', dbname '<REMOTE_DB_NAME>');

Replace:

<REMOTE_DB_ENDPOINT> with the remote RDS PostgreSQL instance endpoint
<REMOTE_DB_NAME> with the target database name

To list foreign servers:

SELECT * FROM pg_foreign_server;

1	SELECT * FROM pg_foreign_server;

Step 3: Create a User Mapping

A user mapping links local users to remote database users. Create it using:

CREATE USER MAPPING FOR current_user  
SERVER remote_postgres_server  
OPTIONS (user '<REMOTE_USER>', password '<REMOTE_PASSWORD>');

CREATE USER MAPPING FOR current_user

SERVER remote_postgres_server

OPTIONS (user '<REMOTE_USER>', password '<REMOTE_PASSWORD>');

Check existing mappings:

SELECT * FROM pg_user_mappings;

1	SELECT * FROM pg_user_mappings;

For security, avoid storing credentials in SQL scripts and use AWS Secrets Manager instead.

Step 4: Create a Foreign Table

A foreign table acts as a proxy to the remote table. Define it as follows:

CREATE FOREIGN TABLE remote_table (  
    id INT,  
    name TEXT,  
    created_at TIMESTAMP  
)  
SERVER remote_postgres_server  
OPTIONS (schema_name 'public', table_name 'source_table');

CREATE FOREIGN TABLE remote_table (

id INT,

name TEXT,

created_at TIMESTAMP

)

SERVER remote_postgres_server

OPTIONS (schema_name 'public', table_name 'source_table');

The columns must match the remote table’s structure.

The schema_name should be the actual schema in the remote database.

To verify:

SELECT * FROM information_schema.foreign_tables;

1	SELECT * FROM information_schema.foreign_tables;

Step 5: Querying the Remote Table

Once the foreign table is set up, query it as a local table:

SELECT * FROM remote_table LIMIT 10;

1	SELECT * FROM remote_table LIMIT 10;

To ensure performance, analyze query execution:

EXPLAIN ANALYZE SELECT * FROM remote_table;

1	EXPLAIN ANALYZE SELECT * FROM remote_table;

Step 6: Managing and Securing Foreign Servers

Granting Privileges

Grant access to specific users:

GRANT USAGE ON FOREIGN SERVER remote_postgres_server TO some_user;
GRANT SELECT ON remote_table TO some_user;

1 2	GRANT USAGE ON FOREIGN SERVER remote_postgres_server TO some_user; GRANT SELECT ON remote_table TO some_user;

Updating User Mapping

Modify existing credentials:

ALTER USER MAPPING FOR current_user  
SERVER remote_postgres_server  
OPTIONS (SET password '<NEW_PASSWORD>');

ALTER USER MAPPING FOR current_user

SERVER remote_postgres_server

OPTIONS (SET password '<NEW_PASSWORD>');

Dropping Foreign Server or Table

To remove the foreign table:

DROP FOREIGN TABLE remote_table;

1	DROP FOREIGN TABLE remote_table;

To drop the foreign server:

DROP SERVER remote_postgres_server CASCADE;

1	DROP SERVER remote_postgres_server CASCADE;

Best Practices for Foreign Data Wrappers

Optimize Performance:

Use LIMIT when querying large datasets.
Avoid complex joins with foreign tables.
Consider materialized views for frequently accessed data.

Secure Connections:

Use SSL to encrypt connections between databases.
Store credentials securely (AWS Secrets Manager, IAM).

Monitor Query Performance:

Use pg_stat_activity to monitor FDW-related queries:

SELECT * FROM pg_stat_activity WHERE query LIKE '%foreign%';

1	SELECT * FROM pg_stat_activity WHERE query LIKE '%foreign%';

Use Connection Pooling

For frequent queries, consider PgBouncer to manage persistent connections.

Advantages of Using Foreign Servers in Amazon RDS PostgreSQL

Seamless Data Integration
- FDWs allow PostgreSQL to query remote databases as if they were local, reducing the need for complex ETL (Extract, Transform, Load) processes.
Real-Time Access to Remote Data
- Instead of duplicating data, you can fetch live records from remote databases, ensuring data consistency across multiple environments.
Reduced Storage Costs
- Since FDWs query external databases instead of storing duplicate data, they help save on Amazon RDS storage costs.
Cross-Region and Cross-Account Data Access
- Foreign servers enable secure data access between PostgreSQL instances across different AWS accounts or regions.

Conclusion

Setting up a foreign server in Amazon RDS PostgreSQL enables seamless data integration between databases. You can efficiently access and manage remote data by following the steps outlined enabling postgres_fdw, creating a foreign server, mapping users, defining foreign tables, and securing connections.

Drop a query if you have any questions regarding Amazon RDS PostgreSQL and we will get back to you quickly.

Empowering organizations to become ‘data driven’ enterprises with our Cloud experts.

Reduced infrastructure costs
Timely data-driven decisions

Get Started

About CloudThat

CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

CloudThat is the first Indian Company to win the prestigious Microsoft Partner 2024 Award and is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 650k+ professionals in 500+ cloud certifications and completed 300+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, Microsoft Gold Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, AWS GenAI Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner, AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, Amazon ECS Service Delivery Partner, AWS Glue Service Delivery Partner, Amazon Redshift Service Delivery Partner, AWS Control Tower Service Delivery Partner, AWS WAF Service Delivery Partner, Amazon CloudFront, Amazon OpenSearch, AWS DMS, AWS Systems Manager, Amazon RDS, and many more.

FAQs

1. Can I use a foreign server to connect an Amazon RDS PostgreSQL instance with an on-premise PostgreSQL database?

ANS: – Yes, you can use postgres_fdw to connect your RDS PostgreSQL instance with an on-premise PostgreSQL database. However, you need to:

Ensure network connectivity between your on-premise environment and RDS.
Configure VPC Peering, AWS VPN, or AWS Direct Connect for secure access.
Modify the security groups and inbound rules to allow connections from RDS to the on-premise database.

2. How can I improve the performance of queries using Foreign Data Wrappers (FDW)?

ANS: – To optimize FDW performance:

Use LIMIT when querying large remote tables to reduce data transfer.
Leverage EXPLAIN ANALYZE to check how queries are executed.
Enable query pushdown by filtering data before fetching it from the remote database.
Use Materialized Views to store frequently accessed remote data locally for faster performance.
Consider connection pooling with PgBouncer to manage multiple FDW connections efficiently.

WRITTEN BY Sunil H G

Sunil H G is a highly skilled and motivated Research Associate at CloudThat. He is an expert in working with popular data analysis and visualization libraries such as Pandas, Numpy, Matplotlib, and Seaborn. He has a strong background in data science and can effectively communicate complex data insights to both technical and non-technical audiences. Sunil's dedication to continuous learning, problem-solving skills, and passion for data-driven solutions make him a valuable asset to any team.