BŁĄD PostgreSQL: anulowanie instrukcji z powodu konfliktu z odzyskiwaniem

Podczas wykonywania zapytania w bazie danych PostgreSQL w trybie gotowości pojawia się następujący błąd. Zapytanie, które powoduje błąd, działa dobrze przez 1 miesiąc, ale gdy zapytanie trwa dłużej niż 1 miesiąc, pojawia się błąd.

ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed

Jakieś sugestie, jak rozwiązać problem? Dzięki

Uruchamianie zapytań na serwerze w trybie gorącej gotowości jest nieco skomplikowane - może się nie powieść, ponieważ podczas wykonywania zapytań niektóre potrzebne wiersze mogą zostać zaktualizowane lub usunięte na serwerze podstawowym. Ponieważ jednostka podstawowa nie wie, że zapytanie jest uruchamiane na serwerze pomocniczym, uważa, że ​​może wyczyścić (odkurzyć) stare wersje swoich wierszy. Następnie pomocniczy musi powtórzyć to czyszczenie i musi wymusić anulowanie wszystkich zapytań, które mogą używać tych wierszy.

Dłuższe zapytania będą częściej anulowane.

Możesz obejść ten problem, uruchamiając powtarzalną transakcję odczytu na podstawowym, która wykonuje fikcyjne zapytanie, a następnie pozostaje bezczynna, podczas gdy prawdziwe zapytanie jest uruchamiane na drugim. Jego obecność zapobiegnie odkurzaniu starych wersji rzędów na pierwszym.

Więcej informacji na ten temat i inne obejścia wyjaśniono w sekcji Pełna gotowość - obsługa konfliktów zapytań w dokumentacji.

Nie musisz dotykać hot_standby_feedback. Jak wspominali inni, ustawienie go na onmistrza może nadąć. Wyobraź sobie, że otwierasz transakcję na slave i nie zamykasz jej.

Zamiast tego ustaw max_standby_archive_delayi max_standby_streaming_delayna jakąś rozsądną wartość:

# /etc/postgresql/10/main/postgresql.conf on a slave
max_standby_archive_delay = 900s
max_standby_streaming_delay = 900s

W ten sposób zapytania dotyczące niewolników trwające krócej niż 900 sekund nie zostaną anulowane. Jeśli obciążenie wymaga dłuższych zapytań, po prostu ustaw te opcje na wyższą wartość.

Nie ma potrzeby rozpoczynania bezczynnych transakcji na module głównym. W postgresql-9.1 najbardziej bezpośrednim sposobem rozwiązania tego problemu jest ustawienie

hot_standby_feedback = on

Dzięki temu mistrz będzie świadomy długotrwałych zapytań. Z dokumentów :

Pierwszą opcją jest ustawienie parametru hot_standby_feedback, który zapobiega usuwaniu przez VACUUM ostatnio martwych wierszy, dzięki czemu nie występują konflikty czyszczenia.

Dlaczego nie jest to ustawienie domyślne? Ten parametr został dodany po początkowej implementacji i jest to jedyny sposób, w jaki stan gotowości może wpływać na urządzenie główne.

Jak stwierdzono tutaj, o hot_standby_feedback = on:

Cóż, wadą tego jest to, że czuwanie może nadąć mistrza, co też może być zaskakujące dla niektórych osób

A tutaj :

Z jakim ustawieniem max_standby_streaming_delay? Wolałbym domyślnie to -1 niż domyślne hot_standby_feedback włączone. W ten sposób to, co robisz w trybie gotowości, wpływa tylko na stan gotowości

Więc dodałem

max_standby_streaming_delay = -1

I nigdy więcej pg_dumpdla nas błędu, ani mistrzowskiego wzdęcia :)

W przypadku wystąpienia AWS RDS sprawdź http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html

Dane tabeli na serwerze podrzędnym w trybie pełnej gotowości są modyfikowane, gdy działa długo działające zapytanie. Rozwiązaniem (PostgreSQL 9.1+), aby upewnić się, że dane tabeli nie zostały zmodyfikowane, jest wstrzymanie replikacji i wznowienie po zapytaniu:

select pg_xlog_replay_pause(); -- suspend
select * from foo; -- your query
select pg_xlog_replay_resume(); --resume

Na odpowiedź może być za późno, ale z podobnymi problemami mamy do czynienia w przypadku produkcji. Wcześniej mieliśmy tylko jedną usługę RDS i wraz ze wzrostem liczby użytkowników po stronie aplikacji zdecydowaliśmy się dodać do niej Read Replica. Replika Read działa poprawnie na etapie przejściowym, ale po przejściu do produkcji zaczynamy otrzymywać ten sam błąd.

