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.
