Replikacja logiczna w PostgreSQL - jak dziala i kiedy jej uzyc

Replikacja logiczna w PostgreSQL dziala inaczej niz fizyczna - pozwala replikowac wybrane tabele miedzy roznymi wersjami serwera. Pokazujemy jak ja skonfigurowac od zera.

Z tego artykułu dowiesz się:

• czym jest replikacja logiczna i w jakim celu się ją stosuje,- jakie są plusy i minusy replikacji logicznej,- jak stworzyć replikę logiczną i replikować tylko wybrane tabele,- jak odnajdywać problematyczne przy replikacji logicznej tabele,- jak monitorować postęp replikacji logicznej.

[ Do realizacji przykładów z tego rozdziału potrzebujemy hostów 1,2,3 i 5. Należy je przygotować według instrukcji, uwzględniając poniższe zalecenia. Howto znajdziesz w instrukcji instalacji hostów, instrukcja instalacji replikacji strumieniowej i artykule na temat pgbackrest]

Do konfiguracji samej logicznej replikacji wystarczą hosty 1,3,5.

Do wykonania failovera dla logicznej repliki, której źródłem jest - wysoko dostępny klaster Postgresa, potrzebujemy dodatkowo serwera 2.

Serwer 1:

• zainstalowane binarki PostgreSQL i pgBackRest,
• stworzone katalogi z uprawnieniami,
• zainicjalizowany klaster za pomocą "initdb", jak w instrukcji,
• skonfigurowany pgbackrest (archiwizacja + backupy),
• skonfigurowane połączenie SSH bez hasła do serwera 2 i 5 (serwer kopii zapasowych).

Serwer 2:

• zainstalowane binarki PostgreSQL i pgBackRest,
• stworzone katalogi z uprawnieniami,
• skopiowany z Serwera 1 plik konfiguracyjny /etc/pgbackrest.conf,
• skonfigurowane połączenie SSH bez hasła do serwera 1 i 5 (serwer kopii zapasowych),
• skonfigurowana fizyczna replikacja strumieniowa.

Serwer 3:

• zainstalowane binarki PostgreSQL i pgBackRest,
• stworzone katalogi z uprawnieniami,
• skopiowany z Serwera 1 plik konfiguracyjny /etc/pgbackrest.conf,
• skonfigurowane połączenie SSH bez hasła do serwera 5 (serwer kopii zapasowych).

Serwer 5:

• zainstalowany pakiet pgBackRest,
• skonfigurowane repozytorium kopii zapasowych, jak w instrukcji dla konfiguracji pgBackResta.

W odróżnieniu od replikacji fizycznej, która kopiuje wszystkie dane blok po bloku z primary na instancję standby będącą tylko do odczytu, sprawiając, że oba klastry są idealnymi klonami, replikacja logiczna replikuje tylko zmiany danych w obiektach, DML - Data Manipulation Language. Dlatego wszystkie operacje DDL - Data Definition Language - takie jak "ALTER TABLE ... ADD COLUMN" lub tworzenie nowych tabel muszą być wykonywane zarówno na primary jak i standby. Ale dzięki temu, że replikacja logiczna wykonuje tylko operacje DML na instancji docelowej, możliwe jest skonfigurowanie replikacji pomiędzy różnymi wersjami postgresa. Zarówno instancja źródłowa jak i docelowa mogą być otwarte do zapisu pod warunkiem, że tylko klaster źródłowy zmienia dane replikowanych tabel oraz umożliwia selektywną replikację, np. wybranej grupy tabel. Tabele mogą mieć inne indeksy, ale muszą zachować tę samą kolejność kolumn i kompatybilne typy danych. Jednak koniecznym przy jej konfiguracji jest posiadanie przez wszystkie replikowane tabele tzw. "replica identity", tzn. kolumny, lub zestawu kolumn, które tworzą unikalną wartość, które nie może się w tabeli powtórzyć, np. klucza głównego, unique constraint lub ustawienie "replica_identity = full". Replica identity full oznacza, że każdy wiersz jest w całości kluczem głównym, przez co niemożliwe jest ponowne dodanie tego samego wiersza. Ostatnia opcja nie jest polecana, a jej użycie powinno być ograniczone tylko do ostateczności, ponieważ każda zmiana wiersza w takiej tabeli spowoduje wywołanie skanu sekwencyjnego na niej w celu sprawdzenia, czy wiersz nie jest duplikatem.

