Serwer PostgreSQL jest konfigurowany za pomocą plików konfiguracyjnych. Te pliki można konfigurować bezpośrednio na poziomie systemu operacyjnego, ale coraz częściej takie podejście nie jest zalecane.
Np. zamiast modyfikować plik postgresql.conf można utworzyć nowy plik o nazwie postgresql.auto.conf i w nim umieścić przedefiniowane wartości parametrów. Jednak i to nie jest najlepsze rozwiązanie. Dodana w kolejnych wersjach posgresa komenda ALTER SYSTEM pozwala wprowadzać modyfikacje systemu z poziomu SQL:
ALTER SYSTEM SET max_connections = 130;
Dzięki temu ważne pliki konfiguracyjne pozostają w swojej oryginalnej postaci i co za tym idzie przechowują początkowe ustawienia. Modyfikacja plików wykonywana za pomocą komend pozwala unikać literówek, bądź wprowadzenia nonsensownych ustawień, które pozostają w konflikcie z innymi ustawieniami – polecenie ALTER SYSTEM chroni po prostu administratora przed niepoprawnymi modyfikacjami plików.
(Można też tworzyć własne pliki konfiguracyjne i dołączać je korzystając ze słowa include. Ta możliwość przyda się gdy modyfikujesz dużą liczbę parametrów).
Aktualne wartości parametrów można sprawdzić poleceniem
SELECT * FROM pg_settings
Tych ustawień jest całkiem sporo, dlatego aby zobaczyć te najważniejsze i najczęściej stosowane można uruchomić komendę:
SELECT
name,
context 1,
unit 2,
setting, boot_val, reset_val 3
FROM pg_settings
WHERE name IN ('listen_addresses','deadlock_timeout','shared_buffers',
'effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY context, name;
Jeśli zaobserwujesz różnice w kolumnie boot_val i reset_val, to oznacza to, że zmiany jeszcze nie obowiazują. W zależności od rodzaju parametru należy w takim przypadku przeładować konfigurację (reload) lub zrestartować serwer (restart).
Ustawienia mogą należeć do wielu kategorii. W przypadku niektórych ustawień, mogą one być zmieniane przez użytkownika dla bieżącej sesji. Zmiana innych ustawień musi być realizowana przez administratora. Zmiany w opcjach zaczynają obowiązywać po otwarciu nowych sesji. Na całe szczęście pliki konfiguracyjne zawierają dobry opis tych opcji włączając w to informacje o tym, czy zmiana danej opcji wymaga restartu serwera.
Niektóre z globalnie definiowanych opcji dotyczą tylko wybranych użytkowników lub baz danych. Pozwala to wyskalować ustawienia serwera tak, żeby kluczowe procesy mogły korzystać z większej puli zasobów.
Opcje serwera można też wyświetlać poleceniem SHOW:
SHOW shared_buffers; SHOW deadlock_timeout; SHOW ALL;
Zwróć uwagę, że w zależności od wyświetlanego parametru, jego jednostki mogą być inne!
Ponieważ konfiguracja może pochodzić z wielu plików, mamy do dyspozycji specjalny widok prezentujący ustawienia per-plik:
SELECT name, sourcefile, sourceline, setting, applied FROM pg_file_settings WHERE name = 'max_connections'
Więcej:
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Komentarze:
Pozwolę sobie na krótki komentarz niekoniecznie dotyczący Postgresa, choć wiem że autorzy blogów nie lubią gdy pisze się własne przemyślenia luźno związane z tematem posta. Dla mnie jako dla laika, choć hobbysty IT, właściwie to tylko MySQL jest bazą z którą mogę pobawić się na jakimś rozsądnym poziomie. Moją przygodę (czysto hobbystyczną) zacząłem od poznania SQLite, który był potrzebny w ramach kursu w którym brałem udział. Po nim, czyli po SQLite, przyszedł czas na „standard” dydaktyczny, czyli MySQL. Początkowo mnie nie zachwycił, więc zabrałem się za SQL Server a potem za Oracle, oba systemy w wersji XE. Są świetne, ale z perspektywy czasu stwierdzam, że jak dla hobbysty za trudne. Oczywiście nie myślę o podstawowym SQL-u, bo podstawy tego języka można poznać używając jakiegokolwiek RDBMS. Chodzi mi nieco bardziej zaawansowany SQL, w tym optymalizację zapytań, grzebanie w optymalizatorze i w ustawieniach serwera. W tym przypadku (jak dla mnie) również i Postgres jest za bardzo skomplikowany. Zatem wróciłem do MySQL, i stwierdzam że jest to optymalna baza do „gmerania” w niej hobbystycznie. Jest bardziej zaawansowana niż SQLite, ale nie tak skomplikowana jak np. Oracle. Działanie optymalizatora i konfiguracja serwera jest łatwiejsza, bardziej przejrzysta i zrozumiała. Pomocne w tym są liczne blogi i świetna dokumentacja. Chociaż akurat do dokumentacji Oracle i Postgresa nie można mieć słowa zarzutu. Co innego dokumentacja do SQL Server, która jest koszmarna, niekompletna, nieintuicyjna i pełna niedziałających linków. Podobnie byle jaka jest dokumentacja do MariaDB, która sprawia wrażenie jakby była pisana przez amatorów. Chociażby brak podziału dokumentacji na wersje, pod danym zagadnieniem jakieś dyskusje internautów. Zresztą uważam że sama MariaDB nie nadaje się do nauki chociażby optymalizacji zapytań, gdyż nie posiada wizualizacji planów w postaci drzewa ani szacowania kosztów operacji. Tutaj MySQL bije Marię na głowę.
Dzięki Krzysiek za podzielenie się uwagami. Każdy ma prawo coś lubić bardziej, a coś mniej, a jak za tym jeszcze idą konkretne argumenty to już całkiem super!