Administracja PostgreSQL po instalacji: użytkownik aplikacji, kopie zapasowe, VACUUM i wolne zapytania

Wersje PostgreSQL: wydania główne i poprawkowe, okres wsparcia i wersje w Debianie oraz Ubuntu

PostgreSQL ma dwa rodzaje wydań. Wydanie główne ukazuje się mniej więcej raz w roku, wprowadza nowe funkcje i może zmieniać format katalogu danych. Wydanie poprawkowe zawiera wyłącznie poprawki błędów i bezpieczeństwa, ukazuje się co najmniej raz na trzy miesiące i nie zmienia formatu danych. Od wersji 10 numer wydania głównego to pierwsza liczba: 17.11 to jedenaste wydanie poprawkowe PostgreSQL 17. Przed wersją 10 wydanie główne wyznaczały dwie pierwsze liczby, na przykład 9.6. Tak opisuje to polityka wersjonowania PostgreSQL (stan strony na 14 października 2026 r.).

Projekt wspiera każdą wersję główną przez 5 lat od pierwszego wydania i zaleca, żeby zawsze używać najnowszego wydania poprawkowego swojej wersji. Przejście z 17.10 na 17.11 to wymiana binarek i restart serwera. Przejście na nową wersję główną wymaga przeniesienia danych: zrzutu przez pg_dump i odtworzenia albo konwersji przez pg_upgrade, co pokazujemy w ostatniej części artykułu.

PostgreSQL 19 w dniu pisania nie jest jeszcze wydany. Według harmonogramu ogłoszonego 28 września 2026 r. na liście pgsql-hackers wersja RC1 (ang. Release Candidate) ma się ukazać 15 października, a wydanie finalne 29 października 2026 r.

Która wersja PostgreSQL jest w Debianie i Ubuntu

Dystrybucja trzyma jedną wersję główną PostgreSQL przez cały okres życia swojego wydania i dostarcza do niej tylko wydania poprawkowe. Dotyczy to także Ubuntu w wydaniach LTS (ang. Long Term Support). Wersje pakietów w tabeli odczytaliśmy przez apt-cache policy na maszynach labowych 14 października 2026 r.

Na Ubuntu 22.04 jest PostgreSQL 14, którego wsparcie przez projekt kończy się 12 listopada 2026 r. Nowsze wersje główne na każdy z tych systemów daje repozytorium PGDG prowadzone przez projekt PostgreSQL pod adresem apt.postgresql.org. W dniu testu miało ono gałęzie między innymi dla bookworm, trixie, jammy, noble i resolute. Dodanie tego repozytorium ma efekt uboczny, który opisujemy w części o aktualizacji.

Instalacja PostgreSQL na Debianie 13: pakiety, klaster 17/main, katalogi i usługa systemd

Każde polecenie z tego artykułu wykonaliśmy 14 października 2026 r. na maszynach wirtualnych KVM (ang. Kernel-based Virtual Machine) w naszym labie, który opisuje artykuł Lab na osobnym hoście: od czystego Debiana 13 do jedenastu maszyn z Terraform. Każda maszyna ma 2 wirtualne procesory, 2 GB RAM i dysk 10 GB:

• Debian 13.7, jądro 6.12.111+deb13-cloud-amd64, PostgreSQL 17.11 (pakiet postgresql-17 w wersji 17.11-0+deb13u1, postgresql-common 278); na tej maszynie testowaliśmy też aktualizację do PostgreSQL 18.6 z repozytorium PGDG (pakiet 18.6-1.pgdg13+2);
• Ubuntu 26.04.1 LTS, jądro 7.0.0-38-generic, PostgreSQL 18.6 (pakiet postgresql-18 w wersji 18.6-0ubuntu0.26.04.1, postgresql-common 290ubuntu1).

Polecenia pokazujemy dla Debiana 13. Tam, gdzie PostgreSQL 18 zachowuje się inaczej, piszemy to wprost. Linki do dokumentacji prowadzą do wersji 17 na postgresql.org, która w dniu pisania odpowiada wydaniu 17.11, a przy nowościach wersji 18 do informacji o wydaniu PostgreSQL 18.

Polecenia wykonujemy na serwerze bazy, jako root. Plikami bazy zarządza systemowe konto postgres i tylko ono może zaraz po instalacji zalogować się do PostgreSQL bez hasła. Polecenia, które muszą działać jako postgres, poprzedzamy sudo -u postgres: root uruchamia wtedy program z uprawnieniami tego konta. Wymaga to pakietu sudo; bez niego to samo robi runuser -u postgres --. W blokach z wynikiem znak # na początku linii oznacza polecenie roota w powłoce, a znak zachęty psql to nazwa bazy, po której stoi =# dla superużytkownika albo => dla zwykłej roli: postgres=# to rola postgres w bazie postgres, sklep=> to rola sklep w bazie sklep.

Instalacja pakietu postgresql i pierwszy klaster 17/main

apt install postgresql

Pakiet postgresql to metapakiet: na Debianie 13 instaluje postgresql-17, postgresql-client-17, postgresql-common i ich zależności. Skrypt instalacyjny od razu tworzy i uruchamia klaster 17/main. W wyniku instalacji na Debianie 13 pojawia się zdanie Data page checksums are disabled., bo PostgreSQL 17 tworzy klaster bez sum kontrolnych stron danych. PostgreSQL 18 włącza je domyślnie i na Ubuntu 26.04 SHOW data_checksums; zwraca on.

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

Klaster to jeden serwer PostgreSQL: jeden proces nadrzędny, jeden port i jeden katalog danych, w którym może być wiele baz. Pakiet postgresql-common w Debianie i Ubuntu nazywa klaster parą wersja/nazwa i pozwala trzymać obok siebie kilka klastrów, także w różnych wersjach głównych. Do zarządzania nimi służą pg_lsclusters, pg_createcluster, pg_ctlcluster i pg_dropcluster.

Po instalacji serwer nasłuchuje tylko na localhost, na porcie 5432, z włączonym szyfrowaniem połączeń TLS (ang. Transport Layer Security), które PostgreSQL w nazwach parametrów nazywa jeszcze SSL (ang. Secure Sockets Layer). Certyfikat to samopodpisany ssl-cert-snakeoil.pem: połączenie jest szyfrowane, ale klient nie może na jego podstawie potwierdzić tożsamości serwera.

Polecenia psql, pg_dump, vacuumdb czy pgbench w /usr/bin to dowiązania do skryptu pg_wrapper, który dobiera wersję programu do klastra. Część narzędzi nie ma takich dowiązań, między innymi pg_ctl, initdb, pg_upgrade, pg_verifybackup i pg_combinebackup. Wywołujemy je pełną ścieżką, na przykład /usr/lib/postgresql/17/bin/pg_verifybackup.

Praca w psql: metapolecenia \l+, \dt+, \d+, \du, \dx i \x

psql to konsolowy klient PostgreSQL. Jako root łączymy się przez gniazdo uniksowe na konto postgres:

sudo -u postgres psql

Takie połączenie przechodzi bez hasła dzięki metodzie peer w pg_hba.conf: PostgreSQL pyta jądro, które konto systemowe jest po drugiej stronie gniazda, i wpuszcza je na rolę o tej samej nazwie. Polecenia zaczynające się od ukośnika wstecznego to metapolecenia. Wykonuje je psql, a nie serwer, zwykle zamieniając je na zapytania do katalogu systemowego. \? wypisuje wszystkie metapolecenia, a \h POLECENIE składnię polecenia SQL razem z adresem strony dokumentacji:

postgres=# \h VACUUM
Command:     VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
[...]
URL: https://www.postgresql.org/docs/17/sql-vacuum.html

Według dokumentacji psql znak + w \l+ dodaje rozmiar bazy, domyślny tablespace i opis, a w \dt+ i \di+ trwałość obiektu, jego fizyczny rozmiar na dysku i opis. Tak wygląda \l+ na serwerze z bazą sklepu, której używamy w dalszej części artykułu:

postgres=# \l+
                                                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+---------+---------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |                       | 7710 kB | pg_default | default administrative connection database
 sklep     | sklep    | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | sklep=CTc/sklep      +| 674 MB  | pg_default |
           |          |          |                 |         |         |        |           | raport=c/sklep        |         |            |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +| 7353 kB | pg_default | unmodifiable empty database
           |          |          |                 |         |         |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +| 7654 kB | pg_default | default template for new databases
           |          |          |                 |         |         |        |           | postgres=CTc/postgres |         |            |
(4 rows)

Rozmiar bazy widzi tylko rola, która ma do niej prawo CONNECT. Rola bez tego prawa dostaje w kolumnie Size wartość No Access; pokazujemy to w części o uprawnieniach. Kolumna Access privileges zapisuje uprawnienia w skrócie: sklep=CTc/sklep oznacza, że rola sklep ma prawo tworzenia schematów (C), tabel tymczasowych (T) i połączenia (c), a nadała je rola sklep. Pusta kolumna oznacza uprawnienia domyślne.

\dt+ i \di+ w bazie sklepu zaraz po załadowaniu danych testowych:

sklep=> \dt+
                                     List of relations
 Schema |    Name     | Type  | Owner | Persistence | Access method |  Size  | Description
--------+-------------+-------+-------+-------------+---------------+--------+-------------
 public | customers   | table | sklep | permanent   | heap          | 16 MB  |
 public | order_items | table | sklep | permanent   | heap          | 391 MB |
 public | orders      | table | sklep | permanent   | heap          | 240 MB |
(3 rows)

sklep=> \di+
                                                List of relations
 Schema |        Name         | Type  | Owner |    Table    | Persistence | Access method |  Size   | Description
--------+---------------------+-------+-------+-------------+-------------+---------------+---------+-------------
 public | customers_email_key | index | sklep | customers   | permanent   | btree         | 15 MB   |
 public | customers_pkey      | index | sklep | customers   | permanent   | btree         | 4408 kB |
 public | order_items_pkey    | index | sklep | order_items | permanent   | btree         | 129 MB  |
 public | orders_pkey         | index | sklep | orders      | permanent   | btree         | 43 MB   |
(4 rows)

Rozmiar w \dt+ dotyczy samej tabeli, bez indeksów. Łączny rozmiar tabeli z indeksami podaje funkcja pg_total_relation_size(), a samych indeksów pg_indexes_size(); obie zwracają bajty, które pg_size_pretty() zamienia na czytelne jednostki.

\conninfo od razu pokazuje, czy połączenie idzie przez gniazdo, czy przez TCP, i czy jest szyfrowane:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

sklep=> \conninfo
You are connected to database "sklep" as user "sklep" on host "127.0.0.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)

psql 18 wypisuje \conninfo jako tabelę z kilkunastoma wierszami, między innymi SSL Protocol, Password Used i Superuser. Przy wierszu, który nie mieści się na ekranie, pomaga \x:

sklep=> \x on
Expanded display is on.
sklep=> SELECT * FROM orders WHERE id = 1000000;
-[ RECORD 1 ]-----------------------------------------------------
id          | 1000000
customer_id | 189418
status      | shipped
created_at  | 2025-04-13 10:53:20+00
total       | 710.74
note        | Uwagi do zamowienia 8155bc545f84d9652f1012ef2bdfb6eb

psql w skryptach: -c, -f, \watch i plik .psqlrc

Opcja -c przyjmuje albo samo zapytanie SQL, albo jedno metapolecenie. Dokumentacja psql mówi wprost, że w jednym -c nie da się mieszać SQL i metapoleceń. Próba psql -c "SELECT ... \watch i=1 c=2" kończy się błędem składni syntax error at or near "\". Zapytanie z \watch zapisujemy więc do pliku i uruchamiamy przez -f:

# cat /tmp/w.sql
SELECT count(*) AS polaczenia FROM pg_stat_activity WHERE backend_type = 'client backend' \watch i=1 c=2
# sudo -u postgres psql -X -f /tmp/w.sql
Wed Oct 14 11:15:39 2026 (every 1s)

 polaczenia
------------
          1
(1 row)

Wed Oct 14 11:15:40 2026 (every 1s)

 polaczenia
------------
          1
(1 row)

Ustawienia, które chcemy mieć w każdej sesji, wpisujemy do pliku ~/.psqlrc konta, z którego uruchamiamy psql. Dla konta postgres na Debianie to /var/lib/postgresql/.psqlrc. Opcja -X pomija ten plik, dlatego używamy jej w skryptach, które mają działać tak samo na każdym serwerze:

\set QUIET 1
\x auto
\timing on
\pset null (null)
\set HISTCONTROL ignoredups
\set PROMPT1 '%n@%/%R%# '
\unset QUIET

Z takim plikiem psql pokazuje wartości NULL jako (null) zamiast pustego pola i po każdym zapytaniu wypisuje czas, np. Time: 0.520 ms. \x auto przełącza się na układ rekordowy tylko wtedy, gdy wynik jest szerszy niż ekran.

Rola i baza danych dla aplikacji: CREATE ROLE, \password, CREATE DATABASE i uprawnienia

W PostgreSQL użytkownik i grupa to ten sam rodzaj obiektu: rola. Użytkownikiem nazywamy rolę z atrybutem LOGIN. Rola, która jest właścicielem bazy (OWNER), ma pełne prawa do swoich obiektów w tej bazie i nie potrzebuje uprawnień superużytkownika. Aplikacja nigdy nie powinna łączyć się jako postgres.

Tworzenie roli i bazy oraz hasło ustawiane przez \password

Na serwerze bazy, jako root, otwieramy psql jako postgres. Nazwę sklep zastępujemy nazwą swojej aplikacji:

sudo -u postgres psql

CREATE ROLE sklep LOGIN;
\password sklep
CREATE DATABASE sklep OWNER sklep;
REVOKE CONNECT, TEMPORARY ON DATABASE sklep FROM PUBLIC;

\password pyta o hasło dwa razy, samo liczy jego skrót i wysyła do serwera gotowe ALTER ROLE. Według dokumentacji psql dzięki temu hasło nie trafia jawnym tekstem do historii poleceń ani do logu serwera. Sprawdziliśmy to z ustawionym w tej sesji log_statement = all: w logu zostało ALTER USER "sklep" PASSWORD 'SCRAM-SHA-256$4096:...', a jawne hasło nie pojawiło się ani w logu, ani w ~/.psql_history. Hasło wpisane wprost w CREATE ROLE ... PASSWORD '...' według dokumentacji CREATE ROLE przechodzi do serwera jawnym tekstem i może zostać w historii poleceń albo w logu. Z powłoki to samo, co \password, robi createuser --pwprompt, który według tej samej strony wysyła hasło zaszyfrowane.

Serwer przechowuje skrót hasła w formacie SCRAM-SHA-256 (SCRAM, ang. Salted Challenge Response Authentication Mechanism), bo parametr password_encryption ma domyślnie wartość scram-sha-256 w obu testowanych wersjach. PostgreSQL 18 przy ustawianiu hasła w starym formacie MD5 wypisuje ostrzeżenie MD5 password support is deprecated and will be removed in a future release of PostgreSQL.

Nowa baza dostaje uprawnienia domyślne: pseudorola PUBLIC, czyli każda rola w klastrze, może się do niej połączyć (CONNECT) i tworzyć w niej tabele tymczasowe (TEMPORARY). W labie rola raport, która nie ma z bazą sklepu nic wspólnego, połączyła się do niej bez przeszkód. Po REVOKE dostaje błąd:

psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  permission denied for database "sklep"
DETAIL:  User does not have CONNECT privilege.

W kolumnie Access privileges w \l sklep zostaje wtedy tylko sklep=CTc/sklep, a rola raport w \l+ widzi przy bazie sklepu rozmiar No Access.

Od PostgreSQL 15 schemat public w nowej bazie należy do pseudoroli pg_database_owner, czyli do właściciela bazy, a pozostałe role mają w nim tylko prawo USAGE (U), bez prawa tworzenia obiektów (C):

sklep=# \dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(1 row)

Rola sklep jako właściciel bazy tworzy więc tabele w public, a inne role nie: CREATE TABLE uruchomione przez rolę raport kończy się błędem permission denied for schema public.

Rola tylko do odczytu dla raportów: GRANT SELECT i ALTER DEFAULT PRIVILEGES

Narzędzie raportowe łączy się osobną rolą, która może tylko czytać. Prawo połączenia do bazy nadajemy jako postgres, a prawa do tabel jako ich właściciel, czyli rola sklep:

-- jako postgres
GRANT CONNECT ON DATABASE sklep TO raport;

-- jako sklep, właściciel tabel
GRANT USAGE ON SCHEMA public TO raport;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO raport;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO raport;

GRANT SELECT ON ALL TABLES obejmuje tylko tabele, które istnieją w chwili wykonania. Tabele tworzone później, na przykład przez migracje aplikacji, obejmuje ALTER DEFAULT PRIVILEGES: dotyczy obiektów, które w przyszłości utworzy rola wykonująca to polecenie. W labie rola raport odczytała 200 000 wierszy z customers, jej INSERT skończył się błędem permission denied for table customers, a tabelę coupons, którą rola sklep utworzyła już po nadaniu uprawnień, odczytała bez dodatkowego GRANT:

sklep=> \ddp
         Default access privileges
 Owner | Schema | Type  | Access privileges
-------+--------+-------+-------------------
 sklep | public | table | raport=r/sklep
(1 row)

Uwierzytelnianie w pg_hba.conf: peer, scram-sha-256 i kolejność reguł

Plik /etc/postgresql/17/main/pg_hba.conf decyduje, kto i jak może się połączyć. Po instalacji na Debianie 13 zawiera takie reguły, tu bez komentarzy:

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Kolumny to typ połączenia (local przez gniazdo uniksowe, host przez TCP), baza, rola, adres klienta i metoda uwierzytelniania. Według dokumentacji pg_hba.conf serwer sprawdza reguły po kolei i używa pierwszej, która pasuje do typu połączenia, adresu klienta, bazy i roli. Nie ma przechodzenia do następnej reguły: jeśli wybrana reguła odrzuci uwierzytelnienie, kolejne nie są sprawdzane, a gdy nie pasuje żadna, połączenie jest odrzucane.

