Linki bazodanowe w PostgreSQL - jak odpytac tabele w Oracle i MS SQL Server (dblink, postgres_fdw)

Czasem trzeba polaczyc dane z kilku roznych baz w jednym zapytaniu - PostgreSQL daje do tego dwa mechanizmy, dblink i postgres_fdw. Pokazujemy roznice miedzy nimi i jak podlaczyc nimi Oracle oraz MS SQL Server.

Linki bazodanowe pozwalają łączyć się między bazami danych, odnosić się do obiektów znajdujących się w innych bazach, a także wykorzystywać obiekty pochodzące z różnych baz w jednym zapytaniu.
W tym artykule omówimy nie tylko łączenie się do innych baz PostgreSQL, ale także do baz Oracle i Microsoft SQL Server.

Z tego artykułu dowiesz się:

• co to są linki bazodanowe i w jakim celu się je stosuje,- jak zainstalować i skonfigurować FDW,- w jaki sposób odpytywać wiele baz danych PostgreSQL w jednym zapytaniu,- w jaki sposób z poziomu PostgreSQL odpytywać i modyfikować zawartość tabel na serwerze MS SQL Server,- w jaki sposób z poziomu PostgreSQL odpytywać i modyfikować zawartość tabel na serwerze Oracle

Postgres - posiada wiele bardzo użytecznych funkcjonalności, których nie mają inne systemy bazodanowe. Jedną z nich są Foreign Data Wrappery, w skrócie FDW. Jest to biblioteka, dzięki której Postgres może odczytywać, a w niektórych przypadkach również zmieniać, dane z zewnętrznych źródeł, jakimi są na przykład inne bazy, systemy bazodanowe, zarówno SQL, takie jak Oracle, MSSQL, MySQL, DB2, jak i NoSQL, np. - MongoDB, Redis, Cassandra, oraz źródeł takich jak pliki CSV, XML, serwery LDAP, IMAP czy też odczytywanie postów bezpośrednio z portali internetowych, takich jak Twitter, Facebook i wielu innych.

Pierwsze Foreign Data Wrappery zaczęły się pojawiać w roku 2003, kiedy SQL/MED (SQL Management of External Data) został dodany do standardu SQL, a w Postgresie od wersji 9.1 w roku 2011. SQL/MED to zestaw definicji opisujących zasady dostępu do “zewnętrznych danych”, czyli do danych, do których system bazodanowy ma dostęp, ale którymi nie zarządza. Istnieją dwa sposoby dostępu do takich danych. Są to tabele obce, z których korzystają FDW, czyli sposób na dostęp oraz prezentację danych zewnętrznych w formie tabeli relacyjnej. Drugi sposób to datalinki, czyli metoda pozwalająca systemowi bazodanowemu kontrolować pliki spoza instancji i korzystać z nich bezpośrednio w bazie danych, na przykład kolumna tabeli może odwoływać się bezpośrednio do pliku.

W domyślnej konfiguracji Postgres nie pozwala również na zapytania pomiędzy bazami w jednym klastrze (instancji). Możemy jednak skorzystać z wbudowanej funkcjonalności linków bazodanowych (dblink), która pozwala na wykonywanie zapytań w innych bazach Postgresa, zarówno na tym samym serwerze i klastrze jak i zdalnie lub z FDW, które są zdecydowanie popularniejszą oraz bardziej wydajną i funkcjonalną opcją.

W tym artykule przyjrzymy się rozszerzeniu tds_fdw (Tabular Data Stream - Foreign Data Wrapper) umożliwiającemu odczytywanie danych przechowywanych w Microsoft SQL serwerze na serwerze Postgresa, oracle_fdw oraz postgres_fdw, które pozwalają na odczyt i zmianę danych dla baz Oracle i Postgres.

TDS_FDW - instalacja i konfiguracja na przykładzie Ubuntu

Aby skonfigurować Foreign Data Wrapper dla instancji MS SQL, musimy ją najpierw zainstalować i skonfigurować. Tutaj na przykładzie Ubuntu 20.04 LTS.

# pobieramy klucz repozytorium Microsoftu

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

# dodajemy do apt repozytorium zawierające mssql 

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list)"

# instalujemy paczki mssql

sudo apt-get update && sudo apt-get install -y mssql-server

# wybieramy edycję “express (free)” i ustawiamy hasło administratora (sa)

sudo /opt/mssql/bin/mssql-conf setup

