Konfiguracja postgres_fdw (Foreign Data Wrapper) w PostgreSQL

PostgreSQL

Foreign Data Wrapper w PostgreSQL pozwala na dostęp do danych z innych baz danych PostgreSQL tak, jakby były one częścią lokalnego serwera. Oto ogólny przewodnik po konfiguracji:

1. Instalacja rozszerzenia:
Jeśli rozszerzenie postgres_fdw nie jest jeszcze zainstalowane, zainstaluj je w bazie danych, w której chcesz mieć dostęp do danych zdalnych:

CREATE EXTENSION postgres_fdw;
SQL

2. Utworzenie serwera obcego:
Serwer obcy reprezentuje zdalną bazę danych PostgreSQL. Musisz podać informacje o połączeniu.

CREATE SERVER moj_serwer_zdalny
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'adres_hosta_zdalnego', port '5432', dbname 'nazwa_bazy_zdalnej');
SQL
  • moj_serwer_zdalny: Nazwa, którą nadasz serwerowi.
  • adres_hosta_zdalnego: Adres IP lub nazwa hosta zdalnej bazy danych.
  • 5432: Port, na którym nasłuchuje zdalna baza danych (domyślnie 5432).
  • nazwa_bazy_zdalnej: Nazwa bazy danych na serwerze zdalnym.

3. Utworzenie mapowania użytkownika:
Mapowanie użytkownika łączy lokalnego użytkownika z użytkownikiem na zdalnym serwerze. To pozwala na uwierzytelnienie.

CREATE USER MAPPING FOR uzytkownik_lokalny
SERVER moj_serwer_zdalny
OPTIONS (user 'uzytkownik_zdalny', password 'haslo_uzytkownika_zdalnego');
SQL
  • uzytkownik_lokalny: Nazwa użytkownika w lokalnej bazie danych.
  • uzytkownik_zdalny: Nazwa użytkownika w zdalnej bazie danych.
  • haslo_uzytkownika_zdalnego: Hasło użytkownika w zdalnej bazie danych.

WAŻNE: Zaleca się używanie bezpieczniejszych metod uwierzytelniania, takich jak pliki .pgpass, zamiast przechowywać hasło bezpośrednio w definicji mapowania użytkownika. Plik .pgpass pozwala na przechowywanie haseł w bezpieczny sposób.

4. Używanie tabeli obcej:
Tabela obca definiuje, które tabele z zdalnej bazy danych chcesz udostępnić lokalnie.

CREATE FOREIGN TABLE moja_tabela_zdalna (
    id integer,
    nazwa text,
    data date
)
SERVER moj_serwer_zdalny
OPTIONS (schema 'public', table 'nazwa_tabeli_w_bazie_zdalnej');
SQL
  • moja_tabela_zdalna: Nazwa, którą nadasz tabeli lokalnie.
  • id integer, nazwa text, data date: Definicja kolumn tabeli (musi odpowiadać kolumnom w zdalnej tabeli).
  • schema 'public’: Nazwa schematu w zdalnej bazie danych (często 'public’).
  • nazwa_tabeli_w_bazie_zdalnej: Nazwa tabeli w zdalnej bazie danych.

5. Używanie tabeli obcej:
Po utworzeniu tabeli obcej, możesz jej używać tak, jakby była lokalna:

SELECT * FROM moja_tabela_zdalna;
SQL

Przykład z .pgpass:

1. Utwórz plik .pgpass w katalogu domowym użytkownika (np. ~/.pgpass na Linux/macOS, %APPDATA%\postgresql\pgpass.conf na Windows).

2. Dodaj linię w formacie: hostname:port:database:username:password

3. Ustaw odpowiednie uprawnienia do pliku (np. chmod 0600 ~/.pgpass na Linux/macOS).

4. W definicji USER MAPPING pomiń opcję password:

CREATE USER MAPPING FOR uzytkownik_lokalny
SERVER moj_serwer_zdalny
OPTIONS (user 'uzytkownik_zdalny');
SQL

