Replikacja logiczna nie przenosi zmian - publication i subscription

PostgreSQL

Replikacja logiczna nie przenosi zmian - publication i subscription

W skrócie

Replikacja logiczna kopiuje zmiany na poziomie wierszy między wybranymi tabelami, także między różnymi wersjami PostgreSQL. Gdy przestaje przenosić dane, przyczyna prawie zawsze leży w jednym z kilku typowych miejsc. Przechodzimy je po kolei.

Jak to wygląda w praktyce

Na bazie źródłowej mamy publikację, na docelowej subskrypcję, połączenie się nawiązało - a mimo to w tabeli docelowej nie przybywa wierszy. Czasem początkowa kopia się wykonała, ale kolejne wstawienia już nie docierają. Innym razem replikacja idzie dla części tabel, a dla jednej stoi. Bywa też, że w logu bazy docelowej co chwilę pojawia się błąd w rodzaju duplicate key value violates unique constraint albo logical replication target relation ... has no replica identity, a subskrypcja wpada w pętlę restartów. Dane po prostu się rozjeżdżają, choć konfiguracja na pierwszy rzut oka wygląda poprawnie.

Dlaczego tak się dzieje

Replikacja logiczna ma więcej ruchomych części niż fizyczna i każda z nich potrafi po cichu zablokować przepływ. Po stronie źródła wal_level musi być ustawiony na logical - przy replica dekodowanie zmian w ogóle nie działa. Tabela musi być jawnie dodana do publikacji; publikacja FOR ALL TABLES obejmuje istniejące i nowe tabele, ale publikacja na wybrane tabele nie złapie tej, której nie wymieniliśmy. Do przenoszenia UPDATE i DELETE PostgreSQL musi umieć jednoznacznie wskazać wiersz w tabeli docelowej - służy do tego klucz główny lub ustawione REPLICA IDENTITY; bez tego takie zmiany nie przechodzą. Wreszcie sam worker subskrypcji może się wywalić na konflikcie (np. naruszeniu unikalności w tabeli docelowej) i pozostać wstrzymany - a dopóki nie usuniemy przyczyny, kolejne zmiany czekają w kolejce.

Jak to rozwiązać krok po kroku

1. Na źródle sprawdzamy poziom WAL: SHOW wal_level;. Jeśli nie jest logical, ustawiamy wal_level = logical w postgresql.conf i restartujemy klaster (tej zmiany nie da się przeładować w locie).
2. Sprawdzamy, co faktycznie jest w publikacji: SELECT * FROM pg_publication_tables WHERE pubname = 'moja_publikacja';. Brakującą tabelę dokładamy: ALTER PUBLICATION moja_publikacja ADD TABLE schemat.tabela;.
3. Na bazie docelowej sprawdzamy stan subskrypcji i jej workera: SELECT subname, subenabled FROM pg_subscription; oraz SELECT * FROM pg_stat_subscription;. Jeśli subenabled = false, włączamy: ALTER SUBSCRIPTION moja_subskrypcja ENABLE;.
4. Zaglądamy do logu bazy docelowej. Konkretny błąd (naruszenie unikalności, brak identity, błąd typu) wskaże wprost, co blokuje replikację - to najszybsza droga do przyczyny.
5. Gdy problemem jest brak identyfikatora wiersza (błąd o replica identity), na źródle ustawiamy klucz do identyfikacji. Najlepiej klucz główny; jeśli go nie ma, tworzymy unikalny indeks i wskazujemy go: ALTER TABLE tabela REPLICA IDENTITY USING INDEX nazwa_indeksu;, ewentualnie REPLICA IDENTITY FULL (wolniejsze, ale działa bez klucza).
6. Gdy replikacja rozjechała się już wcześniej i w tabeli docelowej są kolidujące wiersze, usuwamy konflikt (poprawiamy lub czyścimy dane po stronie docelowej) i, jeśli trzeba, odświeżamy subskrypcję: ALTER SUBSCRIPTION moja_subskrypcja REFRESH PUBLICATION;, co dokopiuje brakujące tabele i wznowi przepływ.
7. Jeśli zmienialiśmy skład publikacji, po stronie subskrypcji też robimy REFRESH PUBLICATION, aby subskrypcja dowiedziała się o nowych tabelach i uruchomiła ich synchronizację początkową.

Jak sprawdzić, że zadziałało

Na bazie docelowej odpytujemy SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time FROM pg_stat_subscription; - czasy powinny być świeże, a LSN rosnąć. Test praktyczny: na źródle wstawiamy nowy wiersz do replikowanej tabeli, a po chwili odpytujemy tę tabelę na bazie docelowej - wiersz powinien się pojawić. Warto sprawdzić też UPDATE i DELETE, bo to one wymagają identity - jeśli zmiana wartości w źródle odbija się w celu, klucz działa. Po stronie źródła kontrolujemy slot: SELECT slot_name, active, confirmed_flush_lsn FROM pg_replication_slots; - slot subskrypcji powinien być aktywny, a confirmed_flush_lsn nadążać za bieżącym WAL. Jeśli w logu docelowym nie ma już powtarzających się błędów workera, przepływ jest zdrowy.

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