PostgreSQL w firmie: projektowanie bazy, wydajność i sprawdzony backup

Rozdziel klientów, zamówienia i pozycje zamówień. Ustal klucze, relacje oraz pola wymagane. Formularz może sprawdzać dane dla wygody użytkownika, ale import i integracja API również zapisują rekordy. Ograniczenia bazy powinny chronić reguły wspólne dla wszystkich kanałów.

Klucz obcy zapobiega wskazaniu nieistniejącego rekordu, UNIQUE kontroluje unikalność, a CHECK sprawdza warunek dotyczący wiersza. CHECK sam nie wyklucza wartości NULL, dlatego dla wymaganej ilości potrzebne są oba ograniczenia. Przykład poniżej jest niewielkim modelem pozycji zamówienia do przećwiczenia na bazie testowej.

CREATE TABLE order_items (
  order_id bigint NOT NULL,
  line_no integer NOT NULL CHECK (line_no > 0),
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, line_no)
);

Wypisz najczęstsze operacje: ostatnie zamówienia klienta, otwarte zgłoszenia, wyszukiwanie produktu. Przygotuj dane o reprezentatywnej wielkości i rozkładzie. Szybki wynik na tabeli z dwudziestoma rekordami niewiele mówi o zachowaniu systemu po roku pracy.

EXPLAIN pokazuje plan wykonania, a EXPLAIN ANALYZE dodatkowo uruchamia zapytanie i podaje pomiary. Z tego powodu wariantu ANALYZE nie należy bezrefleksyjnie używać do operacji zmieniających dane. Oceniaj liczbę odczytanych wierszy, sortowanie i różnice między estymacją a wykonaniem. Dodany indeks też zajmuje miejsce i musi być utrzymywany przy zapisie.

pg_dump tworzy logiczną kopię jednej bazy, spójną dla momentu rozpoczęcia eksportu. Format custom obsługuje pg_restore. Sam plik dump nie zastępuje informacji o rolach, wymaganych rozszerzeniach, konfiguracji aplikacji i plikach przechowywanych poza bazą. Te elementy uwzględnij w planie odtworzenia.

Poniższy zestaw poleceń zakłada skonfigurowane połączenia libpq nazwane firma_prod oraz firma_test. Najpierw sprawdź ich hosty: odtwarzanie ma trafić wyłącznie na oddzielny serwer testowy. Wymagane role i rozszerzenia muszą być przygotowane, a docelowa baza firma_restore_test nie może już istnieć.

pg_dump --dbname='service=firma_prod' --format=custom --file=firma.dump
createdb --maintenance-db='service=firma_test dbname=postgres' --template=template0 firma_restore_test
pg_restore --dbname='service=firma_test dbname=firma_restore_test' --exit-on-error firma.dump

Ustal dwie wartości: ile danych firma może utracić i jak długo może nie działać. Kopia wykonywana raz dziennie może nie odpowiadać wymaganiom systemu przyjmującego zamówienia co minutę. Jeżeli potrzebne jest odtworzenie do wybranego momentu, zaplanuj kopię bazową i ciągłą archiwizację WAL.

PITR wymaga kompletnego ciągu odpowiednich plików WAL. Nie powstaje przez połączenie zwykłego pg_dump z późniejszymi logami. Kontroluj skuteczność archiwizacji, wolne miejsce i retencję, aby usuwanie starych plików nie przerwało możliwości przywrócenia deklarowanego okresu.

Odtwórz kopię na izolowanym środowisku, uruchom aplikację i wykonaj podstawowy proces biznesowy. Porównaj liczbę kluczowych rekordów, relacje i wybrane sumy kontrolne danych. Zmierz rzeczywisty czas od rozpoczęcia przywracania do gotowości systemu.

Przechowuj kopię także poza serwerem bazy, z kontrolą dostępu i ochroną odpowiednią do zawartości. Alarmuj o braku nowej kopii, a nie tylko o zgłoszonym błędzie programu. Plan powinien wskazywać osobę odpowiedzialną oraz regularny termin kolejnej próby odtworzenia.