Jeszcze jedną istotną różnicą pomiędzy replikacją fizyczną a logiczną jest fakt, że fizyczna replikuje cały klaster/instancję razem ze wszystkimi znajdującymi się w nim bazami, podczas gdy replikacja logiczna musi być skonfigurowana dla każdej bazy z osobna. Każda z baz w replikacji logicznej może też posiadać więcej niż jedną publikację, co jest częstą praktyką w przypadku większych baz, gdzie dla największych tabel możemy stworzyć dedykowane publikacje i subksrypcje, a resztę tabel podzielić po równo na pozostałe subskrypcje. Przykładowo, na podstawie rzeczywistej implementacji logicznej replikacji:

• publikacja 1 - tabela 700GB,
• publikacja 2 - tabela 500GB,
• publikacja 3 - 4 tabele, ważące w sumie 600GB,
• publikacja 4 - 30 tabel, ważących razem 500GB,
• publikacja 5 - wszystkie pozostałe tabele 300GB.

Pozwala nam to zminimalizować wpływ blokad na tabelach uniemożliwiających replikację do tylko jednej publikacji z pięciu. Czyli zawsze większość tabel powinna być w stanie się replikować, nawet kiedy transakcja na logicznej replice tymczasowo blokuje możliwość replikacji w jednej z tabel.

Konfiguracja logicznej replikacji wymaga zmiany poziomu "wal_level" na logical. Postgres dodaje do pliku WAL dodatkowe informacje umożliwiające odszyfrowanie poleceń SQL z plików WAL z primary na instancji docelowej. Trzeba też dodać publikację i logiczny slot replikacji na klastrze źródłowym oraz dodać subskrypcję na instancji docelowej. Musimy też ręcznie stworzyć wszystkie replikowane tabele na instancji docelowej lub zagwarantować ich obecność w inny sposób, zanim zaczniemy cokolwiek replikować, ponieważ replikacja logiczna nie przesyła żadnych zapytań DDL i sama nie jest w stanie stworzyć potrzebnych obiektów.

Publikacja określa, które tabele replikować. Mamy kilka możliwości stworzenia publikacji:

• wszystkie tabele w bazie,
• wszystkie tabele w schemacie,
• dla jednej lub grupy tabel,
• dla tabeli, ale tylko wiersze pasujące do zadanego filtra w klauzuli where,
• dla tabeli, ale tylko operacje wybranego typu, np. same inserty,
• dla tabeli, ale tylko wybrane kolumny.

Subskrypcja na docelowym klastrze zawiera informacje, z której publikacji na jakim serwerze dane są replikowane, który slot replikacyjny będzie przez nią wykorzystywany oraz wstępną konfigurację dla subskrypcji. Domyślnie każda subskrypcja w momencie stworzenia automatycznie się aktywuje, spróbuje stworzyć sobie nowy slot replikacyjny oraz rozpocznie kopiowanie danych z tabel powiązanych z wybraną publikacją.

Replikacja logiczna często ma nieco inne zastosowania niż replikacja fizyczna. Jedną z częstszych jest upgrade głównej wersji postgresa "online", z uwagi na możliwość replikowania zmian pomiędzy różnymi głównymi wersjami postgresa, replikacja do klastra przeznaczonego do analizy danych/data science, aby - długie analityczne zapytania nie blokowały procesu replikacji i dla możliwości tworzenia nowych tabel, i widoków zmaterializowanych, które są często wykorzystywane przy analizie danych. Innym przykładem jest sytuacja, kiedy mamy bardzo dużo małych zmian do przesłania z instancji primary na replikę, w takich sytuacjach replikacja fizyczna może nie dać rady dotrzymać tempa i zacząć lagować, a logiczna replikacja jest w stanie replikować bardzo częste małe aktualizacje sprawniej niż fizyczna. Z drugiej strony, odtwarzanie bardzo dużych transakcji, zmieniających gigabajty danych, mogą być czasami problematyczne do odtworzenia przez replikację logiczną, bez tuningu dodatkowych parametrów.

Jest kilka sposobów na konfigurację logicznej replikacji.