# mssql powinien automatycznie wystartować, sprawdzam status usługi

root@vagrant:/home/vagrant# systemctl status mssql-server

● mssql-server.service - Microsoft SQL Server Database Engine

     Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)

     Active: active (running) since Fri 2023-06-02 22:27:03 UTC; 19s ago

o wstępnej konfiguracji możemy przejść do stworzenia nowej bazy, schematu i tabeli z danymi, które za chwilę będziemy odczytywać z Postgresa. Możemy też dodać użytkownika, którego wykorzystamy do połączenia się z Postgresa do MSSQL.

sqlcmd -S localhost -U sa 

CREATE DATABASE hurtownia;

SELECT Name from sys.databases;

go

USE hurtownia;

go

CREATE SCHEMA produkcja;

go

CREATE TABLE produkcja.stan_magazynowy (id int IDENTITY(1,1) PRIMARY KEY, 

produkt NVARCHAR(50), ilosc INT);

INSERT INTO produkcja.stan_magazynowy (produkt,ilosc) VALUES ('gwozdzie 4x100',100);

INSERT INTO produkcja.stan_magazynowy (produkt,ilosc) VALUES ('mlotek 300g',2);

INSERT INTO produkcja.stan_magazynowy (produkt,ilosc) VALUES ('wkrety 3,5x45',300);

INSERT INTO produkcja.stan_magazynowy (produkt,ilosc) VALUES ('deska 200x10x2',30);

go

CREATE LOGIN postgres_fdw WITH PASSWORD = 'zaq1@WSXcde3';

CREATE USER postgres_fdw FOR LOGIN postgres_fdw;

GRANT SELECT ON OBJECT::produkcja.stan_magazynowy to postgres_fdw;

go

Jeżeli mamy już zainstalowanego Postgresa w wersji 15 z oficjalnego repozytorium PGDG (Postgresql Development Group), możemy przejść do instalacji paczek wymaganych do kompilacji i działania rozszerzenia. Jeżeli Postgresa jeszcze nie mamy, zainstalujemy go wg oficjalnych instrukcji https://www.postgresql.org/download/linux/ubuntu/.

Rozszerzenie TDS_fdw przed kompilacją wymaga instalacji paczki development dla Postgresa, bibliotek FreeTDS oraz dla pewności kompilatora C.

sudo apt-get update

sudo apt-get install postgresql-server-dev-15 libsybdb5 freetds-dev freetds-common \

gnupg gcc make -y

Kod źródłowy pobierzemy z oficjalnej strony projektu na github.com https://github.com/tds-fdw/tds_fdw

Przed kompilacją sprawdźmy najnowszą dostępną wersję rozszerzenia https://github.com/tds-fdw/tds_fdw/releases.

Po instalacji wymaganych pakietów możemy pobrać paczkę z kodem źródłowym z GitHuba, rozpakować i skompilować.

export TDS_FDW_VERSION="2.0.3"

sudo apt-get install wget

wget https://github.com/tds-fdw/tds_fdw/archive/v${TDS_FDW_VERSION}.tar.gz

tar -xvzf v${TDS_FDW_VERSION}.tar.gz

cd tds_fdw-${TDS_FDW_VERSION}/

make USE_PGXS=1

sudo make USE_PGXS=1 install

Zweryfikujemy, - czy rozszerzenie zostało poprawnie zainstalowane i stworzymy je w bazie Postgres.

postgres=# select * from pg_available_extensions where name = 'tds_fdw';

  name - | default_version | installed_version |                                                comment                                                

---------+-----------------+-------------------+-----------------------------------------------------------------------------------

 tds_fdw | 2.0.3             | 2.0.3                | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

postgres=# create extension tds_fdw;

CREATE EXTENSION

