SQL Academy · Lektion

Logiske kontra fysiske backups

pg_dump og base-backups.

Lektion 1 af 413 trin

Logiske kontra fysiske backups er en gratis SQL Academy-lektion på CoddyKit. Dette er lektion 1 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i SQL Academy, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. SQL Academy-kurset indeholder 4 lektioner i alt.

Hvad er en sikkerhedskopi af en database

En sikkerhedskopi er en kopi af dine databasedata, som kan bruges til at gendanne systemet efter datatab, beskadigelse eller en katastrofe. Uden pålidelige sikkerhedskopier kan en enkelt hardwarefejl eller en utilsigtet DELETE permanent ødelægge data fra måneder eller år.

PostgreSQL tilbyder to overordnede kategorier af strategier for sikkerhedskopiering: logiske sikkerhedskopier og fysiske sikkerhedskopier. De har hver deres egenskaber, anvendelsesområder og afvejninger, som enhver databaseadministrator bør forstå.

Logiske sikkerhedskopier forklaret

En logisk sikkerhedskopi eksporterer databasen som SQL-sætninger, der kan læses af mennesker — CREATE TABLE, INSERT, COPY og lignende kommandoer. Det mest almindelige værktøj til dette i PostgreSQL er pg_dump.

Fordi outputtet er almindelig SQL, er en logisk sikkerhedskopi portabel: Du kan gendanne den til en anden PostgreSQL-version, et andet operativsystem eller endda selektivt gendanne individuelle tabeller eller skemaer. Ulempen er, at eksport og gendannelse af store databaser kan være langsom.

Brug af pg_dump til en logisk sikkerhedskopi

Værktøjet pg_dump køres fra kommandolinjen, ikke inde i SQL. Det opretter forbindelse til en kørende PostgreSQL-server og eksporterer den valgte database. Du kan gemme outputtet som almindelig SQL, i et brugerdefineret komprimeret format eller i et mappeformat.

SQL'en nedenfor simulerer, hvad en logisk sikkerhedskopi indeholder — strukturen og dataene i en tabel som sætninger, der kan genskabes.

-- 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');

Outputformater for pg_dump

pg_dump understøtter fire outputformater, som hver især egner sig til forskellige gendannelsesforløb:

plain — et almindeligt SQL-script, der kan læses i enhver teksteditor.
custom — et komprimeret binært format, der er mest fleksibelt og understøtter parallel gendannelse.
directory — én fil pr. tabel, der understøtter parallel eksport og gendannelse.
tar — et tar-arkiv af directory-formatet.

Custom-formatet anbefales til store databaser, fordi pg_restore kan gendanne objekter parallelt ved hjælp af -j N-arbejdere.

-- 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;

Gendannelse af en logisk sikkerhedskopi

En logisk sikkerhedskopi i almindelig SQL gendannes med psql. En sikkerhedskopi i custom-format kræver pg_restore. Begge værktøjer afspiller SQL-sætningerne for at genskabe tabeller, indeks, begrænsninger og data.

Fordi logiske sikkerhedskopier indeholder SQL, kan du redigere dem før gendannelsen — for eksempel for kun at gendanne én tabel eller ændre et skemanavn. Denne fleksibilitet er en af de største fordele ved den logiske tilgang.

-- 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;

Fysiske sikkerhedskopier forklaret

En fysisk sikkerhedskopi (også kaldet en basissikkerhedskopi) kopierer de rå datafiler, som PostgreSQL bruger på disken — siderne, WAL-segmenterne og konfigurationsfilerne. Resultatet er et binært øjebliksbillede af hele klyngen på et bestemt tidspunkt.

Fysiske sikkerhedskopier er typisk meget hurtigere at gendanne for store databaser, fordi SQL ikke skal udføres igen. PostgreSQL læser blot filerne tilbage på plads og afspiller WAL for at nå frem til en konsistent tilstand.

pg_basebackup: Oprettelse af en fysisk sikkerhedskopi

pg_basebackup er PostgreSQL's standardværktøj til fysiske sikkerhedskopier. Det streamer datamappen fra en kørende primærserver via en replikeringsforbindelse. Du skal bruge en bruger med replikeringsrettigheder, og wal_level skal være sat til replica eller højere.

Du kan forespørge i replikeringsindstillingerne inde i databasen for at bekræfte, at serveren er konfigureret korrekt, før du forsøger at oprette en basissikkerhedskopi.

-- 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;

WAL-arkivering og gendannelse til et bestemt tidspunkt