Metoda peer przepuszcza przez gniazdo tylko konto systemowe o tej samej nazwie co rola. Aplikacja PHP działająca jako www-data, która łączy się przez gniazdo na rolę sklep, dostaje więc:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "sklep"

Aplikacja na tym samym serwerze łączy się zatem przez TCP na 127.0.0.1 z hasłem i pasuje do niej reguła host all all 127.0.0.1/32 scram-sha-256. W labie takie połączenie było szyfrowane mimo adresu lokalnego (\conninfo pokazał TLSv1.3), bo klient domyślnie używa TLS, gdy serwer go oferuje.

Dostęp z serwera aplikacji: listen_addresses, reguła hostssl i test połączenia

Gdy aplikacja działa na innym serwerze, PostgreSQL musi nasłuchiwać na adresie sieciowym, a pg_hba.conf musi wpuścić ten konkretny adres. W labie serwer bazy ma adres 192.168.123.7, a serwer aplikacji 192.168.123.148; zastępujemy je własnymi. Na serwerze bazy, jako root:

cat > /etc/postgresql/17/main/conf.d/05-siec.conf <<'EOF'
listen_addresses = 'localhost,192.168.123.7'
EOF
chown postgres:postgres /etc/postgresql/17/main/conf.d/05-siec.conf

W /etc/postgresql/17/main/pg_hba.conf dopisujemy regułę nad regułami domyślnymi:

# Aplikacja sklep z serwera aplikacji, tylko szyfrowane połączenia
hostssl sklep           sklep           192.168.123.148/32      scram-sha-256

Typ hostssl pasuje tylko do połączeń TCP z TLS. Plik sprawdzamy przed przeładowaniem w widoku pg_hba_file_rules; według dokumentacji wiersz z niepustą kolumną error wskazuje linię z błędem:

postgres=# SELECT line_number, type, database, user_name, address, netmask, auth_method, error
           FROM pg_hba_file_rules ORDER BY line_number;
 line_number |  type   |   database    | user_name  |     address     |                 netmask                 |  auth_method  | error
-------------+---------+---------------+------------+-----------------+-----------------------------------------+---------------+-------
         112 | hostssl | {sklep}       | {sklep}    | 192.168.123.148 | 255.255.255.255                         | scram-sha-256 |
         121 | local   | {all}         | {postgres} |                 |                                         | peer          |
         126 | local   | {all}         | {all}      |                 |                                         | peer          |
         128 | host    | {all}         | {all}      | 127.0.0.1       | 255.255.255.255                         | scram-sha-256 |
[...]

Zmiana listen_addresses wymaga restartu serwera; gdyby zmieniał się tylko pg_hba.conf, wystarczyłoby systemctl reload postgresql@17-main. Po restarcie serwer nasłuchuje na obu adresach:

# systemctl restart postgresql@17-main
# ss -ltnp | grep 5432
LISTEN 0      200    192.168.123.7:5432      0.0.0.0:*    users:(("postgres",pid=149932,fd=8))
LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=149932,fd=7))
LISTEN 0      200            [::1]:5432         [::]:*    users:(("postgres",pid=149932,fd=6))

Test wykonujemy z serwera aplikacji, w labie z Ubuntu 26.04 i psql 18.6. Hasło psql pobiera z pytania albo z pliku ~/.pgpass:

$ psql "host=192.168.123.7 dbname=sklep user=sklep sslmode=require" \
       -c "SELECT inet_client_addr(), (SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid())"
 inet_client_addr | ssl
------------------+-----
 192.168.123.148  | t
(1 row)

$ psql "host=192.168.123.7 dbname=sklep user=sklep sslmode=disable" -c "SELECT 1"
psql: error: connection to server at "192.168.123.7", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.123.148", user "sklep", database "sklep", no encryption

$ psql "host=192.168.123.7 dbname=postgres user=sklep sslmode=require" -c "SELECT 1"
psql: error: connection to server at "192.168.123.7", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.123.148", user "sklep", database "postgres", SSL encryption

Połączenie bez szyfrowania i połączenie do innej bazy serwer odrzucił, bo nie pasuje do nich żadna reguła. Parametr sslmode po stronie klienta decyduje, co klient sprawdza. Według tabeli trybów SSL w dokumentacji libpq require szyfruje połączenie, ale nie chroni przed podszyciem się pod serwer, a verify-full sprawdza też certyfikat i nazwę serwera. Domyślne prefer dokumentacja opisuje jako tryb, który z punktu widzenia bezpieczeństwa nie ma sensu i istnieje dla zgodności wstecznej. Samopodpisany certyfikat snakeoil wystarcza tylko do require; do verify-full potrzebny jest certyfikat z nazwą serwera, wystawiony przez urząd certyfikacji, któremu ufa klient.

Port 5432 otwieramy w zaporze tylko dla adresu serwera aplikacji. Jak zbudować i bezpiecznie wdrożyć taki zestaw reguł, opisuje artykuł Z iptables na nftables na Debianie 13: bezpieczna migracja firewalla.

Konfiguracja PostgreSQL po instalacji: pamięć, logowanie wolnych zapytań i pliki w conf.d

Debianowy postgresql.conf zawiera pod koniec linię include_dir = 'conf.d'. Według dokumentacji ustawień pliki *.conf z tego katalogu są czytane w kolejności nazw, a gdy ten sam parametr pojawia się kilka razy, obowiązuje ostatnia wartość. Własne ustawienia trzymamy więc w osobnych, numerowanych plikach i nie zmieniamy postgresql.conf z pakietu: łatwiej je wtedy porównać i wdrożyć, na przykład przez Ansible. Drugi mechanizm, ALTER SYSTEM, zapisuje ustawienia do pliku postgresql.auto.conf w katalogu danych; ten plik jest czytany razem z postgresql.conf, a jego wartości mają pierwszeństwo. W części o aktualizacji pokazujemy, że ta różnica ma znaczenie przy przejściu na nową wersję główną.

Pamięć PostgreSQL: shared_buffers, effective_cache_size, work_mem i maintenance_work_mem

Na serwerze bazy, jako root, zapisujemy ustawienia pamięci. Wartości dobraliśmy do maszyny z 2 GB RAM, na której działa tylko PostgreSQL; jak je skalować, opisujemy pod przykładem:

cat > /etc/postgresql/17/main/conf.d/10-pamiec.conf <<'EOF'
# Pamięć i planer: serwer z 2 GB RAM
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 8MB
maintenance_work_mem = 128MB
random_page_cost = 1.1
EOF
chown postgres:postgres /etc/postgresql/17/main/conf.d/10-pamiec.conf

• shared_buffers to pamięć podręczna PostgreSQL wspólna dla wszystkich połączeń; domyślnie 128 MB. Dokumentacja parametrów zasobów podaje 25% RAM jako rozsądny punkt wyjścia na dedykowanym serwerze z co najmniej 1 GB RAM i zaznacza, że przydział powyżej 40% RAM raczej nie poprawi wydajności, bo PostgreSQL korzysta też z pamięci podręcznej systemu. Zmiana wymaga restartu.
• effective_cache_size to tylko szacunek dla planera, ile pamięci podręcznej ma do dyspozycji jedno zapytanie. Według dokumentacji planera parametr niczego nie rezerwuje, a wyższa wartość zwiększa szansę, że planer wybierze indeks. Domyślne 4 GB to więcej niż RAM tej maszyny, więc ustawiamy 1536 MB: shared_buffers plus pamięć, którą system miał wolną na pamięć podręczną (przed instalacją PostgreSQL około 1,2 GB).
• work_mem ogranicza pamięć jednej operacji sortowania albo haszowania, zanim zacznie ona zapisywać pliki tymczasowe; domyślnie 4 MB. Dokumentacja ostrzega, że jedno złożone zapytanie wykonuje kilka takich operacji naraz, a kilka sesji robi to równolegle, więc łączne zużycie może wielokrotnie przekroczyć work_mem. Globalnie podnosimy go tylko do 8 MB; większą wartość dajemy pojedynczej roli, na przykład raportowej, przez ALTER ROLE ... SET work_mem.
• maintenance_work_mem to pamięć dla VACUUM, CREATE INDEX i ALTER TABLE ADD FOREIGN KEY; domyślnie 64 MB. Dokumentacja przypomina, że autovacuum może przydzielić ją autovacuum_max_workers razy, czyli przy domyślnych 3 procesach do 384 MB przy naszej wartości.
• random_page_cost to koszt odczytu losowej strony, którym planer porównuje skan indeksu ze skanem sekwencyjnym; domyślnie 4. Według dokumentacji obniżenie może być właściwe, gdy dane prawdopodobnie mieszczą się w całości w pamięci podręcznej, na przykład gdy baza jest mniejsza niż RAM serwera. Nasza baza testowa ma około 700 MB i leży na dysku SSD (ang. Solid State Drive) hosta, dlatego ustawiamy 1.1, blisko kosztu odczytu sekwencyjnego (1).

Logowanie wolnych zapytań, oczekiwania na blokady i pracy autovacuum

cat > /etc/postgresql/17/main/conf.d/20-logi.conf <<'EOF'
log_min_duration_statement = 200ms
log_autovacuum_min_duration = 0
log_lock_waits = on
log_temp_files = 0
track_io_timing = on
EOF
chown postgres:postgres /etc/postgresql/17/main/conf.d/20-logi.conf

• log_min_duration_statement zapisuje w logu każde zapytanie dłuższe niż próg, razem z czasem wykonania; domyślnie -1, czyli wyłączone. W labie ustawiamy 200 ms, bo testowe zapytania trwały od 0,4 do 1,3 s. Na produkcji próg dobieramy tak, żeby log dało się przeczytać, a nie tylko zapisać.
• log_autovacuum_min_duration = 0 loguje każde uruchomienie autovacuum ze szczegółami: ile martwych wierszy usunął, ile stron przeczytał i ile trwał. W PostgreSQL 17 i 18 domyślnie jest to 10 minut, więc w logu widać tylko bardzo długie przebiegi.
• log_lock_waits = on zapisuje sesje, które czekają na blokadę dłużej niż deadlock_timeout. W obu testowanych wersjach domyślnie jest wyłączony. W logu wygląda to tak: process 146530 still waiting for AccessExclusiveLock on relation 16403 of database 16390 after 1000.162 ms.
• log_temp_files = 0 zapisuje każdy plik tymczasowy z jego rozmiarem; to sygnał, że zapytaniu nie wystarczyło work_mem.
• track_io_timing = on mierzy czas operacji wejścia-wyjścia (I/O) i pokazuje go w EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements i VACUUM (VERBOSE). Dokumentacja wyjaśnia, że parametr jest domyślnie wyłączony, bo na niektórych platformach częste odczyty zegara są kosztowne, i odsyła do narzędzia pg_test_timing.

# sudo -u postgres /usr/lib/postgresql/17/bin/pg_test_timing -d 3
Testing timing overhead for 3 seconds.
Per loop time including overhead: 38.27 ns
Histogram of timing durations:
  < us   % of total      count
     1     96.19832   75411912
     2      3.79961    2978597
     4      0.00006         50
[...]

Według dokumentacji pg_test_timing dobry wynik to ponad 90% pomiarów poniżej 1 µs i średni koszt pętli poniżej 100 ns. Maszyna labowa ma 96,2% i 38,27 ns, więc track_io_timing możemy na niej włączyć.

Które parametry wymagają restartu: kolumna context i pending_restart

Pliki z conf.d serwer wczytuje przy przeładowaniu konfiguracji (systemctl reload postgresql@17-main albo SELECT pg_reload_conf();), ale nie każdy parametr da się zmienić bez restartu. Mówi o tym kolumna context widoku pg_settings, a kolumna pending_restart pokazuje parametry zmienione w plikach, które czekają na restart. W labie zmieniliśmy shared_buffers z 512 MB na 384 MB i przeładowaliśmy konfigurację:

postgres=# SELECT name, setting, unit, context, pending_restart FROM pg_settings
           WHERE name IN ('shared_buffers', 'work_mem', 'log_min_duration_statement') ORDER BY name;
            name            | setting | unit |  context   | pending_restart
----------------------------+---------+------+------------+-----------------
 log_min_duration_statement | 200     | ms   | superuser  | f
 shared_buffers             | 65536   | 8kB  | postmaster | t
 work_mem                   | 8192    | kB   | user       | f
(3 rows)

postmaster oznacza parametr ustawiany tylko przy starcie serwera; shared_buffers zostaje na 65536 stron po 8 kB, czyli 512 MB, do najbliższego restartu. sighup to parametr zmieniany przeładowaniem. user i superuser można dodatkowo ustawić w sesji przez SET albo przypisać roli przez ALTER ROLE ... SET; superuser zmienia tylko superużytkownik. Po zapisaniu plików z tej części restartujemy klaster, bo zmienia się shared_buffers:

systemctl restart postgresql@17-main

Literówka w conf.d: przeładowanie nie stosuje żadnej zmiany, a restart zatrzymuje klaster

Pierwsze przeładowanie z nowymi plikami zrobiliśmy w labie celowo z dodatkowym plikiem 99-literowka.conf, w którym parametr nazywał się shared_bufers. Log serwera:

2026-10-14 10:44:31.167 UTC [144267] LOG:  received SIGHUP, reloading configuration files
2026-10-14 10:44:31.167 UTC [144267] LOG:  unrecognized configuration parameter "shared_bufers" in file "/etc/postgresql/17/main/conf.d/99-literowka.conf" line 1
2026-10-14 10:44:31.167 UTC [144267] LOG:  configuration file "/etc/postgresql/17/main/conf.d/99-literowka.conf" contains errors; no changes were applied

Jedna błędna linia zablokowała wszystkie zmiany, także work_mem i log_min_duration_statement, które nie wymagają restartu: w pg_settings nie było ani jednej wartości z katalogu conf.d. systemctl reload nie zgłosił przy tym żadnego błędu. Restart z tym samym plikiem skończył się zatrzymaniem klastra:

# systemctl restart postgresql@17-main
Job for postgresql@17-main.service failed because the service did not take the steps required by its unit configuration.
[...]
# journalctl -u postgresql@17-main -n 8 --no-pager -o cat
Error: /usr/lib/postgresql/17/bin/pg_ctl /usr/lib/postgresql/17/bin/pg_ctl start -D /var/lib/postgresql/17/main -l /var/log/postgresql/postgresql-17-main.log -s -o  -c config_file="/etc/postgresql/17/main/postgresql.conf"  exited with status 1:
2026-10-14 10:44:45.566 GMT [144694] LOG:  unrecognized configuration parameter "shared_bufers" in file "/etc/postgresql/17/main/conf.d/99-literowka.conf" line 1
2026-10-14 10:44:45.566 GMT [144694] FATAL:  configuration file "/etc/postgresql/17/main/conf.d/99-literowka.conf" contains errors
pg_ctl: could not start server
Examine the log output.
postgresql@17-main.service: Can't open PID file '/run/postgresql/17-main.pid' (yet?) after start: No such file or directory
postgresql@17-main.service: Failed with result 'protocol'.
Failed to start postgresql@17-main.service - PostgreSQL Cluster 17-main.
# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
17  main    5432 down   postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

Przed każdym restartem sprawdzamy więc pliki w widoku pg_file_settings. Dokumentacja funkcji administracyjnych wskazuje go razem z pg_hba_file_rules jako sposób na sprawdzenie plików konfiguracji przed przeładowaniem:

SELECT sourcefile, sourceline, name, setting, error
FROM pg_file_settings
WHERE error IS NOT NULL;

W labie zapytanie wskazało plik 99-literowka.conf, linię 1 i błąd unrecognized configuration parameter. Ten sam widok pokazuje też parametry poprawne, które czekają na restart, z błędem setting could not be applied. To nie jest błąd w pliku; przykład jest w części o aktualizacji.

Domyślne wartości zmienione w PostgreSQL 18

Porównaliśmy pg_settings na świeżych klastrach 17.11 (Debian 13) i 18.6 (Ubuntu 26.04). Różnice w parametrach, o których mowa w tym artykule, potwierdzają informacje o wydaniu PostgreSQL 18:

io_method = worker włącza w PostgreSQL 18 asynchroniczne operacje wejścia-wyjścia (AIO, ang. asynchronous I/O): proces obsługujący zapytanie może zlecić kilka odczytów naraz, co według informacji o wydaniu przyspiesza skany sekwencyjne, skany bitmapowe i VACUUM. log_connections przyjmuje teraz listę etapów połączenia do zalogowania, a dawne on i off nadal działają.

Jak znaleźć zapytania, które obciążają PostgreSQL: pg_stat_activity, pg_stat_statements i log wolnych zapytań

Baza testowa sklepu i obciążenie z pgbench

Wszystkie mechanizmy z dalszej części pokazujemy na jednej bazie sklepu: 200 000 klientów, 2 000 000 zamówień z trzech lat i 6 000 000 pozycji zamówień. Tabele należą do roli sklep:

CREATE TABLE customers (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    name       text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    status      text NOT NULL,
    created_at  timestamptz NOT NULL,
    total       numeric(12,2) NOT NULL,
    note        text
);
CREATE TABLE order_items (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders (id),
    sku      text NOT NULL,
    qty      integer NOT NULL,
    price    numeric(12,2) NOT NULL
);

Dane wygenerowaliśmy funkcją generate_series(). Po załadowaniu orders zajmuje 240 MB, a order_items 391 MB. Indeksy mają tylko klucze główne i unikalny adres e-mail; kolumny orders.customer_id i order_items.order_id są kluczami obcymi bez indeksów. To celowy błąd projektu, który znajdziemy narzędziami opisanymi niżej.

