Foreign Data Wrappers in PostgreSQL: A Powerful Alternative to SQL Server Synonyms

When transitioning from SQL Server to PostgreSQL, one common challenge is handling cross-database queries efficiently. SQL Server provides Synonyms as a way to create aliases for tables in different databases, simplifying queries and avoiding hardcoded object references. However, PostgreSQL does not have direct synonyms—but it offers something even more powerful: Foreign Data Wrappers (FDWs).

In this post, we’ll explore how FDWs in PostgreSQL can be used as an alternative to SQL Server synonyms, enabling seamless access to external databases.

---

PostgreSQL offers Foreign Data Wrappers (FDWs), which allow users to query tables from different databases, even across different database engines (e.g., PostgreSQL, MySQL, Oracle, SQL Server).

I have two schemas, public and dbo in my postgres database. The public schema contains a table called public_test_table with some data inserted into it. Now, I want to access and use the data from the public schema of my postgres database in another database, say TEST.

Setting Up FDW in PostgreSQL

Let’s go step by step on how to set up an FDW to access another PostgreSQL database.

1. Install the Required Extension

First, enable the postgres_fdw extension, which is built into PostgreSQL in the TEST DB:

CREATE EXTENSION postgres_fdw;

2. Create a Foreign Server

Define the remote PostgreSQL server in the TEST DB:

--CREATE SERVER remote_server
CREATE SERVER public_foreign_server
FOREIGN DATA WRAPPER postgres_fdw
--OPTIONS (host 'remote_host', dbname 'remote_db', port '5432');
OPTIONS (host 'localhost', dbname 'postgres', port '5432');

3. Create a User Mapping

Map a local user to the remote database credentials:

--CREATE USER MAPPING FOR current_user
CREATE USER MAPPING FOR postgres
SERVER public_foreign_server
--OPTIONS (user 'remote_user', password 'remote_password');
OPTIONS (user 'postgres', password 'postgres');

4. Import or Manually Create Foreign Tables

You can either manually define the foreign table or import all tables from the remote database.

--IMPORT FOREIGN SCHEMA public
--FROM SERVER remote_server
--INTO local_schema;

IMPORT FOREIGN SCHEMA public
FROM SERVER public_foreign_server
INTO testschema;

5. Query the Remote Data

Now, you can query the remote table as if it were local:

SELECT * FROM testschema.public_test_table;

Benefits of Using FDWs

1. Cross-Database and Cross-Engine Support: FDWs work not only within PostgreSQL but also with MySQL, SQL Server, and other databases.

2. Real-Time Data Access: FDWs retrieve data directly from the other database, ensuring any changes are reflected in foreign tables in real time.

3. Security & Access Control: You can define granular access rules via user mappings.

4. Schema Flexibility: Easily import foreign tables dynamically.

5. Performance Optimization: PostgreSQL optimizes remote queries via push-down capabilities, reducing network overhead.

Conclusion

While SQL Server Synonyms provide a simple way to reference remote objects, Foreign Data Wrappers (FDWs) in PostgreSQL offer much greater flexibility and power. FDWs enable true cross-database queries, work across different database engines, and provide seamless integration for complex data architectures.

If you’re transitioning to PostgreSQL and need a synonym-like feature, FDWs are the best alternative—and they come with a lot more functionality to supercharge your database interactions!