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;
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
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
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
IMPORT FOREIGN SCHEMA remote_schema_name EXCLUDE (table_name, table_name, ...) FROM SERVER server_name INTO destination_schema;
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
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.
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.
I’d like to point out that Multicorn already implements the IMPORT FOREIGN SCHEMA statement.
For more info about that, look at the example in this blog post:
Thank you for the info and the useful link!
Additional note: the Oracle and Informix FDW support IMPORT FOREIGN SCHEMA, too.
So, given Multicorn, Oracle and Informix FDWs support already IMPORT FOREIGN SCHEMA which FDWs can uniquely profit from this new PostgreSQL 9.5 feature?
Note: In this example the “foreign schema” seems to be imported from the local source into the remote destination, which is a PG instance. I would expect that the remote/external database is the foreign schema source and this is imported in to the non-remote/local database schema.
For my understanding: When I would like to update the definitions of the tables from the external datasource in my destination database, I have run the command
IMPORT FOREIGN SCHEMA …
again? There is no auto update mechanism or something like that implemented?