Ruch aplikacji symuluje pgbench. Według dokumentacji pgbench to program, który wielokrotnie wykonuje ten sam zestaw poleceń SQL, także w wielu równoległych sesjach, i liczy średnią liczbę transakcji na sekundę (tps). Zamiast wbudowanego scenariusza podajemy mu trzy własne skrypty: historię zamówień klienta, pozycje jednego zamówienia i zmianę statusu zamówienia:

# cat historia.sql
\set cid random(1, 200000)
SELECT id, status, total, created_at
FROM orders
WHERE customer_id = :cid
ORDER BY created_at DESC
LIMIT 20;
# cat pozycje.sql
\set oid random(1, 2000000)
SELECT sku, qty, price FROM order_items WHERE order_id = :oid;
# cat status.sql
\set oid random(1, 2000000)
UPDATE orders SET status = 'shipped' WHERE id = :oid AND status = 'paid';

Test trwa 60 sekund w 4 połączeniach (-c 4) obsługiwanych przez 2 wątki (-j 2). Waga po znaku @ ustala, jak często pgbench losuje dany skrypt, a -n pomija czyszczenie standardowych tabel pgbench, których w tej bazie nie ma:

# pgbench -h 127.0.0.1 -U sklep -n -c 4 -j 2 -T 60 -P 10 \
          -f historia.sql@5 -f pozycje.sql@5 -f status.sql@1 sklep
[...]
number of transactions actually processed: 307
number of failed transactions: 0 (0.000%)
latency average = 788.105 ms
latency stddev = 516.333 ms
initial connection time = 51.457 ms
tps = 5.050161 (without initial connection time)
SQL script 1: historia.sql
 - weight: 5 (targets 45.5% of total)
 - 141 transactions (45.9% of total, tps = 2.319455)
 - number of failed transactions: 0 (0.000%)
 - latency average = 499.404 ms
 - latency stddev = 71.327 ms
SQL script 2: pozycje.sql
 - weight: 5 (targets 45.5% of total)
 - 128 transactions (41.7% of total, tps = 2.105604)
 - number of failed transactions: 0 (0.000%)
 - latency average = 1338.126 ms
 - latency stddev = 241.153 ms
SQL script 3: status.sql
 - weight: 1 (targets 9.1% of total)
 - 38 transactions (12.4% of total, tps = 0.625101)
 - number of failed transactions: 0 (0.000%)
 - latency average = 6.636 ms
 - latency stddev = 9.503 ms

Baza obsłużyła 5,05 transakcji na sekundę. Historia zamówień klienta trwała średnio 499 ms, pozycje jednego zamówienia 1338 ms, a zmiana statusu po kluczu głównym 6,6 ms. Średnie obciążenie systemu (load average) wynosiło 4,25 przy 2 procesorach.

Co baza robi w tej chwili: pg_stat_activity i procesy postgres

Widok pg_stat_activity ma jeden wiersz na każdy proces serwera. W czasie testu, jako postgres:

SELECT pid, usename, client_addr, state,
       wait_event_type, wait_event,
       round(extract(epoch FROM clock_timestamp() - query_start)::numeric, 2) AS sek,
       left(regexp_replace(query, '\s+', ' ', 'g'), 45) AS zapytanie
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

  pid   | usename | client_addr | state  | wait_event_type |  wait_event   | sek  |                   zapytanie
--------+---------+-------------+--------+-----------------+---------------+------+-----------------------------------------------
 153032 | sklep   | 127.0.0.1   | active |                 |               | 0.39 | SELECT sku, qty, price FROM order_items WHERE
 153035 | sklep   | 127.0.0.1   | active | LWLock          | BufferMapping | 0.30 | SELECT id, status, total, created_at FROM ord
 153034 | sklep   | 127.0.0.1   | active | LWLock          | BufferMapping | 0.20 | SELECT sku, qty, price FROM order_items WHERE
 153033 | sklep   | 127.0.0.1   | active | LWLock          | BufferMapping | 0.02 | SELECT id, status, total, created_at FROM ord
(4 rows)

state = active oznacza, że proces wykonuje zapytanie, a sek mówi, od ilu sekund. Kolumny wait_event_type i wait_event mówią, na co proces czeka; puste oznaczają, że nie czeka. BufferMapping to według dokumentacji statystyk oczekiwanie na przypisanie bloku danych do bufora w shared_buffers: cztery procesy naraz wczytują setki megabajtów tych samych tabel. W migawce z pierwszego przebiegu tego samego testu pojawiło się też IO / DataFileRead, czyli oczekiwanie na odczyt z pliku danych.

Czas liczymy od clock_timestamp(), a nie od now(). now() to czas rozpoczęcia transakcji, w której działa nasze zapytanie kontrolne, więc wersja z now() pokazała w labie ujemny czas dla zapytania, które zaczęło się chwilę później. Warunek backend_type = 'client backend' ukrywa procesy pomocnicze zapytań równoległych; bez niego każde wolne zapytanie miało w wyniku jeszcze dwa wiersze bez adresu klienta. Te same procesy widać w systemie, a tytuł procesu mówi, do którego klastra, bazy i klienta należy:

# ps -o pid,etime,pcpu,cmd -u postgres --sort=-pcpu | head -6
    PID     ELAPSED %CPU CMD
 145227       00:00 18.0 postgres: 17/main: parallel worker for PID 145034
 145035       00:25 17.9 postgres: 17/main: sklep sklep 127.0.0.1(42038) SELECT
 145034       00:25 17.7 postgres: 17/main: sklep sklep 127.0.0.1(42036) SELECT
 145037       00:25 17.5 postgres: 17/main: sklep sklep 127.0.0.1(42074) SELECT
 145036       00:25 17.5 postgres: 17/main: sklep sklep 127.0.0.1(42060) SELECT

Numer procesu z top albo ps to ta sama wartość co pid w pg_stat_activity. Zapytanie, które trzeba przerwać, anuluje SELECT pg_cancel_backend(pid);, a całą sesję kończy SELECT pg_terminate_backend(pid);. Według dokumentacji funkcji administracyjnych obie funkcje wysyłają sygnał do procesu i może ich użyć superużytkownik, członek roli, do której należy sesja, albo rola z uprawnieniami pg_signal_backend.

Które zapytania kosztują najwięcej łącznie: pg_stat_statements

pg_stat_activity pokazuje tylko chwilę obecną. Sumy dla każdego rodzaju zapytania zbiera moduł pg_stat_statements, dostarczany razem z PostgreSQL. Według dokumentacji pg_stat_statements trzeba go załadować przez shared_preload_libraries, bo potrzebuje pamięci współdzielonej, a to wymaga restartu. Moduł zbiera statystyki ze wszystkich baz serwera, a widok do ich odczytu pojawia się w tej bazie, w której wykonamy CREATE EXTENSION. Na serwerze bazy, jako root:

cat > /etc/postgresql/17/main/conf.d/30-pg_stat_statements.conf <<'EOF'
shared_preload_libraries = 'pg_stat_statements'
EOF
chown postgres:postgres /etc/postgresql/17/main/conf.d/30-pg_stat_statements.conf
systemctl restart postgresql@17-main
sudo -u postgres psql -d postgres -c 'CREATE EXTENSION pg_stat_statements;'

Rozszerzenie zakładamy w bazie postgres, a nie w bazie aplikacji. W labie najpierw utworzyliśmy je w bazie sklep i widoki pg_stat_statements oraz pg_stat_statements_info trafiły do jej schematu public jako obiekty roli postgres. Późniejsze GRANT SELECT ON ALL TABLES IN SCHEMA public wykonane przez rolę sklep skończyło się ostrzeżeniami no privileges were granted for "pg_stat_statements". Po przeniesieniu rozszerzenia do bazy postgres zebrane statystyki zostały, bo moduł trzyma je w pamięci serwera. Zapytania jednej bazy wybieramy filtrem po dbid:

SELECT round(total_exec_time) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS proc,
       shared_blks_hit AS hit,
       shared_blks_read AS read,
       left(regexp_replace(query, '\s+', ' ', 'g'), 58) AS zapytanie
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'sklep')
ORDER BY total_exec_time DESC
LIMIT 5;

 total_ms | calls | mean_ms | proc |   hit   |  read   |                         zapytanie
----------+-------+---------+------+---------+---------+------------------------------------------------------------
   171128 |   128 |  1336.9 | 70.9 | 4827028 | 1572972 | SELECT sku, qty, price FROM order_items WHERE order_id = $
    70171 |   141 |   497.7 | 29.1 | 3934495 |  418838 | SELECT id, status, total, created_at FROM orders WHERE cus
      112 |    38 |     2.9 |  0.0 |     126 |     108 | UPDATE orders SET status = $1 WHERE id = $2 AND status = $
[...]

Przez 60 sekund testu zapytanie o pozycje zamówienia zajęło łącznie 171 sekund czasu wykonania, czyli 70,9% czasu wszystkich zapytań w tej bazie; historia klienta 29,1%. Suma przekracza czas testu, bo zapytania wykonywały się jednocześnie w 4 połączeniach (4 × 60 s = 240 s). Kolumny hit i read to liczba stron po 8 kB znalezionych w shared_buffers i doczytanych spoza nich. Według dokumentacji moduł zastępuje stałe w zapytaniu symbolami $1, $2, dlatego 128 zapytań o różne order_id daje jeden wiersz. Treść zapytań innych ról widzi tylko superużytkownik i rola z uprawnieniami pg_read_all_stats.

Rzutowanie ::numeric jest potrzebne: kolumny czasu są typu double precision, a dla niego nie ma dwuargumentowej funkcji round(). Bez rzutowania zapytanie kończy się błędem function round(double precision, integer) does not exist. Przed pomiarem, na przykład po zmianie indeksu, statystyki zeruje SELECT pg_stat_statements_reset();.

Log wolnych zapytań i automatyczne plany z auto_explain

Przy log_min_duration_statement = 200ms każde wolne zapytanie trafia do logu serwera z czasem i treścią. W ciągu 60 sekund testu takich wpisów było 273:

2026-10-14 10:49:45.219 UTC [145035] sklep@sklep LOG:  duration: 382.288 ms  statement: SELECT id, status, total, created_at
2026-10-14 10:49:45.557 UTC [145034] sklep@sklep LOG:  duration: 881.223 ms  statement: SELECT sku, qty, price FROM order_items WHERE order_id = 1154356;
2026-10-14 10:49:45.709 UTC [145036] sklep@sklep LOG:  duration: 850.417 ms  statement: SELECT sku, qty, price FROM order_items WHERE order_id = 922399;

Moduł auto_explain dopisuje do logu plan wykonania każdego zapytania dłuższego niż próg. Według dokumentacji auto_explain w pojedynczej sesji ładuje go LOAD 'auto_explain', ale tylko superużytkownik; dla zwykłej roli sklep w labie skończyło się to błędem access to library "auto_explain" is not allowed. Zwykle ładuje się go przez session_preload_libraries. Ten parametr działa po przeładowaniu konfiguracji, dla nowych połączeń, bez restartu:

cat > /etc/postgresql/17/main/conf.d/31-auto_explain.conf <<'EOF'
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = off
EOF
chown postgres:postgres /etc/postgresql/17/main/conf.d/31-auto_explain.conf
systemctl reload postgresql@17-main

2026-10-14 11:16:22.878 UTC [150019] sklep@sklep LOG:  duration: 1041.237 ms  plan:
        Query Text: SELECT sku, sum(qty) AS sztuk FROM order_items GROUP BY sku ORDER BY sztuk DESC LIMIT 3;
        Limit  (cost=61686.29..61686.29 rows=3 width=16)
          ->  Sort  (cost=61686.29..61711.34 rows=10022 width=16)
                Sort Key: (sum(qty)) DESC
                ->  Finalize HashAggregate  (cost=61456.53..61556.75 rows=10022 width=16)
                      Group Key: sku
                      ->  Gather  (cost=59251.69..61356.31 rows=20044 width=16)
                            Workers Planned: 2
                            ->  Partial HashAggregate  (cost=58251.69..58351.91 rows=10022 width=16)
                                  Group Key: sku
                                  ->  Parallel Seq Scan on order_items  (cost=0.00..49930.13 rows=1664313 width=12)

auto_explain.log_analyze zostawiamy wyłączony. Dokumentacja ostrzega, że po jego włączeniu czas każdego węzła planu jest mierzony dla wszystkich zapytań, także tych, które nie trafią do logu, co może bardzo obniżyć wydajność.

Brakujące indeksy w PostgreSQL: pełne skany tabel, EXPLAIN (ANALYZE, BUFFERS) i klucze obce bez indeksu

Tabele czytane pełnymi skanami: seq_scan i seq_tup_read w pg_stat_user_tables

Widok pg_stat_user_tables liczy dla każdej tabeli skany sekwencyjne, czyli czytanie wszystkich wierszy po kolei, oraz skany przez indeks. Po teście:

SELECT relname, seq_scan, seq_tup_read, idx_scan,
       n_live_tup, n_dead_tup,
       pg_size_pretty(pg_table_size(relid)) AS rozmiar
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

   relname   | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup | rozmiar
-------------+----------+--------------+----------+------------+------------+---------
 order_items |      358 |    768000000 |        0 |          0 |          0 | 391 MB
 orders      |      400 |    282000000 |       38 |          0 |          4 | 240 MB
 customers   |        0 |            0 |        0 |          0 |          0 | 16 MB
(3 rows)

seq_tup_read to liczba wierszy przeczytanych skanami sekwencyjnymi: 768 000 000 to dokładnie 128 zapytań o pozycje razy 6 000 000 wierszy order_items, a 282 000 000 to 141 zapytań o historię razy 2 000 000 zamówień. Każde zapytanie czytało całą tabelę, żeby znaleźć kilka wierszy.

Zera w n_live_tup to skutek uboczny przygotowania testu: przed nim wyzerowaliśmy liczniki funkcją pg_stat_reset(). Dokumentacja statystyk ostrzega, że ta funkcja zeruje też liczniki, z których autovacuum korzysta przy decyzji o uruchomieniu VACUUM i ANALYZE, i zaleca potem ANALYZE całej bazy. Na produkcji zamiast zerować zapisujemy wartości przed i po okresie pomiaru i odejmujemy. Liczniki zerują się też same po nieczystym zatrzymaniu serwera: po awarii, zatrzymaniu w trybie immediate, starcie z kopii bazowej i odtworzeniu do punktu w czasie.

Plan zapytania: EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN pokazuje plan, który wybrał planer, a z opcją ANALYZE wykonuje zapytanie i dopisuje rzeczywiste czasy i liczby wierszy. BUFFERS dodaje liczbę stron przeczytanych z pamięci i z dysku. Historia zamówień klienta przed zmianami:

sklep=# EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, total, created_at FROM orders
        WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=42186.74..42187.91 rows=10 width=29) (actual time=145.814..155.329 rows=7 loops=1)
   Buffers: shared hit=30597 read=287
   I/O Timings: shared read=1.060
   ->  Gather Merge  (cost=42186.74..42187.91 rows=10 width=29) (actual time=145.813..155.325 rows=7 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=30597 read=287
         I/O Timings: shared read=1.060
         ->  Sort  (cost=41186.72..41186.73 rows=5 width=29) (actual time=140.429..140.431 rows=2 loops=3)
               Sort Key: created_at DESC
               Sort Method: quicksort  Memory: 25kB
[...]
               ->  Parallel Seq Scan on orders  (cost=0.00..41186.66 rows=5 width=29) (actual time=61.484..140.309 rows=2 loops=3)
                     Filter: (customer_id = 4242)
                     Rows Removed by Filter: 666664
                     Buffers: shared hit=30483 read=287
                     I/O Timings: shared read=1.060
 Planning:
   Buffers: shared hit=86 read=3
   I/O Timings: shared read=0.887
 Planning Time: 1.500 ms
 Execution Time: 155.401 ms
(25 rows)

Plan czytamy od najbardziej wciętego węzła. Parallel Seq Scan on orders to skan sekwencyjny podzielony między proces główny i 2 procesy pomocnicze (Workers Launched: 2, loops=3). Każdy z nich odrzucił 666 664 wiersze (Rows Removed by Filter), czyli razem około 2 milionów, żeby znaleźć 7 zamówień klienta. Buffers: shared hit=30597 read=287 to strony po 8 kB: 30 597 było w shared_buffers, 287 trzeba było doczytać. I/O Timings pokazuje czas tych odczytów, bo włączyliśmy track_io_timing. Samo zapytanie trwało 155 ms; pod obciążeniem czterech połączeń średnio 499 ms. Zapytanie o pozycje zamówienia miało ten sam problem na order_items: Parallel Seq Scan, 2 000 000 odrzuconych wierszy na każdy z trzech procesów i 353 ms bez obciążenia.

W PostgreSQL 18 EXPLAIN ANALYZE dołącza BUFFERS bez dopisywania tej opcji, co potwierdzają informacje o wydaniu i test na Ubuntu 26.04. Wersja 18 dopisuje też przy skanach indeksu wiersz Index Searches i podaje liczbę wierszy z dwoma miejscami po przecinku, np. rows=1.00.

Klucz obcy bez indeksu: dlaczego DELETE klienta czyta całą tabelę zamówień

Dokumentacja ograniczeń wyjaśnia: usunięcie wiersza z tabeli, do której odwołuje się klucz obcy, wymaga przeszukania tabeli odwołującej się, a PostgreSQL nie tworzy indeksu na kolumnach klucza obcego automatycznie. MySQL robi to inaczej: według dokumentacji MySQL 8.4 InnoDB wymaga indeksu na kolumnach klucza obcego i sam go tworzy, jeśli go nie ma. Schemat przeniesiony z MySQL do PostgreSQL bez tych indeksów działa więc poprawnie, ale wolniej. Klucze obce bez indeksu, który zaczyna się od ich kolumn, znajduje zapytanie:

SELECT c.conrelid::regclass AS tabela,
       c.conname AS klucz_obcy,
       pg_get_constraintdef(c.oid) AS definicja,
       pg_size_pretty(pg_relation_size(c.conrelid)) AS rozmiar
FROM pg_constraint AS c
WHERE c.contype = 'f'
  AND NOT EXISTS (
        SELECT 1
        FROM pg_index AS i
        WHERE i.indrelid = c.conrelid
          AND (i.indkey::smallint[])[0:cardinality(c.conkey) - 1] @> c.conkey
          AND (i.indkey::smallint[])[0:cardinality(c.conkey) - 1] <@ c.conkey
      )
ORDER BY pg_relation_size(c.conrelid) DESC;

   tabela    |        klucz_obcy         |                     definicja                      | rozmiar
-------------+---------------------------+----------------------------------------------------+---------
 order_items | order_items_order_id_fkey | FOREIGN KEY (order_id) REFERENCES orders(id)       | 391 MB
 orders      | orders_customer_id_fkey   | FOREIGN KEY (customer_id) REFERENCES customers(id) | 240 MB
(2 rows)

Zapytanie porównuje kolumny klucza obcego (conkey) z pierwszymi kolumnami każdego indeksu tabeli (indkey). Ile kosztuje brak takiego indeksu, pokazuje usunięcie klienta bez zamówień. Wykonujemy je w transakcji, którą wycofujemy:

BEGIN;
INSERT INTO customers (email, name) VALUES ('test-usuwania@example.com', 'Test') RETURNING id \gset
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) DELETE FROM customers WHERE id = :id;
ROLLBACK;

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Delete on customers (actual time=0.109..0.109 rows=0 loops=1)
   Buffers: shared hit=12
   ->  Index Scan using customers_pkey on customers (actual time=0.031..0.032 rows=1 loops=1)
         Index Cond: (id = 200001)
         Buffers: shared hit=7
 Planning:
   Buffers: shared hit=33
 Planning Time: 0.258 ms
 Trigger for constraint orders_customer_id_fkey: time=255.004 calls=1
 Execution Time: 255.527 ms
