PostgreSQL Foreign db, collegare MS SQL Server

Collegare a PostgreSQL le tabelle di un database Microsoft SQL Server? è possibile attraverso le funzioni native di PostgreSQL Foreign data table/server.

Va subito specificato che la funzione primaria di questo sistema è quello di poter dialogare con un altro database in sola lettura. Infatti sebbene potenzialmente sia possibile scrivere nel DB remoto è altamente sconsigliato farlo per varie ragioni. Non ultimo il fatto che i due sistemi, sebbene siano base dati relazionali, hanno delle differenze sostanziali.

Il vantaggio principale si riassume nella capacità di PostgreSQL di poter parlare con entità esterne e quindi prelevarne i dati. Ad esempio la migrazione da un vecchio sistema ad uno nuovo. Sono spesso operazioni che devono essere dilazionate nel tempo e da fare per step evolutivi. Ecco che un link diretto dal nuovo al vecchio sistema ci può permettere di avere comunque sotto mano tutti i dati necessari.

Come verifico le extensions

ls  /usr/share/postgresql/10/extension/ | grep tds_fdw.control

deve essere presente il file tds_fdw.control oppure in alternativa possiamo effettuare una query SQL sul database di default (postgres):

select * from pg_available_extensions;

Se non abbiamo avuto riscontro positivo dobbiamo installare qualche pacchetto.

Installazione

per prima cosa verificare che il server sia aggiornato, quindi installare freetds che è la libreria necessaria ad agganciarci a SQL Server.

sudo apt-get update
sudo apt-get install libsybdb5 freetds-dev freetds-common

quindi verificare che PostgreSQL sia installato, in particolare il pacchetto di sviluppo. L’esempio sottostante è per PostgreSQL versione 11, la versione va adattata.

sudo apt-get install postgresql-11 postgresql-client-11 postgresql-server-dev-11

A questo punto è necessario installare la extension del database, come citato in precedenza per SQL Server viene richiesto tds_fdw che però essendo una estensione non è disponibile come pacchetto debian. Pertanto dobbiamo clonare la repository di Github dove risiede il progetto. Di conseguenza è necessario avere installato “git” sul sistema operativo. Lascio comunque qui sotto tutti i comandi completi, nel caso “git” fosse già installato il sistema semplicemente ignora il comando.

sudo apt-get install git
git clone https://github.com/tds-fdw/tds_fdw.git
cd tds_fdw
make USE_PGXS=1
sudo make USE_PGXS=1 install
sudo service postgresql restart

Attenzione che l’ultima riga effettua un riavvio del database, quindi se avete progetti che richiedono zero downtime agite di conseguenza.

Collegarsi a psql

A questo punto possiamo collegarci alla command line di PostgreSQL. Per prima cosa cambiamo utente passando a quello del database, da li avviamo la command line con l’utente di default postgres.

su postgres
psql -U postgres

ora cambiamo database

\c nomedatabase
#You are now connected to database "nomedatabase" as user "postgres".

Qui c’è da fare una precisazione, nell’esempio che porto l’utente utilizzato è appunto postgres per semplicità di esposizione. Se il progetto a cui dovete collegare il Microsoft SQL Server utilizza un altro utente, va prima attivato poiché i comandi successivi si porteranno dietro i diritti dell’utenza. In questo caso il comando psql -U che ho dato sopra va richiamato l’user di vostro interesse.

Il passaggio successivo è quello di creare la extension. Faccio notare che “tds_fdw” è una parola riservata.

create extension tds_fdw;
#CREATE EXTENSION

Ora arriva il passo importante, per prima cosa andiamo a “creare” un foreign server, cioè un server estraneo a PostgreSQL, utilizzando la extension di cui sopra. Nella stessa istanza andiamo ad indicare anche nome del database remoto che andremo a consultare.

create server mssql foreign data wrapper tds_fdw options (servername '10.0.0.1', port '1433', database 'sqldb');
#CREATE SERVER

Se non ci sono stati errori possiamo indicare l’utente che il server remoto (SQL Server) dovrà utilizzare per effettuare il collegamento. Anche in questo caso per semplicità di esposizione uso l’utente base che è “sa”

create user mapping for postgres server mssql options (username 'sa', password 'xyz');
#CREATE USER MAPPING

Da notare che server utilizza il nome “breve” che ho dato nella fase di creazione del server (ovvero mssql). Il collegamento quindi è avvenuto, possiamo ora caricare le tabelle. Qui sono presenti due strade, una diciamo complicata, l’altra più semplice.

Quella complicata prevede di dichiarare le tabelle remote, solo che tale dichiarazione implica il fatto di dover indicare anche le colonne al suo interno, con tanto di referenza sul tipo, ad esempio intero, char, boleano. Ovviamente si tratta di una richiesta che normalmente non ha molto senso. Tuttavia in casi particolari dove ci sono difficoltà interpretative, in fondo MS SQL ha delle divergenze con PostgreSQL, possiamo usare questa tecnica.

Il secondo caso, più semplice, è quello di importare lo schema direttamente da MS SQL Server

import foreign schema dbo from server mssql into public;
#IMPORT FOREIGN SCHEMA

Generalmente dbo è lo schema di default in MS SQL Server, mentre public lo è per PostgreSQL.

L’import in questo caso prende tutto quello che trova e lo importa. Se vogliamo selezionare solo alcune tabelle possiamo anteporre il comando LIMIT TO, esempio:

import foreign schema dbo LIMIT TO (table1, table2) from server mssql into public;

Devo precisare che questa cosa non mi ha funzionato sempre, evidentemente dipende dalle versioni utilizzate e dagli schema

Come eliminare tutto quanto

A questo punto sappiamo come installare, ma se dobbiamo eliminare tutto quanto? Il comando che dobbiamo dare è solo uno, poiché in cascata andrà a ritroso a trovare tutte le referenze ed eliminarle.

drop server mssql cascade;
#NOTICE:  drop cascades to 7 other objects
#DETAIL:  drop cascades to user mapping for postgres on server mssql
#drop cascades to foreign table "MSreplication_options"
#drop cascades to foreign table spt_fallback_db
#drop cascades to foreign table spt_fallback_dev
#drop cascades to foreign table spt_fallback_usg
#drop cascades to foreign table spt_monitor
#drop cascades to foreign table spt_values
#DROP SERVER

Conclusioni

Abbiamo visto come alla fine sia possibile inserire senza troppe difficoltà un server decisamente antitetico al progetto open source e leggere i dati al suo interno. Chiaramente questo articolo è propedeutico al software che auspicabilmente gira intorno al progetto, per cui le query SQL possono essere facilmente estese anche all’altra base dati.