PostgreSQL 9.5: IMPORT FOREIGN SCHEMA

The release of PostgreSQL 9.5 is imminent so the time has come to analyse what’s new in this latest version.

A very interesting feature of version 9.5 is the ability to import a schema from a remote database, using Foreign Data Wrapper and the IMPORT FOREIGN SCHEMA command.

Foreign Data Wrappers (FDW)

Before the introduction of Foreign Data Wrappers, the only way to connect a Postgres database with an external data source was using the dblink module.

In 2003 the set of rules for the standard management of external data sources was defined within the SQL language: SQL/MED  (management of external Data).

PostgreSQL 9.1 introduced a first implementation of the standard SQL/MED with the Foreign Data Wrappers, which provided Postgres with direct access to data sources such as files or other databases (Oracle, Mysql…), allowing their use as tables.

The advantage of this approach is obvious: it gives you the ability to connect to an external data source to extract data natively by running a simple query. The fact that it is not necessary to use external modules to obtain this result considerably simplifies the DBAs’ work.

If you want to know more, take a look at our 2011 blog post: PostgreSQL 9.1: Tabelle esterne con SQL/MED (Warning: blog post in Italian).

Small example of an FDW

PostgreSQL 9.3 introduces the support for Foreign Data Wrappers in writing (Warning: blog post in Italian) and also added support to the foreign data wrapper for PostgreSQL. Let’s have a look at a simple example of how to use an FDW by connecting together two Postgres databases.

First we create two databases:

CREATE DATABASE source;
CREATE DATABASE destination;

Within the source we create a test table with test data:

\c source
CREATE TABLE test1 AS SELECT id, md5(random()::text) FROM generate_series(1,5) id;

Now we connect to the destination db and then, connect the two databases:

\c destination
CREATE EXTENSION postgres_fdw ;
CREATE SERVER src_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'source' );
CREATE USER MAPPING FOR postgres SERVER src_srv OPTIONS ( user 'postgres' );

Many of you, quite rightly will be up in arms, complaining about my poor choice in terms of security! Good!

For simplicity’s sake I decided to connect with the administrator user postgres — also to avoid deviating excessively from the main topic of this article. Be aware that, for security reasons, you will have to make other choices in a production environment (for example, using a specific user for your application).

However, once the connection is established, we can create on a target database an external table that points to test1 on the source database:

CREATE FOREIGN TABLE test1_ft (id integer, md5 text) server src_srv options(table_name 'test1');

We can now compare the content of the two test tables:

select * from test1_ft ;
 id |               md5
----+----------------------------------
  1 | 63e5bc545b45f5c3961522f2609bedd9
  2 | d74af95e495d946d4a0887c51eb2cbe2
  3 | acce7cba66967332d01d51b74eb293f7
  4 | c5bb57ca54036004de334cf793792d4e
  5 | 02f32751b09042cf28b78cc29321a32e
(5 rows)

\c source

select * from test1 ;
 id |               md5
----+----------------------------------
  1 | 63e5bc545b45f5c3961522f2609bedd9
  2 | d74af95e495d946d4a0887c51eb2cbe2
  3 | acce7cba66967332d01d51b74eb293f7
  4 | c5bb57ca54036004de334cf793792d4e
  5 | 02f32751b09042cf28b78cc29321a32e
(5 rows)

It is clear, looking at this example, that one of the greatest limitations to the use of Foreign Data Wrapper is the need to define each table separately, according to the appropriate structure. Access to external data is therefore laborious if you want to import more complex tables or even entire schemas.

Until now, such operations were performed through scripts that were able to connect to the source database and create the structure of the external tables automatically. Fortunately, the IMPORT FOREIGN SCHEMA function, present in the next release of PostgreSQL, will help us.

IMPORT FOREIGN SCHEMA: synopsis

The IMPORT FOREIGN SCHEMA instruction, allows importing of an entire schema from an external data source without having to specify the structure of each table:

IMPORT FOREIGN SCHEMA remote_schema_name
FROM SERVER server_name INTO destination_schema;

If it is not necessary to import an entire schema, it is possible to use the LIMIT TO clause and restrict imports only to the tables we are interested in:

IMPORT FOREIGN SCHEMA remote_schema_name LIMIT TO (table_name, table_name, ...)
FROM SERVER server_name INTO destination_schema;

Otherwise, if we only want to exclude certain tables from the schema, it is possible to filter them by the EXCLUDE clause:

IMPORT FOREIGN SCHEMA remote_schema_name EXCLUDE (table_name, table_name, ...)
FROM SERVER server_name INTO destination_schema;

Example

We can see in detail how to use this command by extending the example used previously. We connect to the source database and we add two tables to the one that already exists:

\c source
create table test2 as select id, md5(random()::text) from generate_series(1,20) as id;
create table test3 as select id, md5(random()::text) from generate_series(1,50) as id;

Now we create in the target database a schema that we will use as the target of the instruction IMPORT FOREIGN SCHEMA:

\c destination
create schema imported

Now we can import the schema we have just expanded, counting on the open connection in the previous example:

IMPORT FOREIGN SCHEMA public FROM SERVER src_srv INTO imported;

Let’s make a quick inspection of all the tables on the target database to observe the outcome of the schema import:

\dE *.*

               List of relations
  Schema  |   Name   |     Type      |  Owner
----------+----------+---------------+----------
 imported | test1    | foreign table | postgres
 imported | test2    | foreign table | postgres
 imported | test3    | foreign table | postgres
 public   | test1_ft | foreign table | postgres

Within the public schema we note the table that we created earlier, while the result of the “mass” import is visible in the imported schema. With this example it is possible to see how much faster and more efficient the use of external tables is by IMPORT FOREIGN SCHEMA.

Conclusions

With PostgreSQL 9.5, thanks to this new feature, data migrations will become increasingly simpler and quicker. Currently, the IMPORT FOREIGN SCHEMA instruction is only supported by postgres_fdw and requires that developers of individual drivers implement them in the manner most appropriate to the data source. Increasing the number of drivers that are able to support this feature, opens up interesting scenarios for PostgreSQL and data integration.