(10 rows)

Samo usunięcie wiersza trwało 0,1 ms. Pozostałe 255 ms zajęło sprawdzenie klucza obcego (Trigger for constraint orders_customer_id_fkey), które przeczytało całą tabelę zamówień, żeby upewnić się, że żadne zamówienie nie wskazuje na tego klienta. \gset zapisuje wynik RETURNING id w zmiennej psql :id.

Indeks bez blokowania zapisów: CREATE INDEX CONCURRENTLY

Zwykłe CREATE INDEX blokuje zapisy do tabeli na czas budowy indeksu. Na działającej bazie używamy CREATE INDEX CONCURRENTLY, jako właściciel tabel:

sklep=> \timing on
Timing is on.
sklep=> CREATE INDEX CONCURRENTLY order_items_order_id_idx ON order_items (order_id);
CREATE INDEX
Time: 5131.575 ms (00:05.132)
sklep=> CREATE INDEX CONCURRENTLY orders_customer_id_created_at_idx ON orders (customer_id, created_at);
CREATE INDEX
Time: 2772.172 ms (00:02.772)

Dla historii klienta budujemy indeks złożony (customer_id, created_at). Wpisy są w nim posortowane najpierw według klienta, a w obrębie klienta według daty, więc zapytanie WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20 odczytuje od końca 20 ostatnich wpisów jednego klienta i nie sortuje niczego. Ten sam indeks obsługuje sprawdzanie klucza obcego customer_id, bo zaczyna się od tej kolumny: po jego utworzeniu zapytanie o klucze obce bez indeksu zwróciło 0 wierszy, a sprawdzenie klucza przy usuwaniu klienta skróciło się z 255 ms do 1,1 ms.

Według dokumentacji CREATE INDEX wariant CONCURRENTLY dwa razy skanuje tabelę i czeka na zakończenie transakcji, które mogłyby używać indeksu, a nie da się go wykonać wewnątrz bloku transakcji. Postęp budowy pokazuje widok pg_stat_progress_create_index. Odpytywaliśmy go co 0,7 s w czasie budowy indeksu na order_items; trzy kolejne odczyty pokazały kolejne fazy:

SELECT p.pid, p.relid::regclass AS tabela, p.index_relid::regclass AS indeks,
       p.phase, p.blocks_done, p.blocks_total
FROM pg_stat_progress_create_index AS p;

Gdy budowa się nie powiedzie, zostaje indeks oznaczony jako nieprawidłowy. Dokumentacja opisuje, że taki indeks nie służy zapytaniom, ale nadal spowalnia zapisy. W labie celowo spróbowaliśmy założyć indeks unikalny na order_items (order_id), choć zamówienia mają po kilka pozycji:

sklep=> CREATE UNIQUE INDEX CONCURRENTLY order_items_order_id_uniq ON order_items (order_id);
ERROR:  could not create unique index "order_items_order_id_uniq"
DETAIL:  Key (order_id)=(169601) is duplicated.
CONTEXT:  parallel worker
sklep=> \d order_items
[...]
Indexes:
    "order_items_pkey" PRIMARY KEY, btree (id)
    "order_items_order_id_idx" btree (order_id)
    "order_items_order_id_uniq" UNIQUE, btree (order_id) INVALID

Nieprawidłowe indeksy w bazie wypisuje SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;. Dokumentacja zaleca usunąć taki indeks i spróbować ponownie; usuwamy go przez DROP INDEX CONCURRENTLY order_items_order_id_uniq;.

Wynik po dodaniu dwóch indeksów

sklep=# EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, total, created_at FROM orders
        WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..13.82 rows=11 width=29) (actual time=0.055..0.140 rows=7 loops=1)
   Buffers: shared hit=13 read=1
   I/O Timings: shared read=0.018
   ->  Index Scan Backward using orders_customer_id_created_at_idx on orders  (cost=0.43..13.82 rows=11 width=29) (actual time=0.054..0.137 rows=7 loops=1)
         Index Cond: (customer_id = 4242)
         Buffers: shared hit=13 read=1
         I/O Timings: shared read=0.018
 Planning:
   Buffers: shared hit=113
 Planning Time: 0.632 ms
 Execution Time: 0.189 ms
(11 rows)

Index Scan Backward czyta indeks od końca, od najnowszych zamówień klienta. Zamiast 30 884 stron zapytanie przeczytało 14. Ten sam test pgbench po zmianie:

W pg_stat_statements oba zapytania mają teraz średnio 0,1 ms i łącznie po około 14 sekund na ponad 240 000 wykonań każde. Ta sama maszyna obsłużyła ponad 1700 razy więcej transakcji na sekundę.

Nieużywane indeksy: idx_scan i last_idx_scan w pg_stat_user_indexes

Każdy indeks zajmuje miejsce i spowalnia zapisy, bo każdy INSERT i UPDATE musi go uaktualnić. Kandydatów do usunięcia pokazuje pg_stat_user_indexes. Pomijamy indeksy unikalne i klucze główne, bo pilnują poprawności danych, nawet jeśli żadne zapytanie ich nie czyta. Żeby mieć przykład, dodaliśmy indeks na kolumnie status i puściliśmy 10 sekund ruchu:

SELECT s.relname AS tabela,
       s.indexrelname AS indeks,
       s.idx_scan,
       s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS rozmiar
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;

   tabela    |              indeks               | idx_scan |         last_idx_scan         | rozmiar
-------------+-----------------------------------+----------+-------------------------------+---------
 orders      | orders_status_idx                 |        0 |                               | 9048 kB
 order_items | order_items_order_id_idx          |    44264 | 2026-10-14 11:24:07.630352+00 | 56 MB
 orders      | orders_customer_id_created_at_idx |    44276 | 2026-10-14 11:24:07.630352+00 | 40 MB
(3 rows)

Kolumna last_idx_scan, czas ostatniego użycia indeksu, jest dostępna od PostgreSQL 16. Zero w idx_scan znaczy tylko tyle, że indeksu nie użyto od ostatniego wyzerowania liczników, a te zerują się po awarii, zatrzymaniu w trybie immediate i odtworzeniu z kopii. Przed usunięciem indeksu sprawdzamy więc, czy liczniki obejmują pełny cykl pracy aplikacji, łącznie z zadaniami uruchamianymi rzadko, na przykład raportami miesięcznymi.

VACUUM w PostgreSQL: martwe wiersze, wersje wierszy i rodzaje VACUUM

PostgreSQL obsługuje równoczesne transakcje przez MVCC (ang. Multi-Version Concurrency Control). UPDATE nie nadpisuje wiersza, tylko zapisuje jego nową wersję, a DELETE oznacza wiersz jako usunięty. Stara wersja zostaje w pliku tabeli, bo może ją jeszcze widzieć transakcja, która zaczęła się wcześniej. Gdy przestaje być potrzebna komukolwiek, staje się martwym wierszem (ang. dead tuple), a zajmowane przez nią miejsce trzeba odzyskać. Robi to VACUUM. Według rozdziału dokumentacji o rutynowym odkurzaniu VACUUM ma cztery zadania: odzyskać miejsce po zmienionych i usuniętych wierszach, odświeżyć statystyki dla planera, zaktualizować mapę widoczności, która przyspiesza skany samego indeksu, i chronić dane przed zawinięciem licznika transakcji (opisujemy je w części o autovacuum).

Po teście pgbench i UPDATE 284 970 zamówień (zmiana statusu) plik orders urósł w labie z 240 do 275 MB. Po VACUUM rozszerzenie pgstattuple pokazało 35 MB wolnego miejsca, czyli 12,87% tabeli, ale plik nadal miał 275 MB. Zwykły VACUUM zostawia to miejsce w tabeli na nowe wiersze; jak sprawdzić, ile da się oddać systemowi, opisujemy w następnej części.

Rodzaje VACUUM: zwykły, ANALYZE, FREEZE, FULL, CLUSTER i pg_repack

Poziomy blokad pochodzą z rozdziału dokumentacji o blokadach: SHARE UPDATE EXCLUSIVE nie koliduje z blokadami zwykłych odczytów i zapisów, a ACCESS EXCLUSIVE koliduje ze wszystkimi i jako jedyna blokuje zwykły SELECT. Dokumentacja VACUUM podsumowuje: wariant FULL odzyskuje więcej miejsca, ale trwa znacznie dłużej, blokuje tabelę na wyłączność i nie jest zalecany do rutynowego użycia; przydaje się, gdy usunięto albo zmieniono większość wierszy tabeli. Częste zwykłe VACUUM są według rozdziału o odkurzaniu lepsze dla mocno zmienianych tabel niż rzadkie VACUUM FULL.

Pełną listę opcji w danej wersji wypisuje \h VACUUM w psql. W PostgreSQL 17 są to między innymi:

• SKIP_LOCKED pomija tabele, których nie da się zablokować od razu, zamiast czekać;
• TRUNCATE false wyłącza odcinanie pustych stron z końca pliku, które wymaga krótkiej blokady ACCESS EXCLUSIVE;
• PARALLEL n czyści indeksy w kilku procesach pomocniczych, po jednym na indeks; nie działa z FULL;
• INDEX_CLEANUP off pomija czyszczenie indeksów; dokumentacja przewiduje je na sytuację, gdy VACUUM musi jak najszybciej uchronić tabelę przed zawinięciem licznika transakcji;
• BUFFER_USAGE_LIMIT ustala, ile shared_buffers może zająć jeden VACUUM (od 128 kB do 16 GB).

VACUUM nie działa wewnątrz bloku transakcji: BEGIN; VACUUM orders; kończy się błędem.

Jak czytać wynik VACUUM (VERBOSE)

Raport z VACUUM po usunięciu 284 970 martwych wierszy z orders:

sklep=> VACUUM (VERBOSE) orders;
INFO:  vacuuming "sklep.public.orders"
INFO:  launched 1 parallel vacuum worker for index vacuuming (planned: 1)
INFO:  finished vacuuming "sklep.public.orders": index scans: 1
pages: 0 removed, 35158 remain, 34245 scanned (97.40% of total)
tuples: 284970 removed, 1992859 remain, 0 are dead but not yet removable
removable cutoff: 8117, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 29949 pages from table (85.18% of total) had 278694 dead item identifiers removed
index "orders_pkey": pages: 8724 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "orders_customer_id_created_at_idx": pages: 7772 in total, 0 newly deleted, 0 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 10.005 MB/s
buffer usage: 115026 hits, 0 misses, 983 dirtied
WAL usage: 110423 records, 1 full page images, 10067681 bytes
system usage: CPU: user: 0.68 s, system: 0.02 s, elapsed: 0.76 s
INFO:  vacuuming "sklep.pg_toast.pg_toast_16403"
[...]

Druga część raportu dotyczy tabeli pg_toast_16403. To tabela TOAST (ang. The Oversized-Attribute Storage Technique), w której PostgreSQL trzyma duże wartości kolumn poza głównym plikiem tabeli; dokumentacja TOAST opisuje, kiedy wartość tam trafia. VACUUM przetwarza ją razem z tabelą.

Kiedy zwykły VACUUM zmniejsza plik tabeli: puste strony na końcu

Według rozdziału dokumentacji o odkurzaniu zwykły VACUUM oddaje miejsce systemowi tylko w jednym przypadku: gdy całkowicie puste są strony na końcu tabeli i da się na chwilę zablokować tabelę na wyłączność. Sprawdziliśmy to na tabeli z 500 000 wierszy, z której usunęliśmy drugą połowę, czyli wiersze zapisane na końcu pliku:

sklep=> CREATE TABLE import_tmp AS SELECT i AS id, md5(i::text) AS payload FROM generate_series(1, 500000) AS i;
SELECT 500000
sklep=> SELECT pg_size_pretty(pg_table_size('import_tmp')) AS przed;
 przed
-------
 33 MB
sklep=> DELETE FROM import_tmp WHERE id > 250000;
DELETE 250000
sklep=> VACUUM (VERBOSE) import_tmp;
INFO:  vacuuming "sklep.public.import_tmp"
INFO:  table "import_tmp": truncated 4224 to 2084 pages
INFO:  finished vacuuming "sklep.public.import_tmp": index scans: 0
pages: 2140 removed, 2084 remain, 4224 scanned (100.00% of total)
tuples: 250000 removed, 250000 remain, 0 are dead but not yet removable
[...]
sklep=> SELECT pg_size_pretty(pg_table_size('import_tmp')) AS po;
  po
-------
 16 MB

Plik zmniejszył się z 33 do 16 MB. Gdy usuwamy najstarsze wiersze, zapisane na początku pliku, zwykły VACUUM nie odda ani strony, co pokazujemy niżej na archiwizacji zamówień (pages: 0 removed).

vacuumdb: VACUUM i ANALYZE z linii poleceń (vacuumdb -f, -z, -Z, --analyze-in-stages)

vacuumdb to program, który łączy się z bazą i wysyła polecenia VACUUM albo ANALYZE, także dla wszystkich baz klastra naraz. Z opcją -e wypisuje wysyłany SQL, więc w labie sprawdziliśmy, co dokładnie robi każda opcja:

Poza trybem --analyze-in-stages vacuumdb kończy pracę poleceniem VACUUM (ONLY_DATABASE_STATS);. Bez -t program przetwarza wszystkie tabele bazy, zaczynając od największych, a -a wszystkie bazy, do których można się połączyć (bez template0). -j 2 otwiera dwa połączenia i przetwarza dwie tabele naraz: vacuumdb -a -z -j 2 na naszym serwerze zajęło 1,8 s. Inne przydatne opcje w PostgreSQL 17 to --skip-locked, -P (procesy pomocnicze dla indeksów jednej tabeli), --min-xid-age (tylko tabele z najstarszymi numerami transakcji) i -n (jeden schemat). PostgreSQL 18 dodaje --missing-stats-only, czyli ANALYZE tylko tam, gdzie brakuje statystyk.

Polecenie w rodzaju vacuumdb -f nazwa_bazy wykonuje VACUUM FULL na każdej tabeli bazy, po kolei, i każdą blokuje na wyłączność na czas jej przepisania. Na produkcji oznacza to przerwę w działaniu aplikacji i potrzebę wolnego miejsca na kopię największej tabeli. Takiego polecenia nie wpisujemy do crona.

Ile miejsca odzyska VACUUM: pomiar pgstattuple i pgstattuple_approx przed VACUUM FULL

PostgreSQL nie ma trybu próbnego VACUUM ani wbudowanego szacunku, ile miejsca wróci na dysk. Odpowiedź daje pomiar, a reguła jest prosta. Zwykły VACUUM oddaje systemowi tylko puste strony z końca pliku, a resztę zostawia w tabeli na nowe wiersze. VACUUM FULL i pg_repack przepisują tabelę, więc po przepisaniu zajmie ona mniej więcej tyle, ile zajmuje teraz, minus wolne miejsce i martwe wiersze. Te dwie wartości mierzy rozszerzenie pgstattuple z modułów dostarczanych razem z PostgreSQL:

• pgstattuple('tabela') mierzy dokładnie. Według dokumentacji pgstattuple zawsze czyta całą tabelę i zwraca dokładną liczbę i rozmiar żywych i martwych wierszy oraz wolne miejsce. Zakłada tylko blokadę odczytu, więc wynik nie jest migawką z jednej chwili.
• pgstattuple_approx('tabela') szacuje. Pomija strony, które według mapy widoczności mają wyłącznie widoczne wiersze, i dla nich bierze wolne miejsce z mapy wolnej przestrzeni (FSM, ang. Free Space Map). Rozmiar tabeli i dane o martwych wierszach są dokładne, liczba i rozmiar żywych wierszy oraz wolne miejsce są szacunkiem.
• pgstatindex('indeks') pokazuje stan indeksu B-tree: gęstość wypełnienia stron liści, liczbę stron pustych i usuniętych.