Najprostszy, wykorzystywany głównie przy konfiguracji replikacji dla mniejszych baz - lub kiedy serwer z instancją źródłową ma odpowiednio dużo dostępnych zasobów, CPU / IO, które można poświęcić, ponieważ wstępne kopiowanie danych może być dość zasobożerne przy dużych, często zmienianych tabelach. Możemy też wykorzystać dumpa z danymi, kopię zapasową lub snapshota w przypadku rozwiązań chmurowych jako podstawę do logicznej replikacji, co przy większych bazach umożliwi nam pominięcie wstępnego kopiowania tabel, dzięki czemu możemy zaoszczędzić sporo czasu oraz uniknąć zbędnego obciążenia instancji primary.

Sposób pierwszy - z użyciem pg_dumpall

[host 3: nowa replika logiczna]

Przykład zacznijmy od inicjalizacji nowego klastra postgresa na serwerze, na którym skonfigurujemy replikę. Jako użytkownik postgres wykonajmy initdb i wystartujmy klaster.

/usr/lib/postgresql/15/bin/initdb -D /data_pg/

vi /data_pg/postgresql.conf

listen_addresses = '*'

/usr/lib/postgresql/15/bin/pg_ctl -D /data_pg/ start

[host 1: serwer master]

Przed przystąpieniem do konfiguracji logicznej replikacji powinniśmy się upewnić, że instancja źródłowa (primary) ma ustawione odpowiednie parametry w postgresql.conf. Jeżeli wal_level jest na niższym poziomie, a listen_addresses nie jest '*' lub nie nasłuchuje na interfejsie sieciowym, z którego będziemy się łączyć z repliki, ustawmy poniższe parametry i zrestartujmy klaster:

vi /data_pg/postgresql.conf

wal_level=logical

listen_addresses = '*'

Następnie stwórzmy tabele testowe dla replikacji, jedną z kluczem głównym, drugą z unikalnym constraintem, - trzecią z wykorzystaniem replica identity full.

postgres=# CREATE TABLE tabela_pk ( a INT PRIMARY KEY, b INT );

postgres=# CREATE TABLE tabela_uq ( a INT, b INT );

postgres=# ALTER TABLE tabela_uq ALTER COLUMN b SET NOT NULL;

postgres=# ALTER TABLE tabela_uq ADD CONSTRAINT tabela_uq_constraint UNIQUE (b);

postgres=# CREATE TABLE tabela_identity_full ( a INT, b INT );

postgres=# ALTER TABLE tabela_identity_full REPLICA IDENTITY FULL;

1. Przygotowując się do stworzenia nowej logicznej repliki, musimy zacząć od znalezienia problematycznych tabel, które nie posiadają żadnego "replica identity":

postgres=# SELECT c.table_schema, c.table_name, c.table_type

FROM information_schema.tables c

WHERE c.table_schema NOT IN('information_schema', 'pg_catalog')

AND c.table_type = 'BASE TABLE'

 AND NOT EXISTS(SELECT i.tablename

FROM pg_catalog.pg_indexes i

WHERE i.schemaname = c.table_schema

AND i.tablename = c.table_name AND indexdef LIKE '%UNIQUE%')

 AND NOT EXISTS (SELECT cu.table_name

FROM information_schema.key_column_usage cu

WHERE cu.table_schema = c.table_schema

AND cu.table_name = c.table_name)

ORDER BY c.table_schema, c.table_name;

Wynik zapytania zwróci listę tabel, które nie posiadają klucza głównego lub klucza unikatowego (unique constraint).

1. Na instancji źródłowej musimy stworzyć publikację dla wszystkich tabel, lub wybranej grupy tabel, oraz użytkownika z uprawnieniami replikacji i - prawem do selecta na replikowanych obiektach. Następnie wykonujemy pg_dump dla samych definicji obiektów i odtwarzamy go na docelowej instancji.

# stworzenie użytkownika dla logicznej replikacji

postgres=# CREATE USER logicalrep WITH replication password 'logiczna_replika';

# uprawnienia select dla wszystkich tabel w schemacie

postgres=# grant SELECT on ALL tables in schema public to logicalrep ;

# automatyczne nadawanie uprawnień dla nowo tworzonych obiektów

postgres=# alter default privileges in schema public grant select ON tables TO logicalrep ;

# stworzenie publikacji dla wszystkich tabel

