0Pricing
SQL Academy · Lekcja

Kopie logiczne a fizyczne

pg_dump i kopie bazowe

Kopie logiczne a fizyczne to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 1 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Czym jest kopia zapasowa bazy danych

Kopia zapasowa to kopia danych bazy danych, której można użyć do przywrócenia systemu po utracie lub uszkodzeniu danych albo po awarii. Bez niezawodnych kopii zapasowych pojedyncza awaria sprzętu lub przypadkowe DELETE może bezpowrotnie zniszczyć dane gromadzone przez miesiące lub lata.

PostgreSQL udostępnia dwie szerokie kategorie strategii tworzenia kopii zapasowych: kopie logiczne i kopie fizyczne. Każda z nich ma własne cechy, zastosowania i kompromisy, które każdy DBA musi rozumieć.

Kopie logiczne — wyjaśnienie

Kopia logiczna eksportuje bazę danych jako czytelne dla człowieka instrukcje SQL — CREATE TABLE, INSERT, COPY i podobne polecenia. Najczęściej używanym w PostgreSQL narzędziem do tego celu jest pg_dump.

Ponieważ wynik ma postać zwykłego kodu SQL, kopia logiczna jest przenośna: można przywrócić ją do innej wersji PostgreSQL, w innym systemie operacyjnym, a nawet selektywnie przywrócić pojedyncze tabele lub schematy. Kompromis polega na tym, że zrzucanie i przywracanie dużych baz danych może trwać długo.

Używanie pg_dump do tworzenia kopii logicznej

Narzędzie pg_dump uruchamia się w wierszu poleceń, a nie wewnątrz SQL. Łączy się ono z działającym serwerem PostgreSQL i eksportuje wybraną bazę danych. Wynik można zapisać jako zwykły kod SQL, niestandardowy format skompresowany lub format katalogowy.

Poniższy kod SQL symuluje to, co przechwytuje kopia logiczna — strukturę i dane tabeli w postaci instrukcji, które można odtworzyć.

-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)

CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    customer  TEXT        NOT NULL,
    amount    NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (customer, amount, created_at) VALUES
    ('Alice',  149.99, '2024-01-15 09:30:00+00'),
    ('Bob',     89.50, '2024-01-16 14:00:00+00'),
    ('Carol',  210.00, '2024-01-17 11:15:00+00');

Formaty wyjściowe pg_dump

pg_dump obsługuje cztery formaty wyjściowe, z których każdy nadaje się do innych sposobów przywracania:

  • plain — zwykły skrypt SQL, czytelny w dowolnym edytorze tekstu.
  • custom — skompresowany format binarny; najbardziej elastyczny, obsługuje przywracanie równoległe.
  • directory — po jednym pliku na tabelę, obsługuje równoległe tworzenie i przywracanie kopii.
  • tar — archiwum tar formatu katalogowego.

Format custom jest zalecany w przypadku dużych baz danych, ponieważ pg_restore może przywracać obiekty równolegle, korzystając z -j N procesów roboczych.

-- Checking which databases exist before choosing what to back up
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM   pg_database
WHERE  datname NOT IN ('template0', 'template1')
ORDER  BY pg_database_size(datname) DESC;

Przywracanie kopii logicznej

Logiczną kopię zapasową w zwykłym formacie SQL przywraca się za pomocą psql. Kopia w formacie custom wymaga narzędzia pg_restore. Oba narzędzia ponownie wykonują instrukcje SQL, aby odtworzyć tabele, indeksy, ograniczenia i dane.

Ponieważ kopie logiczne zawierają kod SQL, można je edytować przed przywróceniem — na przykład aby przywrócić tylko jedną tabelę lub zmienić nazwę schematu. Ta elastyczność jest jedną z największych zalet podejścia logicznego.

-- After restoring a backup, verify row counts match expectations
SELECT
    schemaname,
    relname           AS table_name,
    n_live_tup        AS estimated_rows
FROM  pg_stat_user_tables
ORDER BY n_live_tup DESC;

Kopie fizyczne — wyjaśnienie

Kopia fizyczna (nazywana również kopią bazową) kopiuje surowe pliki danych używane przez PostgreSQL na dysku — strony, segmenty dziennika z wyprzedzeniem (WAL) i pliki konfiguracyjne. Wynikiem jest binarny zrzut całego klastra z określonego momentu.

Przywracanie kopii fizycznych jest zazwyczaj znacznie szybsze w przypadku dużych baz danych, ponieważ nie wymaga ponownego wykonywania SQL; PostgreSQL po prostu umieszcza pliki z powrotem na miejscu i odtwarza WAL, aby osiągnąć spójny stan.

pg_basebackup: tworzenie kopii fizycznej

pg_basebackup to standardowe narzędzie PostgreSQL do tworzenia kopii fizycznych. Przesyła ono strumieniowo katalog danych z działającego serwera głównego przez połączenie replikacyjne. Potrzebny jest użytkownik z uprawnieniami replikacji oraz ustawienie wal_level na replica lub wyższe.

W bazie danych można odpytać ustawienia replikacji, aby potwierdzić poprawną konfigurację serwera przed rozpoczęciem tworzenia kopii bazowej.

-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM   pg_settings
WHERE  name IN (
    'wal_level',
    'max_wal_senders',
    'archive_mode',
    'archive_command'
)
ORDER  BY name;

Archiwizacja WAL i odzyskiwanie do określonego momentu

