2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Giulio's PlanetPostgreSQL3 / PostgreSQL 9.5: IMPORT FOREIGN SCHEMA
Giulio Calacoci

PostgreSQL 9.5: IMPORT FOREIGN SCHEMA

May 19, 2015/6 Comments/in Giulio's PlanetPostgreSQL /by Giulio Calacoci

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.

import-foreign-schema

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.

Tags: 9.5, data integration, database, etl, FDW, foreign data wrapper, foreign tables, postgres, PostgreSQL, PostgreSQL 9.5, remote data source, schema import, SQL-MED
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
6 replies
  1. rdunklau
    rdunklau says:
    May 19, 2015 at 1:30 pm

    Hello,

    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:

    http://rdunklau.github.io/postgresql/multicorn/python/2015/04/05/multicorn_import_foreign_schema/

    Reply
    • Giulio Calacoci
      Giulio Calacoci says:
      May 19, 2015 at 3:16 pm

      Thank you for the info and the useful link!

      Reply
  2. Bernd Helmle
    Bernd Helmle says:
    May 20, 2015 at 12:35 am

    Additional note: the Oracle and Informix FDW support IMPORT FOREIGN SCHEMA, too.

    Reply
    • Giulio Calacoci
      Giulio Calacoci says:
      May 21, 2015 at 11:08 am

      Thank you!

      Reply
  3. Stefan
    Stefan says:
    May 23, 2015 at 6:53 pm

    So, given Multicorn, Oracle and Informix FDWs support already IMPORT FOREIGN SCHEMA which FDWs can uniquely profit from this new PostgreSQL 9.5 feature?

    -S.

    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.

    Reply
  4. Andy Schönemann
    Andy Schönemann says:
    August 7, 2015 at 8:12 am

    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?

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns The WITHIN GROUP and FILTER SQL clauses of PostgreSQL 9.4
Scroll to top
×