postgres=# CREATE PUBLICATION pierwsza_publikacja FOR ALL TABLES ;

Przed wykonaniem kopii struktury obiektów pamiętajmy o edycji plików pg_hba.conf i przeładowaniu konfiguracji postgresa, przykładowo za pomocą funkcji pg_reload_conf().

Wpisy, które należy dodać na primary:

host     postgres        logicalrep        <iprepliki>/32        scram-sha-256

host     replication - logicalrep        <iprepliki>/32        scram-sha-256

i - przeładowanie konfiguracji:

psql -c "select pg_reload_conf()"

[host 3: nowa replika logiczna]

oraz wpis do pg_hba.conf dla logicznej repliki:

host     postgres        postgres        <ipprimary>/32        trust

[host 1: serwer master]

Z instancji źródłowej wykonajmy pg_dump i przekierujmy wynik polecenia "pg_dump" przez "pipe" | do postgresa na serwerze docelowym dla logicznej replikacji, ignorując błędy o brakujących użytkownikach. Jeżeli chcielibyśmy przenieść również użytkowników, możemy to zrobić za pomocą pg_dumpall.

pg_dump -d postgres -s -n public | psql -h <target_ip_repliki>

pg_dumpall --roles-only | psql -h <target_ip_repliki>

[host 3: nowa replika logiczna]

1. Na instancji docelowej tworzymy wszystkie replikowane obiekty z dumpa oraz tworzymy subskrypcję. Domyślnie subskrypcja uruchomi się automatycznie po stworzeniu, podłączy do instancji źródłowej, stworzy slot replikacyjny i zacznie wstępne kopiowanie danych do tabel za pomocą COPY. Pamiętaj o podmianie adresu ip hosta poniżej na adres ip serwera primary:

CREATE SUBSCRIPTION pierwsza_subskrypcja CONNECTION 'host=<X.X.X.X> dbname=postgres user=logicalrep password=logiczna_replika port=5432' PUBLICATION pierwsza_publikacja;

[host 1: serwer master]

1. Na instancji primary możemy skorzystać z poniższego zapytania, aby monitorować postęp replikacji:

SELECT slot_name, confirmed_flush_lsn as flushed, pg_current_wal_lsn(), (pg_current_wal_lsn() - confirmed_flush_lsn) AS lsn_distance, pg_size_pretty((pg_current_wal_lsn() - confirmed_flush_lsn)) AS slot_size - FROM pg_catalog.pg_replication_slots;

                slot_name                    | - flushed - | pg_current_wal_lsn | lsn_distance | slot_size

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

 pierwsza_subskrypcja                         | 2/ED8D4968 | 2/ED8D6000           |           5784 | 5784 bytes

 pg_16430_sync_16387_7160376278680324808 | 2/E60000D0 | 2/ED8D6000           |     126705456 | 121 MB

Sposób drugi - z użyciem pgbackrest

[host 3: nowa replika logiczna]

Drugi sposób - jest bardzo podobny dla pozostałych opisanych przypadków, - tzn. wykorzystania dumpa, kopii zapasowej lub snapshota jako podstawę dla logicznej replikacji. Różni się tylko sposób, w jaki dane dostarczymy do postgresa, pozostałe kroki są takie same. W poniższym przykładzie wykorzystamy wal-shipping replikę jako bazę, podstawę dla logicznej repliki.

1. Pierwszym krokiem, jak w poprzednim przykładzie, będzie weryfikacja konfiguracji, sprawdzenie "replica identity", stworzenie użytkownika z odpowiednimi uprawnieniami.
   Możemy powtórzyć polecenia z poprzedniego przykładu.

2. Następnym krokiem będzie zatrzymanie poprzedniej wersji logicznej repliki, wyczyszczenie katalogu PGDATA oraz odzyskanie bazy z kopii zapasowej za pomocą pgbackresta z dodatkowym parametrem "--type=standby", aby automatycznie stworzyć konfigurację dla log shipping replikacji
   

/usr/lib/postgresql/15/bin/pg_ctl -D /data_pg/ stop

