What is FDW in PostgreSQL and How to Use it?

Recently, I faced a challenge: I needed to move the schema of a database from one PostgreSQL server to another. At first, I thought of using traditional methods like exporting and importing the schema manually, but then I discovered Postgres Foreign Data Wrapper (FDW). So Let me describe it to you.

FDW allows you to connect two PostgreSQL servers directly, fetch schemas, and even query data between them seamlessly. No more juggling files or running manual imports.

What is Postgres FDW?

FDW (Foreign Data Wrapper) is an extension that enables PostgreSQL to connect to other databases, treat them like local tables, and perform operations like querying or importing schemas.

With FDW, you can:

• Connect directly connect to a remote PostgreSQL server from another PostgreSQL server.
• Query remote tables in real-time, From the other PostgreSQL Server.
• Copy, Import and export data, and run DDL and DML queries.

How to Use FDW for Schema Import

This example is one of my recent experiences with FDW that helped me a lot in Copying the schema from the database in the source server to another database in the destination server.

So here I am sharing what was the steps to connect these servers using FDW:

1. Enable the FDW Extension:

The postgres_fdw extension is what enables PostgreSQL to connect and interact with another PostgreSQL server as if it’s local. Think of it as the bridge between your servers.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;        

2. Create a Server Connection:

This step defines the remote server you want to connect to. You specify the host, port, and database name. This tells PostgreSQL where to look.

CREATE SERVER old_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
  host 'ServerHost',
  port '5432',
  dbname 'Data'
);        

3. Map a User to the Remote Server:

A "user mapping" links a local user to a remote database user, providing credentials for secure access. Without this, the local server can’t authenticate to the remote server.

CREATE USER MAPPING FOR postgresadmindev
SERVER old_server
OPTIONS (
  user 'PosUser',
  password 'PosPassword'
);        

4. Create a Schema for Imported Tables:

You create a new schema locally where the foreign tables will be stored. This keeps the imported data organized and prevents mixing it with your existing data.

CREATE SCHEMA foreign_public;        

5. Import the Schema:

This command imports the structure of the remote schema (e.g., table names, columns, data types) into the specified local schema. You can customize what gets imported using options like import_default.

IMPORT FOREIGN SCHEMA public
FROM SERVER old_server
INTO foreign_public
OPTIONS (
  import_default 'false',
  import_collate 'false',
  import_not_null 'false'
);        

6. Query the Remote Tables: After importing, I could query the remote tables directly:

Once the schema is imported, you can query the remote tables directly as if they were part of your local database. Data retrieval is seamless and feels native.

SELECT * FROM foreign_public.table_name;        

Problems FDW Solves

Using FDW solves several issues that developers often face when working with PostgreSQL across multiple servers:

• No Manual File Handling: Forget about exporting and transferring files between servers.
• Real-Time Access: Query the remote server in real time for up-to-date data.
• Simplified Schema Import: Import schemas with a single command, no need to manually replicate the structure.
• Seamless Integration: Work with remote tables as if they are local, simplifying your queries and logic.
• Scalability: Useful in large systems where multiple databases interact.

Final Thoughts

If you’re managing multiple PostgreSQL servers or need to migrate schemas and data, FDW can save you a ton of time and effort. It turns a potentially tedious task into a smooth, efficient process. Enable FDW, set up your connection, and enjoy the simplicity of working across servers like a pro! ??