Według dokumentacji funkcje mogą domyślnie wywołać tylko superużytkownicy i członkowie roli pg_stat_scan_tables. Rozszerzenie zakładamy jako postgres w bazie, którą mierzymy. Tworzy ono funkcje w schemacie public tej bazy; jeśli nie chcemy ich tam zostawiać, po pomiarze usuwamy je przez DROP EXTENSION pgstattuple;.

sudo -u postgres psql -d sklep -c 'CREATE EXTENSION pgstattuple;'

Scenariusz: usunięcie zamówień starszych niż dwa lata

Sklep archiwizuje zamówienia starsze niż dwa lata. Przed operacją tabela orders ma 275 MB, w tym 35 MB wolnego miejsca po wcześniejszym VACUUM, a order_items 391 MB. Stare zamówienia i ich pozycje usuwamy jako sklep, w jednej transakcji:

sklep=> BEGIN;
BEGIN
sklep=> DELETE FROM order_items
        WHERE order_id IN (SELECT id FROM orders
                           WHERE created_at < timestamptz '2024-10-14 00:00:00+00');
DELETE 2005649
Time: 5870.179 ms (00:05.870)
sklep=> DELETE FROM orders
        WHERE created_at < timestamptz '2024-10-14 00:00:00+00';
DELETE 668549
Time: 13946.561 ms (00:13.947)
sklep=> COMMIT;
COMMIT

Zaraz po COMMIT, jako postgres w bazie sklep:

SELECT 'orders' AS tabela, pg_size_pretty(table_len) AS rozmiar, tuple_count, pg_size_pretty(tuple_len) AS zywe,
       dead_tuple_count, pg_size_pretty(dead_tuple_len) AS martwe, dead_tuple_percent AS martwe_proc,
       pg_size_pretty(free_space) AS wolne, free_percent AS wolne_proc
FROM pgstattuple('orders')
UNION ALL
SELECT 'order_items', pg_size_pretty(table_len), tuple_count, pg_size_pretty(tuple_len),
       dead_tuple_count, pg_size_pretty(dead_tuple_len), dead_tuple_percent,
       pg_size_pretty(free_space), free_percent
FROM pgstattuple('order_items');

   tabela    | rozmiar | tuple_count |  zywe  | dead_tuple_count | martwe | martwe_proc | wolne  | wolne_proc
-------------+---------+-------------+--------+------------------+--------+-------------+--------+------------
 orders      | 275 MB  |     1331451 | 148 MB |           668549 | 74 MB  |       27.01 | 35 MB  |      12.87
 order_items | 391 MB  |     3994351 | 238 MB |          2005649 | 120 MB |       30.64 | 195 kB |       0.05
(2 rows)

Usunięte wiersze nadal leżą w plikach jako martwe: 74 MB w orders i 120 MB w order_items. W orders jest jeszcze 35 MB wolnego miejsca z wcześniejszych aktualizacji.

Autovacuum sprząta martwe wiersze, ale plik zostaje tej samej wielkości

Nie uruchamialiśmy VACUUM ręcznie. Liczba martwych wierszy przekroczyła progi autovacuum (398 622 dla orders i 1 200 050 dla order_items; skąd te liczby, wyjaśniamy w części o autovacuum), więc około minuty po COMMIT autovacuum zajął się obiema tabelami. Dzięki log_autovacuum_min_duration = 0 log pokazał szczegóły:

2026-10-14 10:59:50.252 UTC [146360] LOG:  automatic vacuum of table "sklep.public.orders": index scans: 1
        pages: 0 removed, 35158 remain, 11864 scanned (33.74% of total)
        tuples: 668549 removed, 1324919 remain, 0 are dead but not yet removable
        removable cutoff: 8119, which was 0 XIDs old when operation ended
        frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
        index scan needed: 11863 pages from table (33.74% of total) had 668549 dead item identifiers removed
        index "orders_pkey": pages: 8724 in total, 2950 newly deleted, 2950 currently deleted, 0 reusable
        index "orders_customer_id_created_at_idx": pages: 7772 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        I/O timings: read: 170.836 ms, write: 0.000 ms
        avg read rate: 2.143 MB/s, avg write rate: 18.538 MB/s
        buffer usage: 72448 hits, 1060 misses, 9170 dirtied
        WAL usage: 52153 records, 10688 full page images, 52286354 bytes
        system usage: CPU: user: 0.75 s, system: 0.15 s, elapsed: 3.86 s
2026-10-14 11:00:06.834 UTC [146360] LOG:  automatic vacuum of table "sklep.public.order_items": index scans: 1
        pages: 0 removed, 50000 remain, 50000 scanned (100.00% of total)
        tuples: 2005649 removed, 3994351 remain, 0 are dead but not yet removable
[...]
        index "order_items_pkey": pages: 16454 in total, 5498 newly deleted, 5498 currently deleted, 0 reusable
        index "order_items_order_id_idx": pages: 10736 in total, 3587 newly deleted, 3587 currently deleted, 0 reusable
[...]
        system usage: CPU: user: 2.22 s, system: 0.98 s, elapsed: 15.21 s

pages: 0 removed: usunięte zamówienia były najstarsze, więc leżały na początku plików, a VACUUM odcina tylko strony z końca. Indeks klucza głównego orders_pkey stracił 2950 stron w całości (newly deleted), bo usuwaliśmy ciągły zakres numerów zamówień. W indeksie orders_customer_id_created_at_idx nie zwolniła się żadna cała strona: wpisy starych zamówień były rozrzucone po całym indeksie, posortowanym według klientów. Pomiar po autovacuum, każdy po wyczyszczeniu pamięci podręcznej systemu:

-- pgstattuple, to samo zapytanie co wyżej
   tabela    | rozmiar | tuple_count |  zywe  | dead_tuple_count | martwe  | martwe_proc | wolne  | wolne_proc
-------------+---------+-------------+--------+------------------+---------+-------------+--------+------------
 orders      | 275 MB  |     1331451 | 148 MB |                0 | 0 bytes |           0 | 115 MB |      41.87
 order_items | 391 MB  |     3994351 | 238 MB |                0 | 0 bytes |           0 | 130 MB |      33.33
(2 rows)

Time: 1352.607 ms (00:01.353)

-- pgstattuple_approx
   tabela    | rozmiar | przeskanowano_proc | approx_tuple_count |  zywe  | martwe  | wolne  |    wolne_proc
-------------+---------+--------------------+--------------------+--------+---------+--------+-------------------
 orders      | 275 MB  |                  0 |            1330917 | 160 MB | 0 bytes | 115 MB | 41.76743202116161
 order_items | 391 MB  |                  0 |            3984652 | 261 MB | 0 bytes | 130 MB |     33.2968203125
(2 rows)

Time: 22.335 ms

Martwych wierszy już nie ma, a ich miejsce stało się wolnym miejscem w tabeli: 115 MB (41,87%) w orders i 130 MB (33,33%) w order_items. Pliki mają jednak dalej 275 i 391 MB. pgstattuple_approx podał to samo wolne miejsce w 22 ms zamiast 1353 ms, bo po VACUUM wszystkie strony były oznaczone w mapie widoczności i funkcja nie przeczytała żadnej (scanned_percent = 0). Rozmiar żywych wierszy wyszedł w niej większy (160 zamiast 148 MB), bo według dokumentacji dla pominiętych stron zakłada, że wszystko poza wolnym miejscem zajmują żywe wiersze. Przed VACUUM, gdy stron z martwymi wierszami jest dużo, funkcja musi je przeczytać i traci przewagę.

Rozdęte indeksy: gęstość stron w pgstatindex

SELECT i.indexrelid::regclass AS indeks,
       pg_size_pretty(s.index_size) AS rozmiar,
       s.leaf_pages, s.empty_pages, s.deleted_pages,
       s.avg_leaf_density AS gestosc_lisci,
       s.leaf_fragmentation AS fragmentacja
FROM pg_index AS i
CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
WHERE i.indrelid IN ('orders'::regclass, 'order_items'::regclass)
ORDER BY s.index_size DESC;

              indeks               | rozmiar | leaf_pages | empty_pages | deleted_pages | gestosc_lisci | fragmentacja
-----------------------------------+---------+------------+-------------+---------------+---------------+--------------
 order_items_pkey                  | 129 MB  |      10915 |           0 |          5498 |         90.08 |            0
 order_items_order_id_idx          | 84 MB   |       7121 |           0 |          3587 |          90.2 |            0
 orders_pkey                       | 68 MB   |       5746 |           0 |          2950 |         57.14 |        36.69
 orders_customer_id_created_at_idx | 61 MB   |       7733 |           0 |             0 |         59.52 |        0.91
(4 rows)

avg_leaf_density to średnie wypełnienie stron liści indeksu. Indeksy na order_items mają je na poziomie 90%, bo ich strony ze starymi wpisami zostały usunięte w całości, ale te 5498 i 3587 usuniętych stron nadal należą do plików. orders_pkey urósł po aktualizacjach statusów z 43 do 68 MB i jest wypełniony w 57%, a indeks złożony w 60%. VACUUM FULL i pg_repack budują wszystkie indeksy tabeli od nowa; sam indeks przebudowuje REINDEX INDEX CONCURRENTLY.

Szacunek a wynik: VACUUM FULL na orders i pg_repack na order_items

Rozmiar tabeli po przepisaniu szacujemy jako table_len - free_space - dead_tuple_len z pgstattuple. Na orders uruchomiliśmy VACUUM FULL, na order_items pg_repack (oba opisujemy w następnej części):

Szacunek zgodził się z wynikiem z dokładnością do 1 MB. Dodatkowo wróciło miejsce z indeksów, które oba narzędzia budują od nowa: orders_pkey zmniejszył się z 68 do 29 MB, indeks złożony z 61 do 40 MB, a indeksy order_items ze 129 do 86 MB i z 84 do 56 MB.

VACUUM FULL na produkcji: blokada ACCESS EXCLUSIVE, kolejka blokad, lock_timeout i pg_repack

Co widzi aplikacja podczas VACUUM FULL

VACUUM FULL na tabeli orders (160 MB) uruchomiliśmy w ósmej sekundzie testu pgbench z tymi samymi skryptami, z raportem co sekundę:

progress: 7.0 s, 10817.4 tps, lat 0.369 ms stddev 0.114, 0 failed
progress: 8.0 s, 10236.4 tps, lat 0.388 ms stddev 0.262, 0 failed
progress: 9.0 s, 65.9 tps, lat 0.472 ms stddev 0.555, 0 failed
progress: 10.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 11.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 12.0 s, 2008.1 tps, lat 7.957 ms stddev 162.930, 0 failed
progress: 13.0 s, 7649.5 tps, lat 0.522 ms stddev 0.269, 0 failed

VACUUM FULL trwał 3654 ms (Time: 3653.512 ms). Przez trzy sekundy baza obsłużyła łącznie kilkadziesiąt transakcji, zamiast ponad 10 000 na sekundę, a log wolnych zapytań zapisał zapytania o historię klienta, które zwykle trwają 0,4 ms, z czasem duration: 3650.062 ms. Tabela o rozmiarze kilkudziesięciu gigabajtów oznacza odpowiednio dłuższą przerwę.

Kolejka blokad: VACUUM FULL czekający na długą transakcję blokuje kolejne zapytania

Groźniejsze od samego przepisywania jest czekanie na blokadę. W labie rola raport rozpoczęła transakcję, przeczytała orders i zostawiła transakcję otwartą, tak jak robi to narzędzie raportowe z wyłączonym automatycznym zatwierdzaniem. Po chwili uruchomiliśmy VACUUM FULL orders, a po kolejnych dwóch sekundach aplikacja wysłała zwykły odczyt zamówienia po kluczu głównym. Stan po czterech sekundach:

SELECT a.pid, a.usename, a.state, a.wait_event_type AS typ, a.wait_event,
       pg_blocking_pids(a.pid) AS blokowany_przez,
       round(extract(epoch FROM clock_timestamp() - a.query_start)::numeric, 1) AS sek,
       left(a.query, 34) AS zapytanie
FROM pg_stat_activity AS a
WHERE a.datname = 'sklep' AND a.pid <> pg_backend_pid()
  AND a.backend_type = 'client backend'
ORDER BY a.query_start;

  pid   | usename |        state        |  typ   | wait_event | blokowany_przez | sek |             zapytanie
--------+---------+---------------------+--------+------------+-----------------+-----+------------------------------------
 146525 | raport  | idle in transaction | Client | ClientRead | {}              | 8.1 | SELECT count(*) FROM orders;
 146530 | sklep   | active              | Lock   | relation   | {146525}        | 6.1 | VACUUM FULL orders;
 146534 | sklep   | active              | Lock   | relation   | {146530}        | 4.1 | SELECT status FROM orders WHERE id
(3 rows)

sklep=# SELECT l.pid, l.mode, l.granted FROM pg_locks AS l WHERE l.relation = 'orders'::regclass ORDER BY l.granted DESC, l.pid;
  pid   |        mode         | granted
--------+---------------------+---------
 146525 | AccessShareLock     | t
 146530 | AccessExclusiveLock | f
 146534 | AccessShareLock     | f
(3 rows)

Sesja raport trzyma AccessShareLock, bo jej transakcja czytała tabelę. VACUUM FULL czeka na AccessExclusiveLock, a funkcja pg_blocking_pids() wskazuje jako blokującego proces raport. Zwykły SELECT czeka jednak nie na raport, tylko na VACUUM FULL, choć jego AccessShareLock nie koliduje z blokadą sesji raport. Prośba o blokadę wyłączną ustawia się w kolejce, a późniejsze prośby czekają za nią. Ten sam mechanizm opisuje dokumentacja pg_dump: gdy inny klient poprosi o blokadę wyłączną tabeli, której używa pg_dump, każdy kolejny dostęp do tej tabeli ustawi się w kolejce za tą prośbą. log_lock_waits zapisał przebieg:

2026-10-14 11:01:07.150 UTC [146530] sklep@sklep LOG:  process 146530 still waiting for AccessExclusiveLock on relation 16403 of database 16390 after 1000.162 ms
2026-10-14 11:01:09.080 UTC [146534] sklep@sklep LOG:  process 146534 still waiting for AccessShareLock on relation 16403 of database 16390 after 1000.548 ms at character 20
2026-10-14 11:01:12.447 UTC [146530] sklep@sklep LOG:  process 146530 acquired AccessExclusiveLock on relation 16403 of database 16390 after 6298.235 ms
2026-10-14 11:01:18.346 UTC [146534] sklep@sklep LOG:  process 146534 acquired AccessShareLock on relation 16403 of database 16390 after 10266.669 ms at character 20

Gdy zakończyliśmy sesję raport przez pg_terminate_backend(), VACUUM FULL dostał blokadę po 6,3 s czekania, a odczyt jednego zamówienia, który zwykle trwa ułamek milisekundy, wykonał się po 10 274 ms. Na produkcji w tej kolejce stają wszystkie żądania aplikacji do tej tabeli.

lock_timeout: VACUUM FULL i ALTER TABLE, które rezygnują zamiast blokować

Przed poleceniami, które biorą blokadę ACCESS EXCLUSIVE (VACUUM FULL, większość ALTER TABLE, CREATE INDEX bez CONCURRENTLY), ustawiamy w sesji lock_timeout. Według dokumentacji parametrów klienta przerywa on polecenie, które czeka na blokadę dłużej niż podany czas; dokumentacja odradza ustawianie go w postgresql.conf, bo dotyczyłby wszystkich sesji. Ten sam test z otwartą transakcją raportu:

sklep=> \timing on
Timing is on.
sklep=> SET lock_timeout = '3s';
SET
Time: 0.750 ms
sklep=> VACUUM FULL orders;
ERROR:  canceling statement due to lock timeout
Time: 3001.678 ms (00:03.002)

Po 3 sekundach VACUUM FULL zrezygnował i zwolnił miejsce w kolejce, więc aplikacja straciła najwyżej 3 sekundy. Polecenie powtarzamy później, gdy długa transakcja się skończy.

pg_repack: odzyskanie miejsca bez długiej blokady tabeli

pg_repack to rozszerzenie i program, które usuwają rozdęcie z tabel i indeksów. Według dokumentacji pg_repack 1.5.3, w przeciwieństwie do CLUSTER i VACUUM FULL, pracuje przy działającej aplikacji i nie trzyma blokady wyłącznej przez cały czas przetwarzania: ACCESS EXCLUSIVE bierze tylko na krótko na początku i przy podmianie tabeli na końcu. Tabela musi mieć klucz główny albo indeks unikalny na kolumnie NOT NULL, uruchomić go może superużytkownik albo właściciel tabeli, a na dysku potrzeba wolnego miejsca około dwa razy większego niż tabela z indeksami. Debian 13 ma pakiet postgresql-17-repack w wersji 1.5.2-1, repozytorium PGDG wersję 1.5.3. Na serwerze bazy, jako root:

apt install postgresql-17-repack
sudo -u postgres psql -d sklep -c 'CREATE EXTENSION pg_repack;'
sudo -u postgres pg_repack -d sklep -t order_items

CREATE EXTENSION pg_repack zakłada w bazie schemat repack. Program pg_repack musi mieć tę samą wersję co rozszerzenie w bazie; po aktualizacji pakietu do 1.5.3 rozszerzenie trzeba było usunąć i założyć ponownie, co pokazujemy w części o aktualizacji. Przepisanie order_items trwało 24,3 s (INFO: repacking table "public.order_items"). Test pgbench w tym samym czasie, raport co 2 sekundy:

