Foreign data wrappers

In 2011, PostgreSQL 9.1 was released with a read-only support for SQL / Management of External Data (MED) ISO/IEC 9075-9:2003 standard. SQL/MED defines foreign data wrappers (FDW) to allow the relational database to manage external data. Foreign data wrappers can be used to achieve data integration in a federated database system environment. PostgreSQL supports RDBMS, NoSQL, and file foreign data wrappers including Oracle, Redis, Mongodb, and delimited files.

A simple use case for FDW is to have one database server for analytical purposes, and then ship the result of this server to another server that works as a caching layer.

Also, FDW can be used to test data changes. Imagine you have two databases, one with different data due to applying a certain development patch. One could use FDW to assess the effect of this patch by comparing the data from the two databases.

PostgreSQL supports postgres_fdw starting from release 9.3. postgres_fdw is used to enable data sharing and access between different PostgreSQL databases. It supports the SELECT, INSERT, UPDATE, and DELETE operations on foreign tables.

The following example shows you how to read comma-separated value (CSV) files using FDW; one can read CSV files to parse logs. Let's assume that we would like to read the database logs generated by PostgreSQL. This is quite useful in production environment as one can have statistics about executed queries; the table structure can be found in the documentation at https://www.PostgreSQL.org/docs/current/static/runtime-config-logging.html. To enable CSV logging, one needs to change the following values in PostgreSQL.conf. For simplicity, all statements will be logged but this is not recommended in a production environment:

log_destination = 'csvlog'
logging_collector = on
log_filename = 'PostgreSQL.log'
log_statement = 'all'

For the changes to take effect, one needs to restart PostgreSQL:

$sudo service PostgreSQL restart 

To install the file Foreign data wrapper, we need to run the following command:

postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION

To access the file, we need to create FDW server as follows:

postgres=# CREATE SERVER fileserver FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER

Also, we need to create an FDW table and link it to the log file; in our case, it is located in the log folder in PostgreSQL cluster directory:

postgres=# CREATE FOREIGN TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text 
) SERVER fileserver OPTIONS ( filename '/var/lib/PostgreSQL/10/main/log/PostgreSQL.csv', header 'true', format 'csv' );
CREATE FOREIGN TABLE

To test our example, let us count how many log lines are logged:

postgres=# SELECT count(*) FROM postgres_log;
 count 
-------
    62
(1 row)