Więc rozwiązujemy to, włączając właściwość hot_standby_feedback we właściwościach Postgres. Odnieśliśmy się do następującego łącza

https://aws.amazon.com/blogs/database/best-practices-for-amazon-rds-postgresql-replication/

Mam nadzieję, że to pomoże.

Zamierzam dodać zaktualizowane informacje i odniesienia do doskonałej odpowiedzi @ max-malysh powyżej.

Krótko mówiąc, jeśli zrobisz coś na master, musisz to powtórzyć na slave. Postgres wykorzystuje do tego rekordy WAL, które są wysyłane po każdym zarejestrowanym działaniu na mastera do slave'a. Slave następnie wykonuje akcję i te dwa elementy są ponownie zsynchronizowane. W jednym z kilku scenariuszy możesz być w konflikcie na niewolniku z tym, co przychodzi od mistrza w akcji WAL. W większości z nich dochodzi do transakcji na niewolniku, która jest sprzeczna z tym, co akcja WAL chce zmienić. W takim przypadku masz dwie możliwości:

1. Opóźnij nieco wykonanie akcji WAL, pozwalając slave'owi zakończyć jego konfliktową transakcję, a następnie zastosuj akcję.
2. Anuluj sprzeczne zapytanie na slave.

Martwimy się numerem 1 i dwiema wartościami:

• max_standby_archive_delay - jest to opóźnienie stosowane po długim rozłączeniu między master a slave, gdy dane są odczytywane z archiwum WAL, które nie jest danymi bieżącymi.
• max_standby_streaming_delay - opóźnienie używane do anulowania zapytań, gdy wpisy WAL są odbierane za pośrednictwem replikacji strumieniowej.

Ogólnie rzecz biorąc, jeśli serwer jest przeznaczony do replikacji o wysokiej dostępności, warto, aby te liczby były krótkie. Do 30000tego wystarczające jest ustawienie domyślne (milisekund, jeśli nie podano jednostek). Jeśli jednak chcesz skonfigurować coś w rodzaju archiwum, repliki raportowania lub odczytu, które mogą mieć bardzo długo działające zapytania, ustaw to na coś wyższego, aby uniknąć anulowanych zapytań. Powyższe zalecane 900sustawienie wydaje się dobrym punktem wyjścia. Nie zgadzam się z oficjalną dokumentacją dotyczącą ustawiania nieskończonej wartości -1jako dobrego pomysłu - może to maskować jakiś błędny kod i powodować wiele problemów.

Jedynym zastrzeżeniem dotyczącym długo działających zapytań i ustawiania tych wartości na wyższe jest to, że inne zapytania działające na serwerze podrzędnym równolegle z długotrwałym, które powoduje opóźnienie akcji WAL, będą widzieć stare dane do czasu zakończenia długiego zapytania. Deweloperzy będą musieli to zrozumieć i serializować zapytania, które nie powinny działać jednocześnie.

Pełne wyjaśnienie, jak max_standby_archive_delayi jak max_standby_streaming_delaydziała, i dlaczego, znajdziesz tutaj .

Podobnie, oto drugie zastrzeżenie do opracowania @ Artif3x doskonałej odpowiedzi @ max-malysh, obu powyżej.

Przy każdym opóźnionym zastosowaniu transakcji od mastera obserwujący będą mieli starszy, nieaktualny widok danych. Dlatego też, zapewniając czas na zakończenie zapytania obserwującego przez ustawienie max_standby_archive_delay i max_standby_streaming_delay, należy pamiętać o obu tych zastrzeżeniach:

• wartość obserwatora jako rezerwowego / rezerwowego spada
• wszelkie inne zapytania działające na obserwującym mogą zwrócić nieaktualne dane .

Jeśli wartość obserwatora do tworzenia kopii zapasowych okazuje się zbytnio sprzeczna z zapytaniami hostingowymi, jednym rozwiązaniem byłoby wielu obserwujących, z których każdy byłby zoptymalizowany pod kątem jednego lub drugiego.

Należy również zauważyć, że kilka zapytań z rzędu może spowodować dalsze opóźnienie stosowania wpisów wal. Tak więc przy wybieraniu nowych wartości nie chodzi tylko o czas na pojedyncze zapytanie, ale na ruchome okno, które rozpoczyna się za każdym razem, gdy rozpoczyna się zapytanie powodujące konflikt, i kończy się, gdy wpis wal zostaje ostatecznie zastosowany.