Na których poziomach ustawiać parametry PostgreSQL (klaster, baza, sesja)

PostgreSQL

Na których poziomach ustawiać parametry PostgreSQL (klaster, baza, sesja)

W skrócie

Ustawianie parametrów w PostgreSQL bywa mylące, bo tę samą wartość można nadać na kilku poziomach, a każdy z nich obowiązuje w innym zasięgu. Efekt jest taki, że zmiana w pliku konfiguracyjnym potrafi zostać przykryta ustawieniem dla konkretnej bazy albo sesji. Pokażemy Ci, jakie poziomy istnieją, jak układa się ich pierwszeństwo i który poziom wybrać w zależności od tego, co chcesz osiągnąć. Temat jest dla każdego, kto stroi bazę i chce mieć nad tym pełną kontrolę.

Jak to wygląda w praktyce

Zmieniasz parametr w głównym pliku konfiguracyjnym, przeładowujesz serwer, a w jednej z baz i tak obowiązuje inna wartość. Albo ustawiasz coś na czas testu i zapominasz, że po zamknięciu sesji zmiana zniknie. Bywa też odwrotnie: chciałeś zmienić parametr tylko dla jednego zadania, a zmieniłeś go dla całego serwera i wpłynąłeś na wszystkich. Wspólny objaw to rozjazd między tym, co ustawiłeś, a tym, co baza faktycznie stosuje w danym miejscu i czasie. Bez zrozumienia poziomów wygląda to na nieprzewidywalność, choć rządzą tym proste reguły.

Dlaczego tak się dzieje

PostgreSQL pozwala ustawiać większość parametrów na kilku poziomach, które nakładają się na siebie w ustalonej kolejności. Najniżej jest poziom całego serwera z pliku postgresql.conf oraz automatycznego pliku zmienianego przez ALTER SYSTEM. Wyżej stoją ustawienia przypisane do konkretnej bazy danych oraz do konkretnej roli, nadawane poleceniem ALTER DATABASE i ALTER ROLE. Jeszcze wyżej jest poziom pojedynczej sesji, gdzie działa polecenie SET, oraz poziom pojedynczej transakcji. Gdy łączysz się z bazą, serwer bierze wartość z pliku konfiguracyjnego i po kolei nadpisuje ją tym, co ustawiono dla tej bazy, dla Twojej roli, a na końcu tym, co ustawisz w sesji. Dlatego wartość widoczna w danym połączeniu to wynik tego nałożenia, a nie prosty odczyt jednego pliku. Do tego część parametrów ma ograniczenia, kiedy można je zmienić, bo niektóre wolno modyfikować tylko przy starcie serwera, a innych zwykły użytkownik nie zmieni bez odpowiednich uprawnień.

Jak to rozwiązać krok po kroku

1. Ustal zasięg zmiany. Zadaj sobie pytanie, czy parametr ma obowiązywać cały serwer, jedną bazę, jednego użytkownika czy tylko bieżącą pracę. Odpowiedź wskaże właściwy poziom.
2. Dla całego serwera edytuj plik postgresql.conf albo użyj polecenia ALTER SYSTEM SET, które zapisuje zmianę do osobnego pliku nadpisującego. Następnie przeładuj konfigurację lub wykonaj restart, zależnie od tego, czego dany parametr wymaga.
3. Dla jednej bazy użyj ALTER DATABASE nazwa SET parametr = wartość;. Ustawienie zadziała przy kolejnych połączeniach do tej bazy.
4. Dla jednego użytkownika użyj ALTER ROLE nazwa SET parametr = wartość;. To wygodne, gdy jedno konto, na przykład raportowe, potrzebuje innych ustawień niż reszta.
5. Dla bieżącej pracy użyj w sesji polecenia SET parametr = wartość;. Zmiana obowiązuje tylko do końca sesji, a jeśli opakujesz ją w SET LOCAL, tylko do końca transakcji.
6. Pamiętaj o pierwszeństwie: ustawienie sesji wygrywa z ustawieniem roli i bazy, a te wygrywają z plikiem serwera. Jeśli zmiana na niższym poziomie nie działa, sprawdź, czy nie przykrywa jej coś wyższego.

Jak sprawdzić, że zadziałało

Bieżącą wartość parametru w danym połączeniu sprawdzisz poleceniem SHOW parametr;. Żeby dowiedzieć się, z którego poziomu ta wartość pochodzi, zajrzyj do widoku pg_settings, gdzie kolumna source mówi, czy wartość wzięła się z pliku konfiguracyjnego, z bazy, z roli, czy z sesji. To najpewniejszy sposób, żeby zobaczyć, który poziom faktycznie zadziałał. Po ustawieniu parametru dla bazy albo roli otwórz nowe połączenie w odpowiednim kontekście i potwierdź poleceniem SHOW, że wartość jest już nowa, bo część ustawień działa dopiero od następnego połączenia, a nie w bieżącej sesji.

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