Foreign data wrappers concept in PostgreSQL - Mini Project: Build & Apply

Using Foreign Data Wrappers in PostgreSQL

📖 Scenario: You work in a company that uses PostgreSQL as its main database. You want to access data from another PostgreSQL database without copying the data. Foreign Data Wrappers (FDW) let you do this by connecting to the other database and querying its tables as if they were local.

🎯 Goal: Learn how to set up a Foreign Data Wrapper in PostgreSQL to access a table from a remote database.

📋 What You'll Learn

Create a foreign server connection to a remote PostgreSQL database

Create a user mapping for authentication

Create a foreign table that links to a remote table

Query the foreign table to access remote data

💡 Why This Matters

🌍 Real World

Companies often need to access data stored in different databases without copying it. Foreign Data Wrappers let you query remote data as if it were local, saving time and storage.

💼 Career

Database administrators and backend developers use FDWs to integrate multiple data sources, enabling efficient data analysis and reporting.

1

Create the Foreign Server

Write a SQL command to create a foreign server called remotedb that connects to a PostgreSQL database on host '192.168.1.100' at port 5432 with database name 'sales'. Use the postgres_fdw foreign data wrapper.

PostgreSQL

-- Your code here to create the foreign server

Need a hint?

Use CREATE SERVER with FOREIGN DATA WRAPPER postgres_fdw and specify host, port, and dbname in OPTIONS.

2

Create User Mapping for Authentication

Write a SQL command to create a user mapping for the current user to connect to the remotedb server. Use username 'remote_user' and password 'secret123'.

PostgreSQL

CREATE SERVER remotedb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.100', port '5432', dbname 'sales');
-- Your code here to create user mapping

Need a hint?

Use CREATE USER MAPPING FOR CURRENT_USER SERVER remotedb and specify user and password in OPTIONS.

3

Create the Foreign Table

Write a SQL command to create a foreign table called remote_customers that links to the customers table on the remotedb server. The table has columns id (integer) and name (text).

PostgreSQL

CREATE SERVER remotedb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.100', port '5432', dbname 'sales');
CREATE USER MAPPING FOR CURRENT_USER SERVER remotedb OPTIONS (user 'remote_user', password 'secret123');
-- Your code here to create foreign table

Need a hint?

Use CREATE FOREIGN TABLE with columns and link it to SERVER remotedb. Specify schema_name and table_name in OPTIONS.

4

Query the Foreign Table

Write a SQL query to select all columns from the foreign table remote_customers.

PostgreSQL

CREATE SERVER remotedb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.100', port '5432', dbname 'sales');
CREATE USER MAPPING FOR CURRENT_USER SERVER remotedb OPTIONS (user 'remote_user', password 'secret123');
CREATE FOREIGN TABLE remote_customers (id integer, name text) SERVER remotedb OPTIONS (schema_name 'public', table_name 'customers');
-- Your code here to query the foreign table

Need a hint?

Use a simple SELECT * FROM remote_customers; to get all data from the foreign table.