Po dodaniu rozszerzenia możemy stworzyć “serwer”, czyli definicję zdalnego serwera, z którego będziemy odczytywać dane z tabel. Podajemy dla niego adres serwera, port, na którym działa SQL Server, domyślnie 1433, baza, do której się połączymy oraz wersja biblioteki TDS (https://www.freetds.org/userguide/ChoosingTdsProtocol.html).

Następnie ustawiamy domyślnego użytkownika, którym będziemy się autoryzować w bazie MSSQL. Użytkownik ten musi być wcześniej stworzony i mieć uprawnienia do tabel, które będzie odczytywał. Definicję mapowania użytkownika możemy utworzyć globalnie, dla wszystkich użytkowników klastra, indywidualnie lub dla grupy. W miejscu “username” i “password” podajemy nazwę użytkownika i hasło, które wcześniej stworzyliśmy w MSSQL.

Na końcu możemy stworzyć definicję tabeli obcej, gdzie definiujemy jej strukturę oraz wskazujemy jej nazwę na serwerze źródłowym. Tworząc tabelę obcą, możemy zrobić dokładną kopię. Możemy również wybrać tylko część kolumn, zmienić ich kolejność lub nazwę (nazwa_produktu VARCHAR OPTIONS (column_name 'produkt'). Należy jednak pamiętać, aby użyć kompatybilnych, a najlepiej tych samych, typów danych, które są używane przez tabelę źródłową.

CREATE SERVER mssql_hurtownia FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'X.X.X.X', port '1433', database 'hurtownia', tds_version '7.4');

CREATE USER MAPPING FOR public SERVER mssql_hurtownia OPTIONS (username 'postgres_fdw', password 'zaq1@WSXcde3');

CREATE FOREIGN TABLE mssql_produkcja_stan_magazynowy (id INT,nazwa_produktu VARCHAR OPTIONS (column_name 'produkt'), ilosc INT) SERVER mssql_hurtownia OPTIONS (table_name 'produkcja.stan_magazynowy');

postgres=# select * from mssql_produkcja_stan_magazynowy;

NOTICE: - tds_fdw: Query executed correctly

NOTICE: - tds_fdw: Getting results

 id |     produkt      | ilosc 

----+----------------+-------

  1 | gwozdzie        | - 100

  2 | mlotek 300g     |      2

  3 | wkrety 3,5x45 - | - 300

  4 | deska 200x10x2 |     30

(4 rows)

Dla sprawdzenia dodajemy kolejny wiersz do tabeli w MSSQL.

INSERT INTO produkcja.stan_magazynowy (produkt,ilosc) VALUES ('jajko',110);

Weryfikujemy, czy nowy wiersz jest widoczny, odpytując tabelę obcą w Postgresie.

postgres=# select * from mssql_produkcja_stan_magazynowy;

NOTICE: - tds_fdw: Query executed correctly

NOTICE: - tds_fdw: Getting results

 id |     produkt      | ilosc 

----+----------------+-------

  1 | gwozdzie        | - 100 

  2 | mlotek 300g     |      2 

  3 | wkrety 3,5x45 - | - 300 

  4 | deska 200x10x2 |     30 

  5 | jajko             | - 110 

(5 rows)

Gdy korzystamy z tds_fdw, zmiana danych możliwa jest tylko bezpośrednio w instancji źródłowej. Tabele obce pozwalają jedynie na odczytywanie tabel. Próba dodania nowego wiersza zakończy się błędem:

postgres=# insert into mssql_produkcja_stan_magazynowy(produkt,ilosc) values ('test',2);

ERROR: - cannot insert into foreign table "mssql_produkcja_stan_magazynowy"

Konfiguracja FDW dla PostgreSQL

Rozszerzenie umożliwiające połączenie z innym klastrem Postgresa dostępne jest domyślnie. Można je zainstalować w dowolnym momencie bez potrzeby instalowania - innych dodatkowych pakietów.

postgres=# select * from pg_available_extensions where name = 'postgres_fdw';

     name      | default_version | installed_version |                            comment                             

--------------+-----------------+-------------------+----------------------------------------------------

 postgres_fdw | 1.1                |                       | foreign-data wrapper for remote PostgreSQL servers

(1 row)

postgres=# create extension postgres_fdw;

CREATE EXTENSION

Na drugim serwerze z Postgresem (klaster źródłowy) pobieramy i importujemy przykładowego dumpa oraz tworzymy użytkownika, którego wykorzystamy do połączenia i dostępu do danych.

# pobranie dumpa

wget https://jsystems.pl/nowy_blog/download/postgresql/tuning.zip

# import z powiększonym maintenance_work_mem dla przyspieszenia tworzenia indeksów

PGOPTIONS="-c maintenance_work_mem=512MB" psql '<' tuning.sql

# stworzenie użytkownika i nadanie uprawnień

psql -c "create user pgfdw with password 'asd123'"

CREATE ROLE

psql -c "grant all on all tables in schema public to pgfdw"

GRANT

# wpis do pg_hba.conf

/etc/postgresql/15/main/pg_hba.conf

host     postgres               pgfdw             X.X.X.X/32                  scram-sha-256

Na serwerze docelowym tworzymy definicję serwera. Następnie ustawiamy mapowanie użytkowników oraz tworzymy tabelę obcą. Tworząc tabelę obcą korzystającą z rozszerzenia postgres_fdw, domyślnie udzielamy pozwolenia na modyfikowanie danych, INSERT/UPDATE/DELETE (OPTIONS updatable=true) oraz truncate tabeli (OPTIONS truncatable=true). Jeżeli tabela posiada klucz główny - Primary Key - musimy go wskazać za pomocą OPTIONS(key 'true').

CREATE SERVER postgres_tuning FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'X.X.X.X', dbname 'postgres', port '5432');

