PostgreSQL 9.5: IMPORT FOREIGN SCHEMA

Potete consultare l'articolo originale su:
http://blog.2ndquadrant.it/postgresql-9-5-import-foreign-schema/

Il rilascio di PostgreSQL 9.5 è vicino ed è arrivato il momento di
analizzare le novità di questa nuova release.
Una funzionalità molto interessante della versione 9.5 sarà la possibilità
di importare uno schema da un database remoto, tramite l’utilizzo di
Foreign Data Wrapper e del comando IMPORT FOREIGN SCHEMA.

I Foreign Data Wrapper (FDW)

Prima dell’introduzione dei Foreign Data Wrapper l’unico modo per
connettere un database Postgres con una fonte dati esterna era il modulo
dblink.
Nel 2003 viene definito all’interno del linguaggio SQL l’insieme di regole
per la gestione in maniera standard di fonti di dati esterne: SQL/MED
(management of external Data).

All’interno di PostgreSQL 9.1, una prima implementazione dello standard
SQL/MED viene introdotta con i Foreign Data Wrapper[1], fornendo a Postgres
l’accesso diretto a fonti di dati come file o altri database (Oracle,
Mysql…), permettendone l’utilizzo come tabelle.
Il vantaggio di questo approccio è evidente: la possibilità di interrogare
una fonte di dati esterna per poterne estrarre dati in maniera nativa
eseguendo una semplice query. Il non dover ricorrere a moduli esterni per
ottenere questo risultato è una notevole semplificazione del lavoro per i
DBA.

Per saperne di più, potete dare una occhiata all’articolo pubblicato nel
2011, prima dell’uscita di PostgreSQL 9.1, sul nostro blog: “PostgreSQL
9.1: Tabelle esterne con SQL/MED“[2].

Piccolo esempio di uso di un FDW

PostgreSQL 9.3 introduce il supporto ai foreign data wrapper in
scrittura[3] ed aggiunge anche il supporto al foreign data wrapper per
PostgreSQL. Vediamo adesso un semplice esempio di utilizzo di un FDW
connettendo fra loro due database Postgres.
Creiamo due database:

CREATE DATABASE source;
CREATE DATABASE destination;

All’interno di source creiamo una tabella di test con dei dati di test:

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

Connettiamoci adesso al db di destinazione e connettiamo i due database:

\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' );

Qualcuno di voi, giustamente, si sarà alzato in piedi, lamentandosi per la
pessima scelta in fatto di sicurezza! Molto bene!

Per semplicità, ho infatti deciso di connetterci con l’utente
amministratore “postgres” – anche per non deviare troppo dall’argomento
principale dell’articolo. Sappiate che in un ambiente di produzione, per
motivi di sicurezza, dovrete prendere altre scelte – ad esempio utilizzando
un utente specifico per la vostra applicazione.

Ad ogni modo, una volta stabilita la connessione, possiamo creare sul
database di destinazione una tabella esterna che punti a test1 sul database
source:

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

Possiamo adesso confrontare il contenuto fra le due tabelle di test:

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)

È evidente, osservando questo esempio, che uno dei più grandi limiti
all’utilizzo dei Foreign Data Wrapper è la necessità di definire
separatamente, conoscendone la struttura, ogni tabella.
L’accesso a dati esterni quindi risulta laborioso qualora si voglia
importare tabelle più complesse o, addirittura, interi schemi.

Fino ad adesso, operazioni del genere venivano fatte per mezzo di script in
grado di connettersi al database sorgente e generare la struttura delle
tabelle esterne in maniera automatica.
Fortunatamente la funzionalità IMPORT FOREIGN SCHEMA, presente nella
prossima release di PostgreSQL, ci viene in aiuto.

IMPORT FOREIGN SCHEMA: sinossi

L’istruzione IMPORT FOREIGN SCHEMA[4], permette di importare uno schema
intero da una fonte dati esterna, senza dover specificare la struttura di
ogni singola tabella:

IMPORT FOREIGN SCHEMA remote_schema_name
FROM SERVER server_name INTO destination_schema;

Qualora non sia necessario importare uno schema intero, è possibile usare
la clausola LIMIT TO e circoscrivere l’importazione unicamente alle tabelle
a cui siamo interessati:

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

Altrimenti, se siamo interessati a escludere solo alcune tabelle dallo
schema, è possibile filtrarle con la clausola EXCLUDE:

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

Esempio

Vediamo nel dettaglio come utilizzare questo comando, andando a estendere
l’esempio usato in precedenza.
Connettiamoci al database sorgente e aggiungiamo due tabelle a quella che
già è presente:

\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;

Creiamo adesso nel database di destinazione uno schema che useremo come
target dell’istruzione IMPORT FOREIGN SCHEMA:

\c destination
create schema imported;

Adesso possiamo importare lo schema che abbiamo appena ampliato, contando
sulla connessione aperta nell’esempio precedente:

IMPORT FOREIGN SCHEMA public FROM SERVER src_srv INTO imported;

Facciamo una rapida ispezione di tutte le tabelle sul database di
destinazione per osservare il risultato dell’importazione dello schema:

\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

All’interno dello schema public notiamo la tabella che abbiamo creato in
precedenza, mentre il risultato dell’importazione “in massa” è visibile
nello schema imported.

Con questo esempio è possibile constatare quanto è più veloce e immediato
l’utilizzo delle tabelle esterne con IMPORT FOREIGN SCHEMA.

Conclusioni

Con PostgreSQL 9.5, grazie a questa nuova funzionalità, le migrazioni dei
dati diventeranno sempre più semplici e veloci.
Attualmente l’istruzione IMPORT FOREIGN SCHEMA è supportata solo da
postgres_fdw e richiede che gli sviluppatori dei singoli driver la
implementino nel modo più consono alla fonte di dati.
Aumentando il numero di driver in grado di supportare questa funzionalità
si aprono scenari sempre più interessanti per PostgreSQL e per
l’integrazione dei dati.

Riferimenti:

1.
http://www.postgresql.org/docs/9.4/static/sql-createforeigndatawrapper.html
2. http://blog.2ndquadrant.it/postgresql_91_tabelle_esterne/
3. http://blog.2ndquadrant.it/esce-postgresql-9-3/
4. http://www.postgresql.org/docs/devel/static/sql-importforeignschema.html

--
Giulio Calacoci - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
giulio(dot)calacoci(at)2ndQuadrant(dot)it | www.2ndQuadrant.it