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 (FDWs) to allow the relational database to manage external data. FDW can be used to achieve data integration in a federated database-system environment. PostgreSQL supports RDBMS, NoSQL, and foreign data wrapper files, including Oracle, Redis, MongoDB, and delimited files.

A simple use case for FDWs 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; you can read CSV files to parse logs. Let's assume that we want to read the database logs generated by PostgreSQL. This is quite useful in a production environment as you 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, you need to change the following values in postgresql.conf. For simplicity, all statements will be logged, but this isn't recommended in a production environment since it'll consume a lot of server resources:

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

For the changes to take effect, you need to restart PostgreSQL from the Terminal as follows:

sudo service postgresql restart 

To install the FDW file, we need to run the following command:

postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION

To access the file, we need to create the 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's located in the log folder in the 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/11/main/log/postgresql.csv', header 'true', format 'csv' );
CREATE FOREIGN TABLE

To test our example, let's get one log line in JSON format, as follows:

postgres=# SELECT row_to_json(postgres_log, true) FROM postgres_log limit 1;
                              row_to_json 
------------------------------------------------------------------------
 {"log_time":"2018-12-10T00:35:19.768+01:00", +
  "user_name":null, +
  "database_name":null, +
  "process_id":25847, +
  "connection_from":null, +
  "session_id":"5c0da6b7.64f7", +
  "session_line_num":1, +
  "command_tag":null, +
  "session_start_time":"2018-12-10T00:35:19+01:00", +
  "virtual_transaction_id":null, +
  "transaction_id":0, +
  "error_severity":"LOG", +
  "sql_state_code":"00000", +
  "message":"database system was shut down at 2018-12-10 00:35:19 CET",+
  "detail":null, +
  "hint":null, +
  "internal_query":null, +
  "internal_query_pos":null, +
  "context":null, +
  "query":null, +
  "query_pos":null, +
  "location":null, +
  "application_name":""}
(1 row)

As we've seen, you can store the PostgreSQL logs in the PostgreSQL cluster. This allows the developer to search for certain user actions. Also, it allows the administrators to conduct statistical analysis on performance, such as finding the slowest queries to tune the PostgreSQL server configuration or rewriting slow queries.