progress: 6.0 s, 8152.3 tps, lat 0.489 ms stddev 0.244, 0 failed
progress: 8.0 s, 5988.1 tps, lat 0.667 ms stddev 0.863, 0 failed
progress: 10.0 s, 6172.3 tps, lat 0.646 ms stddev 1.029, 0 failed
[...]
progress: 26.0 s, 5669.0 tps, lat 0.704 ms stddev 1.288, 0 failed
progress: 28.0 s, 5844.8 tps, lat 0.683 ms stddev 2.041, 0 failed
progress: 30.0 s, 7028.2 tps, lat 0.567 ms stddev 0.774, 0 failed
progress: 32.0 s, 9836.6 tps, lat 0.403 ms stddev 0.268, 0 failed

W czasie pracy pg_repack aplikacja obsługiwała od 5300 do 7200 transakcji na sekundę zamiast około 10 000 i nie dostała żadnego błędu. Tabela zmniejszyła się z 391 do 260 MB, a jej indeksy z 212 do 141 MB.

Autovacuum w PostgreSQL: czy go używać, kiedy startuje i jak go stroić dla dużych tabel

Autovacuum zostawiamy włączony. Rozdział dokumentacji o odkurzaniu nazywa go funkcją opcjonalną, ale bardzo zalecaną: automatycznie uruchamia VACUUM i ANALYZE w tabelach, w których przybyło martwych albo zmienionych wierszy. Wyłączenie go nie wyłącza całej pracy. Gdy w tabeli są niezamrożone wiersze starsze niż autovacuum_freeze_max_age transakcji, serwer i tak uruchomi VACUUM chroniący przed zawinięciem licznika transakcji, także przy autovacuum = off. Bez autovacuum martwe wiersze rosną, statystyki planera się starzeją, a najcięższy VACUUM przychodzi w chwili, której nie wybieramy. W scenariuszu archiwizacji autovacuum sam usunął 2,7 miliona martwych wierszy z dwóch tabel w niecałe 20 sekund pracy.

Kiedy autovacuum uruchamia VACUUM i ANALYZE: progi i wzór

Proces nadzorczy autovacuum co autovacuum_naptime (domyślnie minuta) sprawdza każdą bazę i uruchamia do autovacuum_max_workers (domyślnie 3) procesów roboczych. O tym, czy tabela kwalifikuje się do VACUUM, decydują progi z dokumentacji:

• VACUUM po zmianach i usunięciach: martwe wiersze > autovacuum_vacuum_threshold (50) + autovacuum_vacuum_scale_factor (0,2) × liczba wierszy tabeli;
• VACUUM po samych wstawieniach: nowe wiersze od ostatniego VACUUM > autovacuum_vacuum_insert_threshold (1000) + autovacuum_vacuum_insert_scale_factor (0,2) × liczba wierszy;
• ANALYZE: wiersze zmienione od ostatniego ANALYZE > autovacuum_analyze_threshold (50) + autovacuum_analyze_scale_factor (0,1) × liczba wierszy.

Wartości w nawiasach to domyślne w PostgreSQL 17.11 i 18.6, odczytane z pg_settings w labie. PostgreSQL 18 ogranicza pierwszy próg od góry parametrem autovacuum_vacuum_max_threshold, domyślnie 100 000 000 martwych wierszy, i dodaje autovacuum_worker_slots, dzięki któremu autovacuum_max_workers można zmienić bez restartu (informacje o wydaniu 18). Autovacuum celowo pracuje wolniej niż ręczny VACUUM: według dokumentacji opóźnienia kosztowego po wykonaniu pracy o koszcie vacuum_cost_limit (200) proces robi przerwę na czas autovacuum_vacuum_cost_delay (2 ms), żeby nie zabrać aplikacji całego dysku. Ręczny VACUUM domyślnie takich przerw nie robi (vacuum_cost_delay = 0).

Próg dla każdej tabeli, z uwzględnieniem ustawień tabeli, liczy zapytanie:

SELECT s.relname AS tabela,
       c.reltuples::bigint AS reltuples,
       s.n_dead_tup,
       round(coalesce((SELECT option_value FROM pg_options_to_table(c.reloptions)
                        WHERE option_name = 'autovacuum_vacuum_threshold')::numeric,
                      current_setting('autovacuum_vacuum_threshold')::numeric)
           + coalesce((SELECT option_value FROM pg_options_to_table(c.reloptions)
                        WHERE option_name = 'autovacuum_vacuum_scale_factor')::numeric,
                      current_setting('autovacuum_vacuum_scale_factor')::numeric)
             * greatest(c.reltuples, 0)::numeric) AS prog_vacuum,
       s.last_autovacuum,
       s.autovacuum_count
FROM pg_stat_user_tables AS s
JOIN pg_class AS c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC;

   tabela    | reltuples | n_dead_tup | prog_vacuum | last_autovacuum | autovacuum_count
-------------+-----------+------------+-------------+-----------------+------------------
 order_items |   6000000 |    2005649 |     1200050 |                 |                0
 orders      |   1992859 |     668549 |      398622 |                 |                0
 customers   |    200000 |          2 |       40050 |                 |                0
(3 rows)

To stan zaraz po archiwizacji: martwych wierszy jest więcej niż wynosi próg, więc obie tabele trafiły do autovacuum przy najbliższym przeglądzie bazy. Domyślny współczynnik 0,2 oznacza jednak, że tabela z 2 milionami wierszy czeka na prawie 400 000 martwych wierszy, a tabela ze 100 milionami na 20 milionów. W PostgreSQL 17 dla dużych tabel trzeba więc ustawić niższy próg ręcznie. Zapytanie nie uwzględnia limitu z wersji 18.

Strojenie autovacuum dla jednej tabeli: ALTER TABLE ... SET

Progi ustawiamy osobno dla dużych, często zmienianych tabel. Według dokumentacji ALTER TABLE zmiana parametrów autovacuum tabeli bierze blokadę SHARE UPDATE EXCLUSIVE, która nie przeszkadza w odczytach i zapisach:

sklep=> ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 1000);
ALTER TABLE
sklep=> \d+ orders
                                                               Table "public.orders"
   Column    |           Type           | Collation | Nullable |           Default            | Storage  | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
 id          | bigint                   |           | not null | generated always as identity | plain    |             |              |
 customer_id | bigint                   |           | not null |                              | plain    |             |              |
 status      | text                     |           | not null |                              | extended |             |              |
 created_at  | timestamp with time zone |           | not null |                              | plain    |             |              |
 total       | numeric(12,2)            |           | not null |                              | main     |             |              |
 note        | text                     |           |          |                              | extended |             |              |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_customer_id_created_at_idx" btree (customer_id, created_at)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)
Referenced by:
    TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)
Access method: heap
Options: autovacuum_vacuum_scale_factor=0.02, autovacuum_vacuum_threshold=1000

Dla orders po archiwizacji (1 331 451 wierszy) domyślne współczynniki dawały próg 266 340 martwych wierszy; po zmianie zapytanie o progi pokazało 27 629. Ustawienia tabeli cofa ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold);.

Długa transakcja blokuje sprzątanie: dead but not yet removable w VACUUM VERBOSE

VACUUM może usunąć tylko te martwe wiersze, których nie potrzebuje już żadna otwarta transakcja. Dokumentacja parametru idle_in_transaction_session_timeout pisze wprost, że otwarta transakcja blokuje usuwanie niedawno unieważnionych wierszy, które mogą być dla niej widoczne, więc długo bezczynna transakcja przyczynia się do rozrostu tabel. W labie rola raport rozpoczęła transakcję w trybie REPEATABLE READ, przeczytała orders i została bezczynna. W tym czasie aplikacja zmieniła status 284 970 zamówień, a my uruchomiliśmy VACUUM:

sklep=> VACUUM (VERBOSE) orders;
INFO:  vacuuming "sklep.public.orders"
INFO:  launched 1 parallel vacuum worker for index vacuuming (planned: 1)
INFO:  finished vacuuming "sklep.public.orders": index scans: 1
pages: 0 removed, 35158 remain, 35158 scanned (100.00% of total)
tuples: 0 removed, 2284970 remain, 284970 are dead but not yet removable
removable cutoff: 8115, which was 1 XIDs old when operation ended
[...]

VACUUM przeczytał całą tabelę i nie usunął ani jednego z 284 970 martwych wierszy. Sesję, która trzyma granicę removable cutoff, znajdujemy po kolumnie backend_xmin:

SELECT pid, usename, datname, state,
       backend_xmin,
       age(backend_xmin) AS wiek_xmin,
       clock_timestamp() - xact_start AS trwa_transakcja,
       left(query, 40) AS ostatnie_zapytanie
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

  pid   | usename  | datname  |        state        | backend_xmin | wiek_xmin | trwa_transakcja |          ostatnie_zapytanie
--------+----------+----------+---------------------+--------------+-----------+-----------------+--------------------------------------
 146115 | raport   | sklep    | idle in transaction |         8115 |         1 | 00:00:11.123798 | SELECT count(*) FROM orders;
 146125 | postgres | postgres | active              |         8116 |         0 | 00:00:00.007124 | SELECT pid, usename, datname, state,
(2 rows)

backend_xmin sesji raport to 8115, czyli dokładnie removable cutoff z raportu VACUUM. idle in transaction oznacza według dokumentacji, że sesja jest w transakcji, ale nie wykonuje zapytania. W labie age(backend_xmin) wynosi 1, bo od tej chwili prawie nie powstawały nowe transakcje; na ruchliwym serwerze ta liczba rośnie z każdą transakcją. Po zakończeniu sesji raport ten sam VACUUM usunął wszystko:

postgres=# SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity
           WHERE usename = 'raport' AND state = 'idle in transaction';
  pid   | pg_terminate_backend
--------+----------------------
 146115 | t
(1 row)

sklep=> VACUUM (VERBOSE) orders;
[...]
tuples: 284970 removed, 1992859 remain, 0 are dead but not yet removable

Żeby nie robić tego ręcznie, ustawiamy limit bezczynności w transakcji dla ról narzędzi, które zostawiają otwarte transakcje. W labie, żeby test był krótki, 5 sekund:

postgres=# ALTER ROLE raport IN DATABASE sklep SET idle_in_transaction_session_timeout = '5s';
ALTER ROLE

Nowa sesja raport, która po BEGIN i jednym zapytaniu milczała 8 sekund, dostała FATAL: terminating connection due to idle-in-transaction timeout. Ustawienie widać w \drds. Wartość dobieramy do najdłuższej przerwy, jaką dana aplikacja robi w otwartej transakcji. Ten sam rozdział dokumentacji wymienia jeszcze dwa źródła starej granicy: przygotowane i niezatwierdzone transakcje (pg_prepared_xacts) oraz stare sloty replikacji (pg_replication_slots, kolumny xmin i catalog_xmin).

Monitorowanie autovacuum: pg_stat_user_tables, log i postęp VACUUM

postgres=# SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
           FROM pg_stat_user_tables ORDER BY relname;
   relname   | n_live_tup | n_dead_tup |        last_autovacuum        |       last_autoanalyze
-------------+------------+------------+-------------------------------+-------------------------------
 customers   |     200000 |          2 |                               |
 order_items |    3984652 |          0 | 2026-10-14 11:00:06.834046+00 | 2026-10-14 11:00:07.975287+00
 orders      |    1330917 |          0 | 2026-10-14 10:59:50.252335+00 | 2026-10-14 10:59:51.619785+00
(3 rows)

Tabela, w której n_dead_tup stale rośnie, a last_autovacuum się nie zmienia, ma za wysoki próg albo coś blokuje sprzątanie. W PostgreSQL 18 widok ma też kolumny total_vacuum_time, total_autovacuum_time, total_analyze_time i total_autoanalyze_time z łącznym czasem tych operacji. Trwający VACUUM, także uruchomiony przez autovacuum, pokazuje widok pg_stat_progress_vacuum, a szczegóły zakończonych przebiegów log przy log_autovacuum_min_duration.

Zawinięcie licznika transakcji: wiek datfrozenxid i autovacuum_freeze_max_age

Numer transakcji (XID) ma 32 bity. Według dokumentacji klaster, który wykona ponad 4 miliardy transakcji, bez zamrażania starych wierszy doszedłby do zawinięcia licznika: transakcje z przeszłości zaczęłyby wyglądać na przyszłe, a ich dane stałyby się niewidoczne. Zapobiega temu zamrażanie wierszy przez VACUUM. Gdy najstarszy niezamrożony wiersz tabeli jest starszy niż autovacuum_freeze_max_age (domyślnie 200 000 000 transakcji), autovacuum przetwarza tabelę obowiązkowo, a po przekroczeniu vacuum_failsafe_age (1 600 000 000) VACUUM przechodzi w tryb awaryjny, w którym według dokumentacji VACUUM pomija między innymi czyszczenie indeksów. Wiek baz sprawdza zapytanie:

SELECT datname, age(datfrozenxid) AS wiek_xid, mxid_age(datminmxid) AS wiek_mxid,
       current_setting('autovacuum_freeze_max_age') AS autovacuum_freeze_max_age
FROM pg_database ORDER BY 2 DESC;

  datname  | wiek_xid | wiek_mxid | autovacuum_freeze_max_age
-----------+----------+-----------+---------------------------
 template0 |     7750 |         0 | 200000000
 postgres  |     7745 |         0 | 200000000
 template1 |     7745 |         0 | 200000000
 sklep     |     7745 |         0 | 200000000
(4 rows)

Na serwerze labowym wiek to kilka tysięcy transakcji. Na produkcji alarm ustawiamy na wartość wyraźnie niższą od autovacuum_freeze_max_age, żeby mieć czas na znalezienie przyczyny, zanim przyjdzie wymuszony VACUUM.

Tablespace w PostgreSQL: czym jest, jak go utworzyć i czego nie zastąpi

Według dokumentacji PostgreSQL tablespace to wskazane przez administratora miejsce w systemie plików, w którym PostgreSQL zapisuje pliki obiektów bazy: tabel, indeksów i tabel tymczasowych. Każdy klaster ma dwa wbudowane: pg_default w katalogu danych, domyślny dla baz i ich obiektów, oraz pg_global na katalogi systemowe wspólne dla całego klastra. Własny tablespace ma sens, gdy część danych ma leżeć na innym dysku, na przykład duże archiwum na tańszym i wolniejszym, a pliki tymczasowe sortowań na szybkim (parametr temp_tablespaces).

Tworzenie tablespace i przeniesienie tabeli z indeksami

Katalog musi istnieć, być pusty i należeć do konta postgres. Na serwerze bazy, jako root (w labie /srv leży na tym samym dysku, na produkcji byłby to punkt montowania innego dysku):

install -d -o postgres -g postgres -m 0700 /srv/pg_archiwum
sudo -u postgres psql -c "CREATE TABLESPACE archiwum OWNER postgres LOCATION '/srv/pg_archiwum';"
sudo -u postgres psql -c "GRANT CREATE ON TABLESPACE archiwum TO sklep;"

postgres=# \db+
                                       List of tablespaces
    Name    |  Owner   |     Location     |  Access privileges  | Options |  Size   | Description
------------+----------+------------------+---------------------+---------+---------+-------------
 archiwum   | postgres | /srv/pg_archiwum | postgres=C/postgres+|         | 0 bytes |
            |          |                  | sklep=C/postgres    |         |         |
 pg_default | postgres |                  |                     |         | 697 MB  |
 pg_global  | postgres |                  |                     |         | 621 kB  |
(3 rows)

# ls -l /var/lib/postgresql/17/main/pg_tblspc/
lrwxrwxrwx 1 postgres postgres 16 Oct 14 11:14 16776 -> /srv/pg_archiwum
# find /srv/pg_archiwum -maxdepth 2
/srv/pg_archiwum
/srv/pg_archiwum/PG_17_202406281

W katalogu danych powstało dowiązanie symboliczne nazwane numerem OID (ang. Object Identifier) tablespace, a w samym katalogu podkatalog z wersją PostgreSQL i numerem wersji katalogu systemowego. Tabelę archiwum tworzymy jako sklep:

sklep=> CREATE TABLE orders_archive (LIKE orders INCLUDING ALL) TABLESPACE archiwum;
sklep=> INSERT INTO orders_archive OVERRIDING SYSTEM VALUE
        SELECT * FROM orders WHERE created_at < timestamptz '2025-01-01 00:00:00+00';
INSERT 0 144305
sklep=> SELECT c.relname, coalesce(t.spcname, 'pg_default') AS tablespace
        FROM pg_class c LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
        WHERE c.relname LIKE 'orders_archive%';
                  relname                  | tablespace
-------------------------------------------+------------
 orders_archive                            | archiwum
 orders_archive_customer_id_created_at_idx | pg_default
 orders_archive_id_seq                     | pg_default
 orders_archive_pkey                       | pg_default
(4 rows)

Klauzula TABLESPACE w CREATE TABLE dotyczy tylko tabeli: indeksy skopiowane przez INCLUDING ALL trafiły do pg_default. Tak samo działa przenoszenie istniejącej tabeli. Według dokumentacji ALTER TABLE ALTER TABLE ... SET TABLESPACE przenosi pliki tabeli, ale nie jej indeksów, i jak większość form ALTER TABLE bierze blokadę ACCESS EXCLUSIVE na czas kopiowania. Indeksy przenosimy osobno:

ALTER INDEX orders_archive_pkey SET TABLESPACE archiwum;
ALTER INDEX orders_archive_customer_id_created_at_idx SET TABLESPACE archiwum;

Tablespace a kopie zapasowe i usuwanie

Tablespace wpływa na kopie zapasowe. pg_basebackup w formacie tar zapisał go w osobnym pliku 16776.tar.gz, obok base.tar.gz i pg_wal.tar.gz. pg_dumpall --globals-only zapisuje polecenie CREATE TABLESPACE archiwum OWNER postgres LOCATION '/srv/pg_archiwum'; ze ścieżką, więc na serwerze docelowym ten katalog musi istnieć, a pg_dump przy obiektach ustawia SET default_tablespace = archiwum;. Pusty tablespace usuwa DROP TABLESPACE archiwum;; dopóki są w nim obiekty, polecenie kończy się błędem tablespace "archiwum" is not empty.