Kopia bazowa obejmuje stan z jednego momentu. Aby odzyskać dane do dowolnego punktu po wykonaniu tej kopii, PostgreSQL odtwarza zarchiwizowane segmenty WAL — proces ten nazywa się odzyskiwaniem do określonego momentu (Point-in-Time Recovery, PITR).

Gdy archive_mode = on i skonfigurowano archive_command, PostgreSQL kopiuje ukończone segmenty WAL do lokalizacji archiwum. Podczas odzyskiwania restore_command pobiera te segmenty, aby serwer mógł odtworzyć je aż do żądanego czasu docelowego.

-- Inspect current WAL position and archive status
SELECT
    pg_current_wal_lsn()                        AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn())        AS current_wal_file,
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time
FROM  pg_stat_archiver;

Porównanie kopii logicznych i fizycznych

Wybór między kopiami logicznymi i fizycznymi zależy od wymagań:

  • Logiczne (pg_dump): przenośne między wersjami, obsługują częściowe przywracanie i są czytelne dla człowieka, ale w przypadku dużych baz danych działają wolno i nie umożliwiają przywracania z dokładnością do podtransakcji.
  • Fizyczne (pg_basebackup + WAL): zapewniają szybkie przywracanie dużych klastrów, obsługują PITR, są zależne od wersji (przywracanie musi odbywać się do tej samej wersji głównej) i przywracają cały klaster — nie można przywrócić pojedynczej tabeli.

Środowiska produkcyjne zwykle wykorzystują oba podejścia: nocne bazowe kopie fizyczne z ciągłą archiwizacją WAL oraz okresowe zrzuty logiczne zapewniające przenośność i selektywne przywracanie.

Weryfikowanie integralności kopii zapasowych

Kopia zapasowa, której nigdy nie przetestowano, nie jest kopią — to tylko nadzieja. Zawsze należy sprawdzać kopie, przywracając je w środowisku testowym i weryfikując dane.

W przypadku kopii logicznych szybką kontrolą integralności jest policzenie wierszy i porównanie sum kontrolnych. W przypadku kopii fizycznych PostgreSQL 14 i nowszy udostępnia narzędzie pg_verifybackup, które sprawdza plik manifestu zapisany przez pg_basebackup.

-- After a test restore, compare row counts across critical tables
SELECT
    relname                              AS table_name,
    n_live_tup                           AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM  pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC
LIMIT  20;

Monitorowanie i planowanie kopii zapasowych

Automatyzacja i monitorowanie kopii zapasowych są równie ważne jak ich tworzenie. Należy śledzić czas ostatniego uruchomienia kopii, czas jej trwania oraz to, czy zakończyła się powodzeniem. PostgreSQL udostępnia przydatne metadane na potrzeby takiego monitorowania.

W przypadku kopii fizycznych pg_stat_archiver pokazuje ostatnią pomyślną archiwizację i wszelkie błędy. W przypadku kopii logicznych należy opakować pg_dump w skrypt, który zapisuje czas rozpoczęcia, czas zakończenia, rozmiar pliku i kod wyjścia w tabeli monitorującej lub systemie alertów.

-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
    id          SERIAL PRIMARY KEY,
    backup_type TEXT        NOT NULL CHECK (backup_type IN ('logical', 'physical')),
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at TIMESTAMPTZ,
    size_bytes  BIGINT,
    status      TEXT        NOT NULL DEFAULT 'running',
    notes       TEXT
);

-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;

Kopie logiczne i fizyczne: szybkie sprawdzenie

Sprawdź swoją wiedzę na temat logicznych i fizycznych strategii tworzenia kopii zapasowych w PostgreSQL.

Podsumowanie lekcji: kopie logiczne i fizyczne

W tej lekcji poznali Państwo dwie podstawowe strategie tworzenia kopii zapasowych w PostgreSQL:

  • Kopie logiczne wykorzystują pg_dump do eksportowania baz danych jako instrukcji SQL. Są przenośne, czytelne dla człowieka i obsługują częściowe przywracanie, ale mogą działać wolno w przypadku bardzo dużych baz danych.
  • Kopie fizyczne wykorzystują pg_basebackup do kopiowania surowych plików danych. W połączeniu z archiwizacją WAL umożliwiają szybkie przywracanie i odzyskiwanie do określonego momentu, ale są zależne od wersji i zawsze przywracają cały klaster.
  • Systemy produkcyjne zwykle łączą obie strategie: bazowe kopie fizyczne z archiwizacją WAL na potrzeby szybkiego i szczegółowego odzyskiwania oraz okresowe zrzuty logiczne zapewniające przenośność.
  • Zawsze należy testować przywracanie kopii. Nieprzetestowanej kopii nie można ufać w rzeczywistym scenariuszu awarii.

Zrozumienie tych dwóch podejść jest niezbędne do zaprojektowania solidnego planu odzyskiwania po awarii dla każdego wdrożenia PostgreSQL.

Często zadawane pytania

Czy lekcja „Kopie logiczne a fizyczne” jest bezpłatna?

Tak — pełny tekst „Kopie logiczne a fizyczne” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Kopie logiczne a fizyczne”?

pg_dump i kopie bazowe Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 1 z 4.

Ile czasu zajmuje lekcja „Kopie logiczne a fizyczne”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?

Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Kopie logiczne a fizyczne
  2. Odzyskiwanie do określonego momentu
  3. Testowanie przywracania kopii
  4. Planowanie odtwarzania po awarii
← Powrót do SQL Academy