cannot execute INSERT in a read-only transaction - aplikacja trafia na replikę

PostgreSQL

cannot execute INSERT in a read-only transaction - aplikacja trafia na replikę

W skrócie

Ten błąd zobaczysz w klastrze z repliką, gdy aplikacja albo pula połączeń po przełączeniu ról dalej łączy się z serwerem, który jest już repliką. Ten sam komunikat daje też zwykły primary, jeśli sesja jest ustawiona tylko do odczytu. Odtworzyliśmy oba przypadki na PostgreSQL 16. Pokażemy, jak szybko ustalić, który z nich masz, i jak skierować zapisy na właściwy serwer, także w klastrze Patroni.

Jak to wygląda w praktyce

Odczyty działają, a zapis kończy się błędem:

shop=# INSERT INTO orders VALUES (2001, 'app', 1);
ERROR:  cannot execute INSERT in a read-only transaction

Dla UPDATE i DELETE komunikat jest analogiczny, np. cannot execute UPDATE in a read-only transaction. Aplikacja wygląda więc na połowicznie sprawną: strony się wyświetlają, a zapis formularza się nie udaje. To nie jest problem uprawnień. Przy braku prawa do tabeli komunikat brzmi inaczej: permission denied for table orders.

Dlaczego tak się dzieje

PostgreSQL odrzuca zapis w transakcji tylko do odczytu z dwóch różnych powodów. Pierwszy to replika hot standby, czyli serwer zapasowy otwarty do odczytu: każda transakcja jest na nim tylko do odczytu, a pg_is_in_recovery() zwraca t. Po failoverze albo switchoverze (awaryjnym albo planowanym przełączeniu ról) dawny primary staje się repliką, a adres wpisany na sztywno w konfiguracji aplikacji dalej go wskazuje. Drugi powód to zwykły primary z parametrem default_transaction_read_only = on, ustawionym dla sesji, roli (ALTER ROLE ... SET) albo bazy, lub transakcja otwarta jako BEGIN READ ONLY. Parametr ma kontekst user, więc jego zmiana nie wymaga reloadu ani restartu.

Zobacz też: Szkolenie Zaawansowana administracja PostgreSQL: High Availability, Disaster Recovery

W klastrze Patroni o tym, który węzeł jest liderem, decyduje Patroni, nie aplikacja. Adres zapisu trzeba więc wiązać z rolą, a nie z konkretnym serwerem. Służy do tego REST API Patroni (w naszej lekcji na porcie 8008): GET /primary zwraca HTTP 200 tylko na węźle, który działa jako primary z blokadą lidera, a na pozostałych 503.

Jak to rozwiązać krok po kroku

1. Ustal, gdzie trafia połączenie aplikacji. Tym samym connection stringiem co aplikacja wykonaj SELECT inet_server_addr(), inet_server_port(), pg_is_in_recovery();. Wartość t w ostatniej kolumnie oznacza replikę.
2. Jeśli wyszło f, sprawdź źródło ustawienia: SELECT setting, source FROM pg_settings WHERE name = 'default_transaction_read_only';. Wartość user albo database w kolumnie source wskazuje ustawienie przypięte do roli lub bazy. Znajdziesz je w pg_db_role_setting (kolumna setconfig) i usuniesz np. poleceniem ALTER ROLE app_user RESET default_transaction_read_only;. U nas nowa sesja tej roli od razu zapisała wiersz.
3. Gdy aplikacja łączy się bezpośrednio z węzłami, podaj w connection stringu libpq (biblioteki klienckiej PostgreSQL) wszystkie węzły i wymagaj sesji do zapisu: host=pg1,pg2,pg3 port=5432 dbname=shop target_session_attrs=read-write. libpq próbuje kolejnych hostów i pomija te w trybie hot standby albo z default_transaction_read_only = on. Wartość primary sprawdza tylko tryb hot standby.
4. Gdy przed klastrem Patroni działa HAProxy, czyli serwer pośredniczący, który rozdziela połączenia między węzły, port zapisu musi sprawdzać rolę przez REST API, a nie samo nasłuchiwanie PostgreSQL. Ręcznie: curl -s -o /dev/null -w "%{http_code}" http://pg1:8008/primary daje 200 na liderze i 503 na replice. W starszych wersjach Patroni, takiej jak w naszej lekcji, ten test ma adres /master.
5. W konfiguracji HAProxy ustaw dla portu zapisu option httpchk, http-check expect status 200 i check port 8008 przy każdym serwerze, jak w przykładowym haproxy.cfg z repozytorium Patroni (bez podanej ścieżki HAProxy wysyła zapytanie OPTIONS /, a Patroni odpowiada na nie tak samo jak na GET /primary). Dodaj on-marked-down shutdown-sessions, żeby HAProxy od razu zamykał połączenia do węzła, który przestał być liderem.

Jak sprawdzić, że zadziałało

Sprawdź, że connection string z kilkoma hostami trafia na primary, nawet gdy replika jest pierwsza na liście:

$ psql "host=127.0.0.1,127.0.0.1 port=54342,54341 dbname=shop target_session_attrs=read-write" -Atc "SELECT inet_server_port(), pg_is_in_recovery()"
54341|f

W naszym teście port 54342 to replika, a libpq połączyło się z primary na 54341. Gdy na liście zostaje sama replika, połączenie kończy się błędem session is read-only, więc aplikacja nie zapisze niczego w złym miejscu. Przy HAProxy wykonaj w oknie serwisowym patronictl switchover i po kilku sekundach, potrzebnych na kolejne sprawdzenia HAProxy, powtórz INSERT przez port zapisu. Powinien przejść bez błędu, bo HAProxy kieruje już ruch do nowego lidera.

Powiązane problemy

• Jeśli aplikacja po awarii w ogóle nie łączy się z nowym primary, przejdź do tekstu po awarii primary aplikacja nie widzi nowego mastera, czyli VIP i HAProxy.
• Rolę każdego węzła sprawdzisz poleceniami z tekstu jak zarządzać klastrem Patroni na co dzień (patronictl).
• Gdy między aplikacją a HAProxy działa pula połączeń, przyda się tekst jak skonfigurować PgBouncer, gdy brakuje połączeń.

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