Postgres Foreign Data Wrappers (FDW) Extension

In today’s data-driven world, access to data from different sources is essential. PostgreSQL’s Foreign Data Wrapper(FDW), or postgres fdw, allows you to query external data sources as if they were local tables. In this blog post, we’ll discuss the details of Postgres Foreign Data Wrappers, its benefits, and how to use it. We can also use fdw extension in Amazon AWS RDS as well postgres database hosted in differnt server.

You will understand below topics in this article:
What is Foreign Data Wrappers
Advantages of using Foreign Data Wrappers
Implementation of Foreign Data Wrappers
Best practices for using Foreign Data Wrappers
Postgres Foreign Data Wrapper Extension Example

What is postgres_fdw?

postgres foreign data wrapper is a powerful PostgreSQL extension that allows you to access and manipulate data stored in external databases as if it were native to your local database. This opens up a world of possibilities for data integration and analysis, enabling you to combine data from different sources into a single view for easy querying and reporting.

Advantages of using Foreign Data Wrappers (FDW)

• Seamless data integration: FDW allows you to query and manipulate data from external databases as if it were native to your local PostgreSQL database. This eliminates the need for complex data replication processes and simplifies data integration and analysis.
• Real-time data access: postgres foreign data wrapper can access data from remote sources in real time, without the need for frequent data syncing. This is especially valuable in scenarios where up-to-date information is crucial, such as fraud detection and customer relationship management (CRM).
• Cost-effective data management: By avoiding the need to duplicate and store data locally, postgres foreign data wrapper can significantly reduce storage and maintenance costs. You can maintain a leaner, more efficient database architecture.

Implementing Postgres Foreign Data Wrappers

Implementing Foreign Data Wrapper is a relatively straightforward process. The following steps provide a high-level overview:

1. Install the Postgres Foreign Data Wrappers Extension: This can be done using the CREATE EXTENSION command.
2. Create a foreign server: This involves defining the location and connection details for the remote database.
3. Create a Foreign User: You need to create a user (remote_user) in the foreign database. You need to grant this user read-only access to the table in question, and usage on the schema where our table lives.
4. Create user mappings: User mappings establish the link between a local user in your PostgreSQL database and a user in the foreign server. This ensures that the correct permissions are in place.
5. Create foreign tables. Foreign tables represent the remote data in your local PostgreSQL database. You can query and manipulate foreign tables just like regular local tables.

Best practices for using Postgres Foreign Data Wrappers (FDW)

• Optimize queries. When working with remote data, it’s important to optimize your queries to minimize data transfer and maximize performance. Consider using joins and filters effectively.
• Monitor connection status. Regularly monitor the status of your foreign server connections. This ensures that any disruptions in connectivity are addressed promptly.

Extension Postgres Foreign Data Wrappers Example

Now you understand what is postgres foreign data wrapper. I am going to show you complete postgre sql Foreign Data Wrappers example as given below:

--1. drop extension postgresql if exists
DROP EXTENSION postgres_fdw;

--2. Create Extension Postgres Foreign Data Wrapper
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

---If you forgot to create extension, make sure to create it
----foreign data wrapper does not exist

--3. Create a Foreign Server
CREATE SERVER PGS_FDW_SRV
FOREIGN DATA WRAPPER postgres_fdw
--postgres_fdw options
OPTIONS (dbname 'fdw_test', host 'ec2-xx-xxx-171-52.eu-west-1.compute.amazonaws.com', port '5432')

--4. Create a User Mapping
CREATE USER MAPPING FOR postgres
SERVER PGS_FDW_SRV
OPTIONS (user 'remote_user', password 'Remote123');

--5. Create Foreign Table for postgre Foreign Data Wrapper
CREATE FOREIGN TABLE test_remote_table (
    id integer,
    name text
) SERVER PGS_FDW_SRV 
  OPTIONS (table_name 'remote_table');

--6. Grant usgae of  Remote SERVER to the user
GRANT USAGE ON FOREIGN SERVER PGS_FDW_SRV TO postgres;

--7. Grant access of remote table to the remote user
GRANT SELECT, INSERT, UPDATE ON remote_table to remote_user;
--Make sure grant executed after login into the remote database

--8. Insert records into Remote table from local daatabse
INSERT INTO test_remote_table2(id, name) 
VALUES (1, 'Test insert from remote');

--9. Access the table from remote database
---postgres Foreign Data Wrapper select example
select * from test_remote_table;

Postgres FDW vs. Parquet FDW: Two Data Management Tools with Different Strengths

Postgres foreign data wrapper and parquet foreign data wrapper are two distinct data management tools, each with its own strengths. fdw Postgres, or Foreign Data Wrapper, enables seamless access and interaction with data stored in external databases, providing real-time data querying and manipulation across different systems. In contrast, parquet_fdw focuses on the Parquet file format, which is optimized for storing and querying large datasets efficiently. Therefore, while Postgres one facilitates database-to-database interactions, parquet data wrapper specializes in file-based data operations.

Similarly you can access parquet file as a source data from remote server and access from postgres as foreign table using parquet fdw extension. I describe in detail with complete example in my earlier blog.

Conclusion:

Postgres Foreign Data Wrappers is a powerful tool that can help you achieve seamless data integration and real-time data access. With its ease of use and cost-effectiveness, postgres_fdw is an invaluable asset for organizations of all sizes.