CREATE SERVER

CREATE USER MAPPING FOR public SERVER postgres_tuning OPTIONS (user 'pgfdw', password 'asd123');

CREATE USER MAPPING

CREATE FOREIGN TABLE tuning_transactions (transaction_id int, operation_value numeric, operation_year int, operation_month int, operation_day int, operation_description text, company_id int) SERVER postgres_tuning;

CREATE FOREIGN TABLE

Wykonamy teraz test odczytu i zmiany danych w tabeli obcej.

postgres=# select * from tuning_transactions where transaction_id = 1;

 transaction_id | operation_value | operation_year | operation_month | operation_day | operation_description | company_id 

----------------+-----------------+----------------+-----------------+---------------+-----------------------+------------

              1 |              9332 |             2012 |                  2 |                3 | operacja numer 1        |     1364646

(1 row)

postgres=# update tuning_transactions set operation_month = 4 where transaction_id = 1;

UPDATE 1

postgres=# select * from tuning_transactions where transaction_id = 1;

 transaction_id | operation_value | operation_year | operation_month | operation_day | operation_description | company_id 

----------------+-----------------+----------------+-----------------+---------------+-----------------------+------------

              1 |              9332 |             2012 |                  4 |                3 | operacja numer 1        |     1364646

(1 row)

W przypadku postgres_fdw, jak widać na powyższym przykładzie, możemy zarówno odczytywać jak i edytować dane.

Instalacja i konfiguracja Oracle

Ostatnim rozszerzeniem, które przeanalizujemy, jest oracle_fdw, umożliwiające odczytywanie oraz zmianę danych przechowywanych w systemach bazodanowych Oracle. Z jednym zastrzeżeniem, że polecenia UPDATE oraz DELETE działają tylko dla tabel posiadających klucz główny, który definiujemy poprzez “OPTIONS(key 'true')” dla odpowiedniej kolumny.

Zaczniemy od instalacji oraz przygotowania przykładowych obiektów w Oracle.

# instalacja Oracle 21c

sudo yum install wget -y

wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm

wget https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm

sudo yum -y localinstall oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm

sudo yum -y localinstall oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm

sudo /etc/init.d/oracle-xe-21c configure

# utworzenie nowej bazy kontenerowej, użytkownika scott z uprawnieniami

[oracle@localhost bin]$ . oraenv

ORACLE_SID = [oracle] ? XE

The Oracle base has been set to /opt/oracle

[oracle@localhost bin]$ sqlplus / as sysdba

CREATE PLUGGABLE DATABASE postgres admin user scott identified by tiger file_name_convert = ('/pdbseed/', '/postgres/');

alter pluggable database postgres open;

grant connect to scott;

grant unlimited tablespace to scott;

grant create view to scott;

grant create table to scott;

grant create trigger to scott;

exit

# utworzenie replikowanej tabeli z przykładowymi danymi

sqlplus scott@x.x.x.x/postgres  # x.x.x.x adres serwera z Oraclem

CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14) , LOC VARCHAR2(13) ) ;

grant all on SCOTT.DEPT to scott;

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); 

INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

Na serwerze Ubuntu z PostgreSQL instalujemy Postgresa z bibliotekami “devel”, ponownie według oficjalnych instrukcji https://www.postgresql.org/download/linux/ubuntu/. Instalujemy też klienta Oracle oraz kompilujemy ręcznie rozszerzenie oracle_fdw.

# Instalacja bibliotek devel dla PostgreSQL, pakietu zip i kompilatora gcc

sudo apt install -y postgresql-server-dev-15 zip gcc make

# Instalacja Oracle clienta

sudo mkdir /opt/oracle && cd $_

sudo wget https://download.oracle.com/otn_software/linux/instantclient/2110000/instantclient-sdk-linux.x64-21.10.0.0.0dbru.zip

