Jak odpytać tabele w innej bazie (dblink i postgres_fdw)

PostgreSQL

Jak odpytać tabele w innej bazie (dblink i postgres_fdw)

W skrócie

W Oracle jednym linkiem bazodanowym sięgasz do dowolnej innej instancji. Osoby przesiadające się na PostgreSQL szybko odkrywają, że tu SELECT po tabeli z sąsiedniej bazy zwraca "relation does not exist", nawet jeśli obie bazy stoją na tym samym serwerze. To nie błąd - PostgreSQL świadomie izoluje bazy od siebie. Żeby zajrzeć do innej bazy, dokładamy jedną z dwóch nakładek: starszy, funkcyjny dblink albo nowocześniejszy postgres_fdw, który udaje, że zdalna tabela jest lokalna.

Jak to wygląda w praktyce

Masz bazę sprzedaz i osobną bazę magazyn na tym samym klastrze. Chcesz w raporcie zestawić zamówienia ze stanami magazynowymi. Piszesz SELECT * FROM magazyn.public.stany i dostajesz błąd, że schemat magazyn nie istnieje - bo notacja baza.schemat.tabela w PostgreSQL nie sięga do innej bazy, człon "bazy" tu nie działa. Zmiana bazy w trakcie sesji (\c magazyn w psql) rozłącza cię od sprzedaz, więc w jednym zapytaniu i tak ich nie połączysz. Ten sam problem pojawia się, gdy dane leżą w zupełnie innym silniku - Oracle albo Microsoft SQL Server - i chcesz je dołączyć do zapytania po stronie PostgreSQL.

Dlaczego tak się dzieje

W PostgreSQL sesja backendu obsługuje dokładnie jedną bazę danych przez cały swój czas życia. Katalogi systemowe (definicje tabel, uprawnienia) są per-baza, więc silnik po prostu nie widzi obiektów sąsiadki. To rozwiązanie celowe - daje mocną izolację. Dostęp międzybazowy realizuje się więc przez osobne, jawne połączenie sieciowe do drugiej bazy. Robią to dwa mechanizmy. dblink to zestaw funkcji: otwierasz połączenie i wywołujesz zdalne zapytanie jako funkcję zwracającą wiersze. postgres_fdw to implementacja standardu SQL/MED (foreign data wrapper): definiujesz raz "serwer obcy", tworzysz tabele obce odwzorowujące tabele zdalne i odpytujesz je normalną składnią, a wrapper w tle przekłada to na zdalne zapytania i - co ważne - potrafi przepchnąć filtry (WHERE) na drugą stronę, żeby nie ściągać całej tabeli. Do innych silników służą odrębne wrappery: oracle_fdw dla Oracle, tds_fdw dla SQL Server.

Jak to rozwiązać krok po kroku

1. Wybierz narzędzie. Jednorazowy odczyt albo wywołanie po stronie zdalnej to dobra rola dla dblink. Stałe, powtarzalne odpytywanie zdalnych tabel w wielu zapytaniach - wybierz postgres_fdw, bo zapytania wyglądają jak lokalne, a planer optymalizuje przesyłanie.
2. Załóż rozszerzenie w bazie, z której będziesz pytać: CREATE EXTENSION postgres_fdw; (lub CREATE EXTENSION dblink;). Wymaga uprawnień superużytkownika.
3. Dla postgres_fdw zdefiniuj serwer obcy: CREATE SERVER magazyn_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5432', dbname 'magazyn');.
4. Dodaj mapowanie użytkownika z danymi logowania do zdalnej bazy: CREATE USER MAPPING FOR raportowy SERVER magazyn_srv OPTIONS (user 'czytelnik', password 'sekret');. Dzięki temu hasło zdalne jest przypisane do konkretnej roli lokalnej.
5. Utwórz tabele obce. Najwygodniej importem całego schematu: IMPORT FOREIGN SCHEMA public FROM SERVER magazyn_srv INTO magazyn_zdalny; - PostgreSQL sam pobierze definicje kolumn i utworzy odpowiedniki w schemacie magazyn_zdalny.
6. Odpytuj i łącz jak lokalnie: SELECT z.nr, s.ilosc FROM zamowienia z JOIN magazyn_zdalny.stany s ON s.produkt_id = z.produkt_id;.
7. Alternatywnie przez dblink, bez tabel obcych: SELECT * FROM dblink('host=127.0.0.1 dbname=magazyn user=czytelnik password=sekret', 'SELECT produkt_id, ilosc FROM stany') AS t(produkt_id int, ilosc int); - przy dblink musisz jawnie podać typy zwracanych kolumn.

Jak sprawdzić, że zadziałało

Zdefiniowane serwery obce obejrzysz przez SELECT srvname, srvoptions FROM pg_foreign_server;, a utworzone tabele obce przez SELECT foreign_table_schema, foreign_table_name FROM information_schema.foreign_tables; albo w psql poleceniem \det. Najlepszy test to po prostu odpytanie tabeli obcej - jeśli zwróci wiersze ze zdalnej bazy, most działa. Warto sprawdzić, czy filtry są przepychane na drugą stronę: EXPLAIN (VERBOSE) SELECT * FROM magazyn_zdalny.stany WHERE produkt_id = 10;. W planie zobaczysz węzeł Foreign Scan z sekcją Remote SQL zawierającą warunek WHERE - to znak, że postgres_fdw wysyła filtr do zdalnej bazy, zamiast ściągać wszystko i filtrować lokalnie. Jeśli warunku tam nie ma, przejrzyj typy kolumn tabeli obcej, bo niezgodność potrafi zablokować pushdown.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5