Kopie zapasowe PostgreSQL: pg_dump, pg_basebackup, archiwum WAL i odtwarzanie do punktu w czasie

PostgreSQL ma dwa rodzaje kopii. Kopia logiczna (pg_dump) zapisuje definicje i dane jednej bazy jako polecenia SQL albo archiwum, z którego można odtworzyć całość albo wybrane obiekty. Nadaje się też do przeniesienia danych na nowszą wersję główną. Odtwarzanie dużej bazy trwa jednak długo, bo serwer musi wstawić wszystkie wiersze i zbudować indeksy od nowa, a kopia zawiera stan z chwili zrzutu. Kopia fizyczna (pg_basebackup) kopiuje pliki całego klastra: wszystkie bazy i role. Odtworzenie nie wymaga ponownego wstawiania wierszy ani budowania indeksów, ale działa tylko z programami tej samej wersji głównej, bo format katalogu danych zmienia się między wersjami głównymi.

Kopię fizyczną można uzupełnić o ciągłe archiwum WAL. WAL to dziennik, do którego serwer zapisuje każdą zmianę, zanim trafi ona do plików tabel. Kopia bazowa i wszystkie późniejsze pliki WAL pozwalają odtworzyć klaster do dowolnej chwili po wykonaniu kopii: to odtwarzanie do punktu w czasie, PITR (ang. Point-In-Time Recovery). Pokazujemy obie metody, a na końcu odtwarzamy bazę sklepu do chwili tuż przed błędnym UPDATE.

Kopia logiczna jednej bazy: pg_dump -Fc i pg_dumpall --globals-only

Na serwerze bazy, jako root:

install -d -o postgres -g postgres -m 0700 /var/backups/postgresql
sudo -u postgres pg_dump -Fc -f /var/backups/postgresql/sklep-$(date +%F).dump sklep
sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals-$(date +%F).sql

Według dokumentacji pg_dump program tworzy spójną kopię, nawet gdy baza jest w użyciu, i nie blokuje innych użytkowników, ani czytających, ani zapisujących. Zrzuca jednak tylko jedną bazę: role i tablespace, wspólne dla całego klastra, zapisuje pg_dumpall --globals-only. Format -Fc (custom) jest domyślnie kompresowany i pozwala na wybiórcze i równoległe odtwarzanie; równoległy zrzut (-j) obsługuje tylko format katalogowy -Fd. Zrzut bazy sklepu trwał 20,3 s i dał plik 93 792 647 bajtów (89 MiB).

Plik z rolami zawiera skróty haseł, więc traktujemy go jak dane poufne i trzymamy w katalogu z prawami 0700:

\restrict 2dA3vRYOVLRgE72AbSYetNJsvcSvkriVCwGrKzJ6GXeefEHylRE5z6f7zVdFfuy
SET default_transaction_read_only = off;
[...]
CREATE ROLE raport;
ALTER ROLE raport WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:<skrót>';
CREATE ROLE sklep;
ALTER ROLE sklep WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:<skrót>';
\unrestrict 2dA3vRYOVLRgE72AbSYetNJsvcSvkriVCwGrKzJ6GXeefEHylRE5z6f7zVdFfuy

Linie \restrict i \unrestrict z losowym kluczem dodaje pg_dump od wydań z 14 sierpnia 2025 r. (m.in. 17.6). Według informacji o wydaniu 17.6 to poprawka CVE-2025-8714 (CVE, ang. Common Vulnerabilities and Exposures): psql dostał polecenie \restrict, które blokuje wykonywanie kolejnych metapoleceń, a pg_dump wstawia je przed danymi pochodzącymi z serwera źródłowego. Przejęty serwer źródłowy nie może więc przemycić w zrzucie metapolecenia, które uruchomiłoby program na koncie osoby odtwarzającej kopię.

Test odtworzenia kopii pg_dump do osobnej bazy

Kopia, której nie odtworzyliśmy, jest tylko plikiem. Test robimy na tym samym serwerze, do nowej bazy, i porównujemy liczbę wierszy oraz sumę kwot zamówień:

# sudo -u postgres createdb -O sklep sklep_test
# time sudo -u postgres pg_restore -d sklep_test -j 2 /var/backups/postgresql/sklep-2026-10-14.dump
real    0m16.459s
# for db in sklep sklep_test; do sudo -u postgres psql -X -d $db -Atc "SELECT current_database(), (SELECT count(*) FROM customers), (SELECT count(*) FROM orders), (SELECT count(*) FROM order_items), (SELECT sum(total) FROM orders)"; done
sklep|200000|1331451|3994351|672039081.67
sklep_test|200000|1331451|3994351|672039081.67
# sudo -u postgres dropdb sklep_test

Odtworzenie w dwóch połączeniach (-j 2) trwało 16,5 s, a liczby się zgadzają. Obiekty dostały właściciela sklep, bo zrzut zawiera polecenia ALTER ... OWNER TO. Na nowym serwerze role muszą istnieć wcześniej, więc najpierw wczytujemy plik z rolami (sudo -u postgres psql -f globals-2026-10-14.sql). pg_restore -l wypisuje spis zawartości archiwum, a spis po edycji podany przez -L pozwala odtworzyć tylko wybrane obiekty:

# sudo -u postgres pg_restore -l /var/backups/postgresql/sklep-2026-10-14.dump | grep -v '^;'
[...]
223; 1259 16403 TABLE public orders sklep
3505; 0 0 ACL public TABLE orders sklep
222; 1259 16402 SEQUENCE public orders_id_seq sklep
3488; 0 16392 TABLE DATA public customers sklep
3492; 0 16416 TABLE DATA public order_items sklep
3490; 0 16403 TABLE DATA public orders sklep
[...]
3339; 2606 16423 FK CONSTRAINT public order_items order_items_order_id_fkey sklep
3338; 2606 16410 FK CONSTRAINT public orders orders_customer_id_fkey sklep
2098; 826 16487 DEFAULT ACL public DEFAULT PRIVILEGES FOR TABLES sklep

Z opcją -C pg_restore sam tworzy bazę razem z jej uprawnieniami. W naszym zrzucie znalazły się REVOKE CONNECT,TEMPORARY ON DATABASE sklep FROM PUBLIC; i GRANT CONNECT ON DATABASE sklep TO raport;.

Kopie narzędziami Debiana i Ubuntu: pg_backupcluster oraz jednostki pg_dump@, pg_basebackup@ i pg_receivewal@

Pakiet postgresql-common zawiera program pg_backupcluster, który według swojej strony podręcznika jest prostą nakładką na pg_basebackup i pg_dump, program pg_restorecluster do odtwarzania takich kopii oraz gotowe szablony jednostek systemd. Sprawdziliśmy, że są w pakietach Debiana 12 (248+deb12u1) i 13 (278) oraz Ubuntu 22.04 (238), 24.04 (257build1.1) i 26.04 (290ubuntu1); testowaliśmy je na Debianie 13. Kopie trafiają do /var/backups/postgresql/17-main/:

Tydzień między kopiami i 3 kopie to dla bazy sklepu za mało. Harmonogram i retencję zmieniamy plikami nadpisującymi (drop-in) w /etc/systemd/system, nie ruszając jednostek z pakietu. Na serwerze bazy, jako root:

mkdir -p /etc/systemd/system/pg_dump@17-main.timer.d /etc/systemd/system/pg_dump@17-main.service.d
cat > /etc/systemd/system/pg_dump@17-main.timer.d/codziennie.conf <<'EOF'
[Timer]
OnCalendar=
OnCalendar=*-*-* 02:30:00
EOF
cat > /etc/systemd/system/pg_dump@17-main.service.d/retencja.conf <<'EOF'
[Service]
Environment="KEEP=14"
EOF
systemctl daemon-reload
systemctl enable --now pg_dump@17-main.timer pg_basebackup@17-main.timer

Pusta linia OnCalendar= kasuje domyślny harmonogram tygodniowy. Jednostka z pakietu ma też RandomizedDelaySec=1h i FixedRandomDelay=true, więc kopia startuje o 02:30 plus stałe dla tej maszyny i tej jednostki opóźnienie do godziny. enable dopina timery do usługi klastra (postgresql@17-main.service.wants), więc działają razem z nim:

# systemctl list-timers --all --no-pager "pg_*"
NEXT                          LEFT LAST PASSED UNIT                        ACTIVATES
Thu 2026-10-15 02:50:02 UTC    15h -         - pg_dump@17-main.timer       pg_dump@17-main.service
Mon 2026-10-19 00:19:58 UTC 4 days -         - pg_basebackup@17-main.timer pg_basebackup@17-main.service

Pierwszą kopię uruchamiamy ręcznie i sprawdzamy jej zawartość:

# systemctl start pg_dump@17-main.service
# pg_backupcluster 17 main list
Cluster 17 main backups in /var/backups/postgresql/17-main:
Dumps:
  /var/backups/postgresql/17-main/2026-10-14T111247Z.dump: 93577045 Bytes
Basebackups:
  /var/backups/postgresql/17-main/2026-10-14T110839Z.backup: 181391027 Bytes
WAL:
  /var/backups/postgresql/17-main/wal: 652445900 Bytes, 134 Files
Total: 927413972 Bytes
# ls /var/backups/postgresql/17-main/2026-10-14T111247Z.dump/
config.tar.gz  createcluster.opts  globals.sql  postgres.dump  sklep.dump  status  template1.dump
# cat /var/backups/postgresql/17-main/2026-10-14T111247Z.dump/createcluster.opts
--encoding UTF8 --lc-collate C.UTF-8 --lc-ctype C.UTF-8 --locale-provider libc

Kopia trwała 21 s. Każda baza ma własny plik .dump, obok są role, kopia konfiguracji i opcje, z którymi pg_restorecluster utworzy klaster przy odtwarzaniu. Plik status zawiera wynik przebiegu w formacie JSON. Strona podręcznika z pakietu 278 opisuje katalogi kopii fizycznych jako *.basebackup, ale program tworzy i rozpoznaje katalogi *.backup, co widać w kodzie pg_backupcluster i pg_restorecluster.

Kopia fizyczna i ciągłe archiwum WAL: pg_basebackup@ i pg_receivewal@ ze slotem replikacji

Do odtwarzania do punktu w czasie potrzebna jest kopia bazowa i wszystkie pliki WAL powstałe po niej. pg_receivewal@17-main.service odbiera WAL protokołem replikacji przez gniazdo uniksowe, jako postgres; pozwala na to domyślna reguła local replication all peer. Nie trzeba włączać archive_mode ani restartować serwera:

systemctl enable --now pg_receivewal@17-main.service
systemctl start pg_basebackup@17-main.service

# systemctl status pg_receivewal@17-main.service
[...]
   Main PID: 147583 (pg_receivewal)