sudo wget https://download.oracle.com/otn_software/linux/instantclient/2110000/instantclient-sqlplus-linux.x64-21.10.0.0.0dbru.zip

sudo wget https://download.oracle.com/otn_software/linux/instantclient/2110000/instantclient-basic-linux.x64-21.10.0.0.0dbru.zip

sudo unzip instantclient-basic-linux.x64-21.10.0.0.0dbru.zip

sudo unzip instantclient-sdk-linux.x64-21.10.0.0.0dbru.zip

sudo unzip instantclient-sqlplus-linux.x64-21.10.0.0.0dbru.zip

sudo cp /opt/oracle/instantclient_21_10/*.so* /lib

# Kompilacja rozszerzenia

cd

git clone https://github.com/laurenz/oracle_fdw.git

cd oracle_fdw

export ORACLE_HOME=/opt/oracle/instantclient_21_10/

make

sudo make install

# weryfikacja

sudo su - postgres

psql

postgres=# select name,comment from pg_available_extensions where name = 'oracle_fdw';

    name     |                    comment                     

------------+----------------------------------------

 oracle_fdw | foreign data wrapper for Oracle access

(1 row)

postgres=# create extension oracle_fdw;

CREATE EXTENSION

Po przygotowaniu środowiska możemy przejść do definiowania serwera, użytkownika i tabeli obcej, podobnie jak w przypadku PostgreSQL i MSSQL. Przy tworzeniu serwera podajemy nazwę utworzonej wcześniej “pluggable database”, zamiast nazwy instancji, aby podłączyć się bezpośrednio do bazy docelowej. Tworząc tabelę obcą oracle_dept, dla kolumny DEPTNO dodajemy OPTIONS(key 'true'), aby poinformować Postgresa, że kolumna ta jest dla tabeli DEPT w Oraclu kluczem głównym. Umożliwi to później zdalną edycję danych w Oraclu z Postgresa.

# definicja serwera, tabeli i użytkownika, x.x.x.x w “create server” to adres serwera Oracle

create server oracle foreign data wrapper oracle_fdw options (dbserver '//x.x.x.x/postgres');

create user mapping for public server oracle options (user 'SCOTT', password 'tiger');

create foreign table oracle_dept(DEPTNO numeric OPTIONS(key 'true') NOT NULL, DNAME varchar(14), LOC varchar(13)) server oracle options (schema 'SCOTT', table 'DEPT');

# odczyt

sco

 deptno | - dname     | - loc     

--------+------------+----------

     10 | ACCOUNTING | NEW YORK

     20 | RESEARCH - | DALLAS

     30 | SALES        | CHICAGO

# dodanie wiersza

insert into oracle_dept values (40,'OPERATIONS','BOSTON');

select * from oracle_dept;

 deptno | - dname     | - loc     

--------+------------+----------

     10 | ACCOUNTING | NEW YORK

     20 | RESEARCH - | DALLAS

     30 | SALES        | CHICAGO

     40 | OPERATIONS | BOSTON

# zmiana wartości

update oracle_dept set loc = 'WASHINGTON' where deptno = 10;

select * from oracle_dept;

 deptno | - dname     |     loc      

--------+------------+------------

     10 | ACCOUNTING | WASHINGTON

     20 | RESEARCH - | DALLAS

     30 | SALES        | CHICAGO

     40 | OPERATIONS | BOSTON

Tylko jedno z przedstawionych wyżej rozwiązań jest “oficjalne”, postgres_fdw, i jest wspierane przez PGDG (Postgres Global Development Group). Pozostałe dwa są rozszerzeniami stworzonymi przez różne firmy zewnętrzne i również przez nie są wspierane. Dlatego nie ma - jednego standardu i funkcjonalności poszczególnych FDW. Mogą się od siebie różnić. Jak mogliśmy zobaczyć, dla postgres_fdw mapowanie kolumn odbywało się po nazwie i domyślnie wszystkie operacje zarówno odczytu i zapisu były dozwolone. Oracle_fdw potrzebował wskazania, która kolumna jest kluczem głównym, aby umożliwić zmianę danych, a mapowanie kolumn odbywało się według kolejności kolumn, nawet jeżeli ich nazwy były inne. Natomiast tds_fdw, który pozwala na dostęp do MSSQL, umożliwia tylko odczyt, bez możliwości jakichkolwiek zmian.