Kod SQL usuwający zera z wartości numerycznych.

SQL

Przykład kodu SQL usuwający zbędne zera z wartości numerycznych przechowywanych jako tekst w kolumnie „COL1”

COL1NEW_COL1
13.0013
12.30012.3
1212
12.012
12.34512.345
12.5012.5
12.55012.55
SELECT
CASE
    
    WHEN "table1"."COL1" :: TEXT ~~ '%.%' :: TEXT THEN
    TRIM(TRAILING '.' :: TEXT FROM TRIM(TRAILING '0' :: TEXT FROM "TABLE1"."COL1")) :: CHARACTER VARYING ELSE "TABLE1"."COL1" 
  END AS "NEW_COL1" 
FROM
  "TABLE1"
SQL
  1. SELECT ... FROM "TABLE1": To polecenie wybiera dane z tabeli o nazwie „TABLE1”.
  2. CASE WHEN ... THEN ... ELSE ... END AS "NEW_COL1": To jest instrukcja warunkowa CASE. Pozwala ona na wykonanie różnych operacji w zależności od spełnienia określonego warunku. AS "NEW_COL1" nadaje nowej kolumnie nazwę „NEW_COL1”. Oznacza to, że zapytanie utworzy nową kolumnę z wyczyszczonymi danymi, pozostawiając oryginalną kolumnę „COL1” bez zmian.
  3. "table1"."COL1"::TEXT ~~ '%.%'::TEXT: To jest warunek, który jest sprawdzany przez instrukcję CASE.
    • "table1"."COL1"::TEXT: Ta część jest bardzo ważna. Konwertuje dane z kolumny „COL1” na typ danych TEXT (tekstowy). Operator ~~ (który działa podobnie do LIKE, ale jest czuły na wielkość liter) działa na ciągach tekstowych. Nawet jeśli kolumna „COL1” przechowuje liczby, ta część traktuje je jako tekst na potrzeby porównania.
    • ~~ '%.%'::TEXT: To jest operacja dopasowywania wzorca. '%.%' to wzorzec przypominający wyrażenie regularne. Oznacza „dowolne znaki (%), po których następuje dosłowny znak kropki (.), a po nim dowolne znaki (%)”. Zatem ten warunek sprawdza, czy wartość w „COL1” zawiera kropkę. W ten sposób kod identyfikuje wartości, które wyglądają jak liczby dziesiętne.
  4. TRIM(TRAILING '.'::TEXT FROM TRIM(TRAILING '0'::TEXT FROM "TABLE1"."COL1"))::CHARACTER VARYING: Ta część jest wykonywana tylko, jeśli warunek (obecność kropki) jest prawdziwy. To jest część odpowiedzialna za czyszczenie danych:
    • TRIM(TRAILING '0'::TEXT FROM "TABLE1"."COL1"): To polecenie usuwa zera z prawej strony ciągu tekstowego. Na przykład, jeśli „COL1” ma wartość „12.300”, ta część da w wyniku „12.3”.
    • TRIM(TRAILING '.'::TEXT FROM ...): To polecenie usuwa kropkę z prawej strony ciągu tekstowego. Na przykład, jeśli „COL1” ma wartość „12.3.”, ta część da w wyniku „12.3”.
    • Połączenie tych dwóch operacji TRIM usuwa zarówno zera z końca, jak i kropkę z końca, jeśli występują. Zatem „12.300.” zmieni się w „12.3”.
    • ::CHARACTER VARYING: To polecenie konwertuje wynik z powrotem na typ danych CHARACTER VARYING (ciąg tekstowy o zmiennej długości). Jest to ważne, aby „NEW_COL1” miała ten sam typ danych, co oryginalna kolumna „COL1” (lub typ z nią zgodny).
  5. "TABLE1"."COL1": Ta część jest wykonywana tylko, jeśli warunek (obecność kropki) jest fałszywy. Jeśli wartość w „COL1” nie zawiera kropki, jest używana bezpośrednio w kolumnie „NEW_COL1” bez żadnych zmian.