rm -rf /data_pg/*

pgbackrest --stanza test --log-level-console=detail --type=standby restore

Po odtworzeniu bazy, przed jej wystartowaniem należy zmienić "archive_command", aby nie dopisać plików WAL z nowo odtworzonej kopii instancji (docelowo logicznej repliki) do repozytorium WAL, z którego korzysta źródłowa instancja. Kiedy wal-shipping replica będzie na "bieżąco", awansujemy replikę do zapisu, co spowoduje automatyczne rozpoczęcie archiwizowania.

W pliku postgresql.conf lub postgresql.auto.conf zmieniamy nazwę stanzy na dowolną inną. W poniższym przykładzie zostawiłem nazwę stanzy i dodałem do niej przyrostek "_logical". Zmiana ta spowoduje, że archiwizacja zacznie wyrzucać błędy o nieudanej archiwizacji do logu, do czasu skonfigurowania pgBackResta dla tej instancji i stworzenia stanzy "test_logical".

vi /data_pg/postgresql.conf

archive_command = '/usr/bin/pgbackrest --config=/etc/pgbackrest.conf    -stanza=test_logical archive-push %p'

Możemy teraz wystartować postgresa i rozpocząć odtwarzanie zmian z archiwum.

/usr/lib/postgresql/15/bin/pg_ctl -D /data_pg/ start

[host 1: serwer master]

1. Kiedy replika jest już na bieżąco ze zmianami, na instancji źródłowej możemy stworzyć nowy logiczny slot replikacyjny i nową publikację, w tej kolejności. Slot replikacyjny tworzymy ręcznie, dlatego ważne jest, aby był on stworzony przed awansowaniem repliki. Logiczny slot replikacyjny rozpoczyna przetrzymywanie plików WAL natychmiast po stworzeniu, w odróżnieniu od fizycznego, który rozpoczyna je przetrzymywać dopiero po pierwszym podłączeniu się do niego.

SELECT pg_create_logical_replication_slot('slot_szkolenie', 'pgoutput');

create publication druga_pub for table tabela_pk, tabela_uq, tabela_identity_full;

[host 3: nowa replika logiczna]

1. Kiedy publikacja została już zreplikowana, możemy to sprawdzić w widoku pg_publication, mamy pewność, że slot replikacyjny był stworzony wcześniej i przechowuje wszystkie zmiany, których będziemy potrzebować. Możemy więc otworzyć replikę, aby umożliwić w niej zapis:

postgres=# \x

Expanded display is on.

postgres=# select * from pg_publication;

-[ RECORD 1 ]+--------------------

oid             | 16407

pubname        | pierwsza_publikacja

pubowner      | 10

puballtables | t

pubinsert      | t

pubupdate        | t

pubdelete      | t

pubtruncate - | t

pubviaroot - | f

-[ RECORD 2 ]+--------------------

oid             | 16411

pubname        | druga_pub

pubowner        | 10

puballtables | f

pubinsert      | t

pubupdate      | t

pubdelete      | t

pubtruncate - | t

pubviaroot - | f

postgres=# select pg_promote();

1. Jeżeli podążalismy dokładnie według instrukcji do konfiguracji replikacji fizycznej strumieniowej, warto sprawdzić, czy nowo awansowana replika nie próbuje synchronicznie commitować z pg2. Jeżeli parametr "synchronous_standby_names" jest niepusty, należy go zresetować do domyślnej wartości i przeładować konfigurację.

postgres=# show synchronous_standby_names ;

 synchronous_standby_names

---------------------------

 pg2

(1 row)

postgres=# alter system reset synchronous_standby_names;

ALTER SYSTEM

postgres=# select pg_reload_conf();

 pg_reload_conf

----------------

 t

(1 row)

postgres=# show synchronous_standby_names ;

 synchronous_standby_names

---------------------------

(1 row)

1. Odnajdujemy ostatnią odtworzoną lokację z logu postgresa, domyślnie w $PGDATA/log/, czyli w naszym przypadku /data_pg/log. Sprawdzamy w najnowszym pliku loga, linia z lokacją będzie znajdowała się zaraz za linią z informacją o wywołaniu funkcji pg_promote() i będzie zawierała frazę "redo done at".

ls -latr /data_pg/log

grep "redo done at" /data_pg/log/Postgresql-XXXXXXX.log

LOG: - redo done at 4/59FFE628

1. Kolejnym krokiem będzie usunięcie publikacji, które zostały zreplikowane z instancji źródłowej. Poniższym poleceniem wypiszemy polecenie "drop publication" dla wszystkich istniejących publikacji na instancji, na której konfigurujemy logiczną replikację, a \gexec spowoduje wykonaniu wyniku zapytania.

postgres=# select 'drop publication ' || pubname || ';' from pg_publication;

postgres=# \gexec

1. Teraz możemy utworzyć subskrypcję. Składnia jest bardzo podobna do wcześniej użytego polecenia, ale tutaj dodajemy dodatkową część "WITH", gdzie definiujemy, z jakimi ustawieniami nowa subskrypcja powinna zostać stworzona. Dodatkowe parametry to kolejno, copy_data = false, aby postgres nie wykonywał wstępnej kopii danych z tabel źródłowych, create_slot = false to informacja, że nie chcemy automatycznie tworzonego slota, mamy go już stworzonego ręcznie, enabled = false, aby subskrypcja nie próbowała replikować od razu po utworzeniu. Ważne jest, - aby o tym pamiętać, ponieważ postgres automatycznie próbowałby replikować wszystko, co znajduje się w slocie "slot_szkolenie", a my będziemy chcieli wskazać mu, od kiedy zmiany powinny być replikowane, connect = true, chcemy, żeby subskrypcja spróbowała się połączyć z postgresem w momencie tworzenia subskrypcji, slot_name = 'slot_szkolenie' to nazwa naszego ręcznie stworzonego slota.

postgres=# CREATE SUBSCRIPTION druga_sub CONNECTION 'host=<adres_ip_serwera_primary> dbname=postgres user=logicalrep password=logiczna_replika port=5432'

PUBLICATION druga_pub

WITH (

copy_data = false,

create_slot = false,

enabled = false,

connect = true,

slot_name = 'slot_szkolenie'

);

1. Za pomocą funkcji pg_replication_origin_advance() ręcznie ustawimy, od której transakcji postgres - powinien zacząć replikować zmiany. Chcemy odtwarzać tylko te zmiany, które były wykonane po awansowaniu / otwarciu repliki do zapisu. W widoku pg_replication_origin znajdziemy listę źródeł replikacji, serwerów, z których możemy replikować zmiany, a w widoku pg_subscription listę subskrypcji.

postgres=# select * from pg_replication_origin;

-[ RECORD 1 ]-----

roident | 1

roname - | pg_26553

postgres=# select * from pg_subscription;

-[ RECORD 1 ]----+---------------------------------------------------------------------------

oid                  | 26553

subdbid             | 13483

subskiplsn        | 0/0

subname             | druga_sub

subowner           | 10

subenabled        | f

subbinary          | f

substream          | f

subtwophasestate | d

subdisableonerr - | f

subconninfo        | host=192.168.38.122 dbname=postgres user=logicalrep password=logiczna_replika port=5432

subslotname        | slot_szkolenie

subsynccommit     | off

subpublications - | {druga_pub}

Funkcja pg_replication_origin_advance(), przyjmuje dwie wartości, "roname" z widoku pg_replication_origin oraz miejsce we wpisach WAL, lsn, pobrane w punkcie 6. Roname, to OID subskrypcji, z którą jest powiązany z przedrostkiem pg_.

select pg_replication_origin_advance('<roname>,'<lsn adres z punktu 6>');

select pg_replication_origin_advance('pg_26553','4/59FFE628');

1. Możemy teraz włączyć subskrypcję i zacząć replikować zmiany, które zostały dodane po awansowaniu repliki.

postgres=# alter subscription druga_sub enable;

1. Na instancji primary możemy skorzystać z poniższego zapytania, aby monitorować postępy replikacji

postgres=# SELECT slot_name, confirmed_flush_lsn as flushed, pg_current_wal_lsn(), (pg_current_wal_lsn() - confirmed_flush_lsn) AS lsn_distance, pg_size_pretty((pg_current_wal_lsn() - confirmed_flush_lsn)) AS slot_size FROM pg_catalog.pg_replication_slots;

Tworząc logiczną replikację w ten sposób, unikamy potrzeby kopiowania danych ze wszystkich replikowanych tabel po stworzeniu subskrypcji, czyli najkosztowniejszej i najbardziej zasobożernej części. Zdecydowana większość danych jest dostępna dzięki odzyskaniu klastra postgresa z kopii zapasowej i odtworzeniu zmian z plików WAL.