loader image

Foreign data wrappers

In the world of data management, there's a constant need to access and analyze data from multiple sources. But what if you could bring all of that data into one place, without the need for complex data pipelines or manual data transfers? That's exactly what Foreign Data Wrappers in PostgreSql enable you to do. With these powerful tools, you can seamlessly integrate data from external sources into your database, opening up a world of possibilities for data-driven insights and analysis.

A foreign data wrapper is a library that can communicate with an external data source, hiding the details of connecting to the data source and obtaining data from it.

Problem:

The highlighted attributes are from different database!

Solution:

  1. First solution was to transfer all logic tied to Flash Alerts on Case service, which is not that simple and secure.
  • Second solution was to implement features like dblink or Foreign datawrapper.

In the end of discussion we agreed to try with second solution.

FDW VS dblink

Funcionality:

Foreign Data Wrappers are a more powerful and flexible solution than dblink. FDWs allow you to define and use external data sources as if they were local tables. This means you can query, join, and manipulate data from external databases or file systems as if they were part of your PostgreSQL database. In contrast, dblink provides a more basic functionality forconnecting to and executing queries on remote databases.

Implementation:

FDWs are implemented as extensions to PostgreSQL and are integrated into the query planner, allowing for better query optimization and performance. DBlink, on the other hand, is implemented as a procedural language extension, meaning it can execute remote queries, but it cannot be integrated into the query planner.

A query planner is a component of a database management system (DBMS) that analyzes an SQL query and generates an execution plan to retrieve the requested data. In PostgreSQL, the query planner for FDWs is responsible for generating an execution plan for queries that involve FDWs. The planner takes into account the foreign table’s statistics and cost estimates, as well as any applicable pushdown clauses, to generate an optimal execution plan for the query.

Security:

FDWs provide a more secure option for accessing external data sources because they can be configured to use secure connections, such as SSL/TLS, for data transmission. DBlink, on the other hand, requires the user to pass credentialsto the remote database in plaintext, which can be a security risk.

Maintenance:

FDWs can be used to create virtual tables that represent external data sources, making it easier to manage and maintain data across different systems. In contrast, dblink requires more manual effort to manage remote connections and queries.

Overall, if you need to integrate external data sources into your PostgreSQLdatabase and require a more robust, flexible, and secure solution, then Foreign Data Wrappers are likely the better option.


Implementation FDW

EXTENSION

Foreign Data Wrapper (FDW) extension is an extension that allows you to access data stored outside the current database, as if it were local data. The FDW extension provides an API that allows developers to define a set of functions that communicate with external data sources. These functions can be used to create virtual tables that represent external data sources. Once a virtual table has been defined, it can be queried like any other table in the database, and the FDW extension takes care of retrieving the data from theexternal source and returning it to the user.

One advantage of using the FDW extension is that it allows you to access data from a wide variety of external sources, including other relational databases, NoSQL databases, file systems, and web services. This can be especially useful in situations where you need to combine data from multiple sources into a single query or report.

FOREIGN SERVER

In PostgreSQL, a foreign server refers to a remote database server that isaccessed using the Foreign Data Wrapper (FDW) functionality. The FDW allows PostgreSQL to connect to and query data sources external to the database server, such as another PostgreSQL server, a MySQL server, or even a file system.

To use a foreign server, you must create a foreign data wrapper that specifies the type of external data source and the connection details, and then create a foreign server that references the foreign data wrapper and defines the connection properties for the external data source.

In summary, a foreign server in PostgreSQL is a way to access and query data from a remote database server using the Foreign Data Wrapper feature.

USER MAPPING

In PostgreSQL, user mapping refers to the process of mapping a local user to a remote user on a foreign server that is accessed using the Foreign Data Wrapper (FDW) functionality.

When creating a foreign server, you can specify a user mapping that determines which user on the remote server is used when connecting to the foreign data source. This allows you to control the access privileges of the foreign data source for each user on the local PostgreSQL server.

The user mapping includes the remote user name and the authentication method used to connect to the foreign server. PostgreSQL supports severalauthentication methods, including password-based authentication, certificate-based authentication, and trust-based authentication.

The user mapping is defined using the CREATE USER MAPPING command,which includes the name of the local user, the name of the foreign server, and the remote user and authentication method.

FOREIGN SCHEMA

In PostgreSQL, the IMPORT FOREIGN SCHEMA command allows you toimport the schema of a foreign database server using the Foreign Data Wrapper (FDW) functionality. This command is useful when you want to access tables and views from a remote database without manually creatingcorresponding foreign tables or views in the local PostgreSQL database.

The IMPORT FOREIGN SCHEMA command requires you to specify thefollowing information:

  • The name of the foreign server to connect to.
  • The name of the schema to import from the foreign server.
  • The name of the local schema to create in the current database.