En basissikkerhedskopi registrerer et øjeblik i tiden. Hvis du vil gendanne til et vilkårligt tidspunkt efter denne sikkerhedskopi, afspiller PostgreSQL arkiverede WAL-segmenter — dette kaldes gendannelse til et bestemt tidspunkt (PITR).

Når archive_mode = on og archive_command er konfigureret, kopierer PostgreSQL færdige WAL-segmenter til en arkivplacering. Under gendannelsen henter restore_command segmenterne tilbage, så serveren kan afspille dem frem til det ønskede måltidspunkt.

-- 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;

Sammenligning af logiske og fysiske sikkerhedskopier

Valget mellem logiske og fysiske sikkerhedskopier afhænger af dine krav:

Logisk (pg_dump): portabel på tværs af versioner, understøtter delvis gendannelse og kan læses af mennesker, men er langsom for store databaser og har ingen granularitet på undertransaktionsniveau.
Fysisk (pg_basebackup + WAL): hurtig gendannelse for store klynger, understøtter PITR, er versionsafhængig (skal gendannes til den samme hovedversion) og gendanner hele klyngen — du kan ikke gendanne en enkelt tabel.

Produktionsmiljøer bruger typisk begge: natlige fysiske basissikkerhedskopier med kontinuerlig WAL-arkivering samt periodiske logiske eksporter for portabilitet og målrettede gendannelser.

Kontrol af sikkerhedskopiens integritet

En sikkerhedskopi, der aldrig er blevet testet, er ikke en sikkerhedskopi — det er kun et håb. Validér altid sikkerhedskopier ved at gendanne dem i et testmiljø og kontrollere dataene.

For logiske sikkerhedskopier er en hurtig integritetskontrol at tælle rækker og sammenligne kontrolsummer. For fysiske sikkerhedskopier introducerede PostgreSQL 14+ pg_verifybackup, som kontrollerer den manifestfil, der blev skrevet af 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;

Overvågning og planlægning af sikkerhedskopier

Automatisering og overvågning af sikkerhedskopier er lige så vigtigt som at oprette dem. Hold øje med, hvornår sikkerhedskopier senest blev kørt, hvor lang tid de tog, og om de lykkedes. PostgreSQL stiller nyttige metadata til rådighed til dette formål.

For fysiske sikkerhedskopier viser pg_stat_archiver den seneste vellykkede arkivering og eventuelle fejl. For logiske sikkerhedskopier kan du pakke pg_dump ind i et script, der registrerer starttidspunkt, sluttidspunkt, filstørrelse og afslutningskode i en overvågningstabel eller et alarmsystem.

-- 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;

Logiske og fysiske sikkerhedskopier: Videnstjek

Test din forståelse af strategier for logiske og fysiske sikkerhedskopier i PostgreSQL.

Besvar den i den interaktive lektion

Lektionens opsummering: Logiske og fysiske sikkerhedskopier

I denne lektion har du undersøgt to grundlæggende strategier for sikkerhedskopiering i PostgreSQL:

Logiske sikkerhedskopier bruger pg_dump til at eksportere databaser som SQL-sætninger. De er portable, kan læses af mennesker og understøtter delvise gendannelser, men kan være langsomme for meget store databaser.
Fysiske sikkerhedskopier bruger pg_basebackup til at kopiere rå datafiler. Kombineret med WAL-arkivering muliggør de hurtige gendannelser og gendannelse til et bestemt tidspunkt, men de er versionsafhængige og gendanner altid hele klyngen.
Produktionssystemer kombinerer typisk begge strategier: fysiske basissikkerhedskopier med WAL-arkivering til hurtig gendannelse med fin granularitet samt periodiske logiske eksporter for portabilitet.
Test altid dine gendannelser. En sikkerhedskopi, der ikke er blevet testet, kan ikke stoles på i en reel katastrofesituation.

Det er afgørende at forstå disse to tilgange, når du designer en robust plan for katastrofegendannelse for enhver PostgreSQL-installation.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 46
Lektioner: 183

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Logiske kontra fysiske backups” gratis?

Ja — hele teksten til “Logiske kontra fysiske backups” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af SQL Academy-kurset, skal du opgradere til CoddyKit PRO. SQL Academy-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Logiske kontra fysiske backups”?

pg_dump og base-backups. Du øver dig i SQL Academy med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på SQL Academy?

Der kræves ingen tidligere erfaring. SQL Academy på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 1 af 4.

Hvor lang tid tager lektionen “Logiske kontra fysiske backups”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne SQL Academy-lektion?

Ja. Alle SQL Academy-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

← Tilbage til SQL Academy