[...]
             `-147583 /usr/lib/postgresql/17/bin/pg_receivewal -D /var/backups/postgresql/17-main/wal --slot pg_receivewal_service --compress=5
# sudo -u postgres psql -c "SELECT slot_name, slot_type, active, restart_lsn, wal_status FROM pg_replication_slots;"
       slot_name       | slot_type | active | restart_lsn | wal_status
-----------------------+-----------+--------+-------------+------------
 pg_receivewal_service | physical  | t      | 1/28000000  | reserved
(1 row)

Kopia bazowa trwała 36 s i zajęła 181 391 027 bajtów (173 MiB) przy klastrze o rozmiarze około 700 MB. Slot replikacji sprawia, że serwer przechowuje WAL, dopóki pg_receivewal nie potwierdzi jego odbioru. Chroni to archiwum przed lukami, ale ma koszt: w labie zatrzymaliśmy usługę i przepisaliśmy obie duże tabele przez VACUUM FULL. Slot przestał być aktywny, a serwer zatrzymał 612 MB WAL, którego nie mógł usunąć:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_zatrzymany
FROM pg_replication_slots;

       slot_name       | active | wal_status | wal_zatrzymany
-----------------------+--------+------------+----------------
 pg_receivewal_service | f      | reserved   | 612 MB
(1 row)

Według dokumentacji parametru max_slot_wal_keep_size domyślne -1 pozwala slotom zatrzymać nieograniczoną ilość WAL. Usługa odbioru WAL, która przestała działać na kilka dni, może więc zapełnić dysk serwera bazy. To zapytanie podpinamy do monitoringu: alarm, gdy active = f albo gdy wal_zatrzymany rośnie. Limit max_slot_wal_keep_size chroni dysk, ale po jego przekroczeniu serwer usuwa WAL potrzebny slotowi, więc archiwum ma lukę i trzeba zrobić nową kopię bazową. Po ponownym uruchomieniu usługi pg_receivewal nadrabia zaległości; w labie po 20 s wciąż brakowało 308 MB.

Klasyczną alternatywą jest archive_command, który serwer wywołuje dla każdego zapełnionego pliku WAL. Ten wariant też sprawdziliśmy: archive_mode = on wymaga restartu (w pg_settings było pending_restart = t), a polecenie w postaci z rozdziału o archiwizacji ciągłej, test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f, kopiowało pliki, co pokazał widok pg_stat_archiver (archived_count, last_archived_wal, failed_count = 0). W obu wariantach archiwum musi trafiać poza serwer bazy.

Odtwarzanie do punktu w czasie: pg_restorecluster --pitr po błędnym UPDATE

Po kopii bazowej aplikacja dodała 1000 zamówień. O godzinie T1 = 2026-10-14 11:09:44.654226+00 wszystko było w porządku, a dwie sekundy później ktoś zmienił status wszystkich zamówień:

sklep=> UPDATE orders SET status = 'cancelled';
UPDATE 1332451
Time: 20624.212 ms (00:20.624)

Nie odtwarzamy produkcji w miejscu. Program pg_restorecluster zakłada obok nowy klaster 17/odtw z kopii bazowej i odtwarza go z archiwum WAL do podanej chwili:

# pg_restorecluster 17 odtw /var/backups/postgresql/17-main/2026-10-14T110839Z.backup \
      --pitr '2026-10-14 11:09:44.654226+00' --start
Restoring /var/backups/postgresql/17-main/2026-10-14T110839Z.backup/config.tar.gz to /etc/postgresql/17/odtw ...
Setting cluster_name = 17/odtw
[...]
Setting port = 5433
Restoring /var/backups/postgresql/17-main/2026-10-14T110839Z.backup/base.tar.gz to /var/lib/postgresql/17/odtw ...
Restoring /var/backups/postgresql/17-main/2026-10-14T110839Z.backup/pg_wal.tar.gz to /var/lib/postgresql/17/odtw/pg_wal ...
Setting archive recovery options
Starting cluster 17 odtw ...
Waiting for end of recovery ...
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
[...]
real    0m43.990s

Program odtworzył konfigurację z kopii pod nową nazwą, wybrał wolny port 5433, rozpakował kopię i WAL, ustawił restore_command pobierający pliki WAL z archiwum (przez skrypt pg_getwal, który obsługuje też pliki .gz i niedokończone .partial), recovery_target_time i recovery_target_action = 'promote', uruchomił klaster, poczekał na koniec odtwarzania, usunął te ustawienia i przeliczył statystyki. Całość trwała 44 s. Log odtworzonego klastra:

2026-10-14 11:10:29.562 UTC [148161] LOG:  starting point-in-time recovery to 2026-10-14 11:09:44.654226+00
2026-10-14 11:10:29.903 UTC [148161] LOG:  consistent recovery state reached at 1/29000158
2026-10-14 11:10:52.748 UTC [148161] LOG:  recovery stopping before commit of transaction 8515, time 2026-10-14 11:10:07.372973+00
2026-10-14 11:10:52.748 UTC [148161] LOG:  redo done at 1/5BB01370 system usage: CPU: user: 8.75 s, system: 1.68 s, elapsed: 23.15 s
2026-10-14 11:10:52.748 UTC [148161] LOG:  last completed transaction was at log time 2026-10-14 11:09:42.472303+00
2026-10-14 11:10:52.766 UTC [148161] LOG:  selected new timeline ID: 2
2026-10-14 11:10:52.890 UTC [148161] LOG:  archive recovery complete

Odtwarzanie zatrzymało się przed zatwierdzeniem transakcji 8515, czyli błędnego UPDATE (zatwierdzonego o 11:10:07). Ostatnią odtworzoną transakcją było dodanie 1000 zamówień o 11:09:42. Klaster przeszedł na nową linię czasu (ang. timeline) 2, więc jego dalsza historia nie miesza się z historią produkcji. Dane w odtworzonym klastrze:

# sudo -u postgres psql -p 5433 -d sklep -c "SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;"
  status   |  count
-----------+---------
 shipped   | 1124657
 new       |  134199
 cancelled |   66139
 paid      |    7456
(4 rows)
# sudo -u postgres psql -p 5433 -d sklep -Atc "SELECT count(*) FROM orders WHERE note = 'po kopii bazowej';"
1000

Naprawa wybiórcza: przeniesienie poprawnych danych z odtworzonego klastra przez \copy

Cofnięcie całej produkcji do T1 skasowałoby wszystko, co aplikacja zapisała po błędzie. Skoro poprawne dane są w klastrze obok, przenosimy tylko uszkodzoną kolumnę. \copy zapisuje wynik zapytania do pliku CSV (ang. Comma-Separated Values) po stronie klienta psql:

# sudo -u postgres psql -p 5433 -d sklep -c "\copy (SELECT id, status FROM orders) TO '/var/lib/postgresql/statusy.csv' WITH (FORMAT csv)"
COPY 1332451

sklep=# BEGIN;
BEGIN
sklep=# CREATE TEMP TABLE statusy_odtw (id bigint PRIMARY KEY, status text NOT NULL);
CREATE TABLE
sklep=# \copy statusy_odtw FROM '/var/lib/postgresql/statusy.csv' WITH (FORMAT csv)
COPY 1332451
sklep=# UPDATE orders AS o
        SET status = s.status
        FROM statusy_odtw AS s
        WHERE s.id = o.id
          AND o.status IS DISTINCT FROM s.status;
UPDATE 1266312
Time: 16880.746 ms (00:16.881)
sklep=# COMMIT;
COMMIT
sklep=# SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;
  status   |  count
-----------+---------
 shipped   | 1124657
 new       |  134199
 cancelled |   66139
 paid      |    7456
(4 rows)

Zmieniło się 1 266 312 wierszy, bo 66 139 zamówień miało status cancelled także przed błędem. Rozkład statusów na produkcji jest taki sam jak w odtworzonym klastrze. Na koniec usuwamy plik CSV i odtworzony klaster:

rm /var/lib/postgresql/statusy.csv
pg_dropcluster 17 odtw --stop

Odtworzony klaster dostaje tę samą konfigurację co produkcja, w tym shared_buffers = 512MB z conf.d. Na tym samym serwerze to druga porcja pamięci, więc przed odtworzeniem obok produkcji sprawdzamy, czy RAM ją zmieści.

Odtworzenie kopii logicznej jako nowego klastra: pg_restorecluster

Z katalogu *.dump ten sam program tworzy nowy klaster z opcjami z createcluster.opts, wczytuje role i odtwarza każdą bazę:

# time pg_restorecluster 17 zdumpu /var/backups/postgresql/17-main/2026-10-14T111247Z.dump
[...]
real    0m30.886s
# sudo -u postgres psql -p 5433 -c "\du" -c "\l sklep"
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 raport    |
 sklep     |

                                            List of databases
 Name  | Owner | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules | Access privileges
-------+-------+----------+-----------------+---------+---------+--------+-----------+-------------------
 sklep | sklep | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | sklep=CTc/sklep  +
       |       |          |                 |         |         |        |           | raport=c/sklep
(1 row)
# pg_dropcluster 17 zdumpu --stop

Odtworzenie trwało 31 s, role i uprawnienia do bazy wróciły, a liczby wierszy i suma kwot zgadzały się z produkcją.

Weryfikacja kopii: pg_verifybackup, test odtworzenia i kopia poza serwerem

pg_verifybackup (w /usr/lib/postgresql/17/bin/) sprawdza kopię fizyczną z jej plikiem backup_manifest. W PostgreSQL 17 nie obsługuje kopii w formacie tar: dla naszej kopii .tar.gz zgłosił fałszywe błędy w rodzaju "base.tar.gz" is present on disk but not in the manifest. Wersja 18 sprawdza kopie tar z opcją -n (--no-parse-wal) i na Ubuntu 26.04 odpowiedziała backup successfully verified. Pełnym testem jest jednak tylko odtworzenie, takie jak wyżej, wykonywane regularnie.

Katalog /var/backups/postgresql leży na serwerze bazy, więc nie przetrwa awarii tego serwera ani jego dysku. Kopie trzeba regularnie przenosić gdzie indziej, na przykład w modelu, w którym serwer kopii sam pobiera pliki: opisuje go artykuł Backup pull z rrsync: ograniczony klucz SSH i test odtworzenia. Przy większych instalacjach warto przyjrzeć się narzędziom przeznaczonym do kopii PostgreSQL, takim jak pgBackRest (w Debianie 13 pakiet 2.55.1-1) czy Barman (3.13.3-1); w tym artykule ich nie testowaliśmy. PostgreSQL 17 potrafi też robić kopie przyrostowe: pg_basebackup --incremental wymaga włączonego summarize_wal (domyślnie off w obu testowanych wersjach), a według dokumentacji pg_basebackup kopii przyrostowej nie da się użyć bezpośrednio, tylko po połączeniu z poprzednimi programem pg_combinebackup.

Aktualizacja PostgreSQL: wydanie poprawkowe przez apt i nowa wersja główna przez pg_upgradecluster

Wydanie poprawkowe: aktualizacja pakietu restartuje klaster

Wydanie poprawkowe instalujemy zwykłą aktualizacją pakietów. Według polityki wersjonowania nie wymaga to zrzutu i odtworzenia danych, ale może wymagać dodatkowych kroków, dlatego przed aktualizacją czytamy informacje o wydaniu. Skrypty pakietu zatrzymują klaster i uruchamiają go ponownie. Sprawdziliśmy to ponowną instalacją pakietu postgresql-18 na Debianie 13, która uruchamia te same skrypty co aktualizacja:

# sudo -u postgres psql -Atc "SELECT pg_postmaster_start_time();"
2026-10-14 11:26:44.628654+00
# apt-get install --reinstall postgresql-18
[...]
# sudo -u postgres psql -Atc "SELECT pg_postmaster_start_time();"
2026-10-14 11:27:10.017392+00
# grep -E "received fast shutdown|database system is ready to accept connections" /var/log/postgresql/postgresql-18-main.log | tail -2
2026-10-14 11:27:07.580 UTC [155034] LOG:  received fast shutdown request
2026-10-14 11:27:10.037 UTC [155945] LOG:  database system is ready to accept connections

Serwer nie przyjmował połączeń przez około 2,5 s. Zatrzymanie w trybie fast według dokumentacji pg_ctl wycofuje aktywne transakcje i rozłącza klientów, więc aktualizację poprawkową planujemy jak restart: w oknie serwisowym albo przy małym ruchu. Prawdziwa aktualizacja postgresql-17 z kompilacji Debiana do kompilacji PGDG zatrzymała tak samo klaster 17, ale go nie uruchomiła, bo był wtedy ustawiony na start ręczny (manual w start.conf); postgresql.service uruchamia tylko klastry z auto.

Repozytorium PGDG i pułapka metapakietu postgresql

Debian 13 dostarcza PostgreSQL 17. Wersję 18 daje repozytorium PGDG, które dodaje skrypt z pakietu postgresql-common. Na serwerze bazy, jako root:

# /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
[...]
You can now start installing packages from apt.postgresql.org.
# cat /etc/apt/sources.list.d/pgdg.sources
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: trixie-pgdg
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
# apt-cache policy postgresql postgresql-17 postgresql-18 libpq5 | grep -E "^[a-z]|Installed|Candidate"
postgresql:
  Installed: 17+278
  Candidate: 18+293.pgdg13+1
postgresql-17:
  Installed: 17.11-0+deb13u1
  Candidate: 17.11-1.pgdg13+2
postgresql-18:
  Installed: (none)
  Candidate: 18.6-1.pgdg13+2
libpq5:
  Installed: 17.11-0+deb13u1
  Candidate: 18.6-1.pgdg13+2

Symulacja (-s) pokazała dwa różne skutki zwykłej aktualizacji. apt-get upgrade podmienia pakiety wersji 17 z Debiana na kompilacje z PGDG (ta sama wersja 17.11, inny numer pakietu), a libpq5 na 18.6; metapakiet postgresql wstrzymuje. apt upgrade idzie dalej i przez metapakiet postgresql w wersji 18+293.pgdg13+1 instaluje postgresql-18, postgresql-client-18 i postgresql-18-jit, czyli nową wersję główną obok działającej. FAQ repozytorium PGDG (ang. Frequently Asked Questions, stan strony z 23 maja 2026 r.) przypomina, że zainstalowane pakiety dalej aktualizują się z PGDG, a automatyczne instalacje z tego repozytorium można ograniczyć przypięciem z priorytetem 200. Jak APT, czyli menedżer pakietów Debiana i Ubuntu, wybiera wersję pakietu dostępną w kilku repozytoriach, opisuje artykuł Priorytety APT: gdy pakiet jest i w dystrybucji, i w repozytorium zewnętrznym. My wybraliśmy prostsze rozwiązanie: oznaczamy zainstalowaną wersję jako zainstalowaną ręcznie i usuwamy metapakiet:

apt-mark manual postgresql-17 postgresql-client-17
apt remove postgresql

Usunięcie metapakietu nie rusza serwera ani danych. Po tej zmianie apt -s upgrade w labie nie proponował już postgresql-18, a nową wersję główną instalujemy jawnie, nazwą pakietu. Instalacja postgresql-18 obok działającego klastra 17/main nie założyła klastra 18/main; komentarz w /etc/postgresql-common/createcluster.conf (postgresql-common 278) wyjaśnia, że klaster main powstaje przy instalacji nowej wersji tylko wtedy, gdy nie istnieje jeszcze żaden klaster.

pg_upgradecluster: sprawdzenie pakietów, aktualizacja i port starego klastra

Program pg_upgradecluster przenosi klaster na nową wersję główną i kopiuje jego konfigurację. Ma cztery metody: dump (domyślna, przez pg_dump i pg_restore, wolna, ale bezpieczna), upgrade (przez pg_upgrade z kopiowaniem plików), link (twarde dowiązania zamiast kopii) i clone (klonowanie plików w systemach plików, które to obsługują). Według dokumentacji pg_upgrade tryb link jest dużo szybszy i nie potrzebuje miejsca na drugą kopię, ale po uruchomieniu nowego klastra starego nie można już bezpiecznie używać. Strona podręcznika dodaje, że metoda upgrade zachowuje ustawienie sum kontrolnych starego klastra, a metoda dump przy przejściu na PostgreSQL 18 je włącza. Najpierw sprawdzamy, czy dla nowej wersji są wszystkie pakiety rozszerzeń:

# pg_upgradecluster --check 17 main
postgresql-17-repack is installed, but postgresql-18-repack is missing
1 package(s) from version 17 missing for version 18
Install the missing packages and try pg_upgradecluster again. To ignore the issue, use --force-packages
Error: pg_upgradecluster pre-upgrade checks failed
# apt install postgresql-18-repack
# pg_upgradecluster --check 17 main
pg_upgradecluster pre-upgrade checks ok

Jednostki kopii zapasowych z poprzedniej części dotyczą konkretnego klastra (17-main). Po aktualizacji stary klaster dostanie inny port i zostanie zatrzymany, więc najpierw je wyłączamy, potem aktualizujemy metodą upgrade:

# systemctl disable --now pg_receivewal@17-main.service pg_dump@17-main.timer pg_basebackup@17-main.timer
# time pg_upgradecluster -m upgrade 17 main
[...]
Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
    /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
    /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-only
[...]
Disabling automatic startup of old cluster...
Starting upgraded cluster on port 5432...
Running finish phase upgrade hook scripts ...
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
[...]
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 17 main

real    0m14.738s
# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
17  main    5433 down   postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Aktualizacja bazy o rozmiarze około 700 MB trwała 14,7 s. Nowy klaster przejął port 5432, a stary dostał 5433 i start ręczny, więc aplikacja łączy się tam, gdzie wcześniej. PostgreSQL 18 według informacji o wydaniu przenosi przez pg_upgrade statystyki planera, ale nie wszystkie, o czym przypomina komunikat Some statistics are not transferred. Zalecane dwa polecenia vacuumdb wykonał już skrypt Debiana /etc/postgresql-common/pg_upgradecluster.d/analyze, który dla wersji 18 uruchamia najpierw vacuumdb --analyze-in-stages --missing-stats-only, a potem vacuumdb --analyze-only.

Po aktualizacji: pliki z conf.d nie przechodzą do nowego klastra

Nowy klaster działał, ale serwer aplikacji nie mógł się połączyć:

$ psql "host=192.168.123.7 dbname=sklep user=sklep sslmode=require" -c "SELECT version()"
psql: error: connection to server at "192.168.123.7", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

Katalog /etc/postgresql/18/main/conf.d/ był pusty, więc klaster 18 wystartował z listen_addresses = localhost, shared_buffers = 128MB i bez pg_stat_statements w shared_preload_libraries. W kodzie pg_upgradecluster z postgresql-common 278 widać, że program kopiuje postgresql.conf, postgresql.auto.conf, pg_hba.conf, pg_ident.conf, start.conf, pg_ctl.conf i pliki certyfikatów, ale nie katalog conf.d; wersja 293 z PGDG robi to samo. pg_hba.conf przeszedł bez zmian. Ustawienia wpisane przez ALTER SYSTEM przeszłyby więc z postgresql.auto.conf, a ustawienia z conf.d trzeba przenieść ręcznie:

# cp -a /etc/postgresql/17/main/conf.d/. /etc/postgresql/18/main/conf.d/
# systemctl reload postgresql@18-main
# sudo -u postgres psql -c "SELECT name, setting, error FROM pg_file_settings WHERE error IS NOT NULL;"
           name           |         setting         |            error
--------------------------+-------------------------+------------------------------
 listen_addresses         | localhost,192.168.123.7 | setting could not be applied
 shared_buffers           | 512MB                   | setting could not be applied
 shared_preload_libraries | pg_stat_statements      | setting could not be applied
(3 rows)
# systemctl restart postgresql@18-main

setting could not be applied przy tych trzech parametrach oznacza, że czekają na restart, a nie błąd w pliku; innych błędów nie było. Po restarcie serwer aplikacji połączył się z PostgreSQL 18.6. Strona podręcznika pg_upgradecluster opisuje opcję --no-start, która pozwala skopiować conf.d przed pierwszym uruchomieniem nowego klastra.

Rozszerzenia, kopie zapasowe i usunięcie starego klastra po aktualizacji

pg_upgrade zostawił w katalogu /var/log/postgresql/pg_upgradecluster-17-18-main.*/ skrypt update_extensions.sql:

\connect postgres
ALTER EXTENSION "pg_stat_statements" UPDATE;
\connect sklep
ALTER EXTENSION "pg_repack" UPDATE;

Uruchomiony jako postgres zaktualizował pg_stat_statements z 1.11 do 1.12, ale dla pg_repack skończył się błędem extension "pg_repack" has no update path from version "1.5.2" to version "1.5.3". Zgodnie z dokumentacją pg_repack starą wersję usuwamy i instalujemy nową; rozszerzenie nie przechowuje danych tabel:

sudo -u postgres psql -d sklep -c 'DROP EXTENSION pg_repack;' -c 'CREATE EXTENSION pg_repack;'

Kopie zapasowe trzeba włączyć dla nowego klastra. Slot replikacji pg_receivewal_service nie przeszedł przez pg_upgrade (w klastrze 18 nie było żadnego slotu), ale usługa zakłada go sama. Kopie bazowe i WAL klastra 17 da się odtworzyć tylko programami wersji 17, więc od razu robimy nową kopię bazową:

mv /etc/systemd/system/pg_dump@17-main.timer.d /etc/systemd/system/pg_dump@18-main.timer.d
mv /etc/systemd/system/pg_dump@17-main.service.d /etc/systemd/system/pg_dump@18-main.service.d
systemctl daemon-reload
systemctl enable --now pg_receivewal@18-main.service pg_dump@18-main.timer pg_basebackup@18-main.timer
systemctl start pg_basebackup@18-main.service

Gdy nowy klaster działa z aplikacją i ma pierwszą kopię, usuwamy stary klaster przez pg_dropcluster 17 main, a potem pakiety wersji 17 (postgresql-17, postgresql-client-17 i rozszerzenia). Kopie wersji 17 w /var/backups/postgresql/17-main trzymamy, dopóki nie przestaną być potrzebne.

Checklista administratora PostgreSQL po instalacji

1. Sprawdź wersję główną i datę końca jej wsparcia. Na Ubuntu 22.04 PostgreSQL 14 traci wsparcie 12 listopada 2026 r.
2. Załóż osobną rolę z LOGIN i bazę z OWNER dla każdej aplikacji. Hasło ustaw przez \password, odbierz PUBLIC prawa CONNECT i TEMPORARY do bazy.
3. Rolom tylko do odczytu nadaj GRANT SELECT ON ALL TABLES i ALTER DEFAULT PRIVILEGES dla tabel tworzonych później.
4. W pg_hba.conf wpuszczaj konkretne adresy przez hostssl, sprawdzaj plik w pg_hba_file_rules, a port 5432 otwieraj w zaporze tylko dla serwerów aplikacji.
5. Własne ustawienia trzymaj w conf.d. Przed każdym restartem sprawdź pg_file_settings, bo jedna literówka blokuje przeładowanie wszystkich zmian, a przy restarcie zatrzymuje klaster.
6. Ustaw shared_buffers (punkt wyjścia: 25% RAM), realne effective_cache_size, ostrożny work_mem i log wolnych zapytań, oczekiwania na blokady oraz pracy autovacuum.
7. Włącz pg_stat_statements w bazie postgres i co jakiś czas przeglądaj zapytania o największym łącznym czasie.
8. Szukaj tabel z dużym seq_tup_read i kluczy obcych bez indeksów; indeksy na działającej bazie twórz przez CREATE INDEX CONCURRENTLY i usuwaj te, które zostały jako INVALID.
9. Nie wyłączaj autovacuum. Dla dużych, często zmienianych tabel obniż autovacuum_vacuum_scale_factor przez ALTER TABLE ... SET.
10. Pilnuj długich transakcji (idle in transaction, backend_xmin) i ustaw idle_in_transaction_session_timeout rolom, które je zostawiają.
11. Przed VACUUM FULL zmierz pgstattuple, sprawdź wolne miejsce na dysku i ustaw lock_timeout; na działającej bazie użyj raczej pg_repack.
12. Włącz kopie: codzienny pg_dump@ z dłuższą retencją, pg_basebackup@ i pg_receivewal@. Monitoruj slot replikacji, kopiuj /var/backups/postgresql poza serwer i regularnie odtwarzaj kopię obok produkcji.
13. Po dodaniu repozytorium PGDG usuń metapakiet postgresql i instaluj wersje główne jawnie. Po pg_upgradecluster skopiuj conf.d, zaktualizuj rozszerzenia, przełącz jednostki kopii na nowy klaster i zrób nową kopię bazową.

Wszystkie punkty tej listy przećwiczyliśmy w labie na PostgreSQL 17.11, a różnice w wersji 18.6 opisujemy w tekście przy danym mechanizmie.