SQL Academy · Pelajaran

Sandaran Logikal berbanding Fizikal

pg_dump dan sandaran asas.

Pelajaran 1 daripada 413 langkah

Sandaran Logikal berbanding Fizikal ialah pelajaran SQL Academy percuma di CoddyKit. Ini ialah pelajaran 1 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran SQL Academy, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus SQL Academy merangkumi sejumlah 4 pelajaran.

Apakah Sandaran Pangkalan Data

Sandaran ialah salinan data pangkalan data yang boleh digunakan untuk memulihkan sistem selepas kehilangan data, kerosakan atau bencana. Tanpa sandaran yang boleh dipercayai, satu kegagalan perkakasan atau DELETE yang dilakukan secara tidak sengaja boleh memusnahkan data berbulan-bulan atau bertahun-tahun secara kekal.

PostgreSQL menyediakan dua kategori umum strategi sandaran: sandaran logik dan sandaran fizikal. Setiap satunya mempunyai ciri, kes penggunaan dan kompromi tersendiri yang perlu difahami oleh setiap DBA.

Penjelasan tentang Sandaran Logik

Sandaran logik mengeksport pangkalan data sebagai pernyataan SQL yang boleh dibaca manusia — CREATE TABLE, INSERT, COPY dan perintah yang serupa. Alat yang paling lazim digunakan untuk tujuan ini dalam PostgreSQL ialah pg_dump.

Oleh sebab hasilnya ialah SQL biasa, sandaran logik mudah dipindahkan: anda boleh memulihkannya kepada versi PostgreSQL yang berbeza, sistem pengendalian yang berbeza, atau memulihkan jadual atau skema tertentu secara terpilih. Komprominya ialah proses mengeksport dan memulihkan pangkalan data besar boleh menjadi perlahan.

Menggunakan pg_dump untuk Sandaran Logik

Utiliti pg_dump dijalankan daripada baris perintah, bukan di dalam SQL. Utiliti ini bersambung kepada pelayan PostgreSQL yang sedang berjalan dan mengeksport pangkalan data yang dipilih. Anda boleh menghasilkan SQL biasa, format termampat tersuai atau format direktori.

SQL di bawah mensimulasikan perkara yang ditangkap oleh sandaran logik — struktur dan data jadual dalam bentuk pernyataan yang boleh dihasilkan semula.

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

Format Hasil pg_dump

pg_dump menyokong empat format hasil, setiap satunya sesuai untuk aliran kerja pemulihan yang berbeza:

biasa — skrip SQL biasa yang boleh dibaca dalam mana-mana penyunting teks.
tersuai — format perduaan termampat yang paling fleksibel dan menyokong pemulihan selari.
direktori — satu fail bagi setiap jadual serta menyokong pengeksportan dan pemulihan selari.
tar — arkib tar bagi format direktori.

Format tersuai disyorkan untuk pangkalan data besar kerana pg_restore boleh memulihkan objek secara selari menggunakan -j N pekerja.

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

Memulihkan Sandaran Logik

Sandaran logik SQL biasa dipulihkan dengan psql. Sandaran berformat tersuai memerlukan pg_restore. Kedua-dua alat memainkan semula pernyataan SQL untuk mencipta semula jadual, indeks, kekangan dan data.

Oleh sebab sandaran logik mengandungi SQL, anda boleh menyuntingnya sebelum memulihkan — contohnya, untuk memulihkan satu jadual sahaja atau menukar nama skema. Fleksibiliti ini ialah salah satu kelebihan terbesar pendekatan logik.

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

Penjelasan tentang Sandaran Fizikal

Sandaran fizikal (juga dipanggil sandaran asas) menyalin fail data mentah yang digunakan oleh PostgreSQL pada cakera — halaman, segmen WAL dan fail konfigurasi. Hasilnya ialah gambaran perduaan bagi seluruh kluster pada satu titik masa.

Sandaran fizikal biasanya jauh lebih pantas untuk dipulihkan bagi pangkalan data besar kerana tiada pelaksanaan semula SQL; PostgreSQL hanya membaca semula fail ke tempatnya dan memainkan semula WAL sehingga keadaan konsisten dicapai.

pg_basebackup: Mengambil Sandaran Fizikal

pg_basebackup ialah alat PostgreSQL standard untuk sandaran fizikal. Alat ini menyalurkan direktori data daripada pelayan utama yang sedang berjalan melalui sambungan replikasi. Anda memerlukan pengguna dengan keistimewaan replikasi dan wal_level yang ditetapkan kepada replica atau lebih tinggi.

Di dalam pangkalan data, anda boleh membuat pertanyaan terhadap tetapan replikasi untuk mengesahkan bahawa pelayan dikonfigurasikan dengan betul sebelum mencuba sandaran asas.

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

Pengarkiban WAL dan Pemulihan pada Titik Masa

Sandaran asas merakam satu detik masa. Untuk memulihkan kepada mana-mana titik masa selepas sandaran itu, PostgreSQL memainkan semula segmen WAL yang diarkibkan — proses ini dipanggil Pemulihan pada Titik Masa (PITR).

Apabila archive_mode = on dan archive_command dikonfigurasikan, PostgreSQL menyalin segmen WAL yang telah lengkap ke lokasi arkib. Semasa pemulihan, restore_command mendapatkan semula segmen tersebut supaya pelayan boleh memainkannya semula sehingga masa sasaran yang dikehendaki.

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

Membandingkan Sandaran Logik dengan Fizikal

Pemilihan antara sandaran logik dan fizikal bergantung pada keperluan anda:

Logik (pg_dump): serasi merentas versi, menyokong pemulihan separa dan boleh dibaca manusia, tetapi perlahan untuk pangkalan data besar serta tiada keperincian pada peringkat subtransaksi.
Fizikal (pg_basebackup + WAL): pemulihan pantas untuk kluster besar, menyokong PITR, khusus kepada versi tertentu (mesti dipulihkan kepada versi utama yang sama) dan memulihkan seluruh kluster — anda tidak boleh memulihkan satu jadual sahaja.

Persekitaran pengeluaran lazimnya menggunakan kedua-duanya: sandaran asas fizikal setiap malam dengan pengarkiban WAL berterusan, serta eksport logik berkala untuk kemudahalihan dan pemulihan terpilih.

Mengesahkan Integriti Sandaran

Sandaran yang tidak pernah diuji bukanlah sandaran — ia hanyalah harapan. Sentiasa sahkan sandaran dengan memulihkannya ke persekitaran ujian dan mengesahkan data.

Bagi sandaran logik, pemeriksaan integriti pantas ialah mengira baris dan membandingkan jumlah semak. Bagi sandaran fizikal, PostgreSQL 14+ memperkenalkan pg_verifybackup, yang memeriksa fail manifes yang ditulis oleh 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;

Pemantauan dan Penjadualan Sandaran

Mengautomatikkan dan memantau sandaran sama pentingnya dengan mengambil sandaran itu sendiri. Jejaki bila sandaran terakhir dijalankan, tempoh yang diambil dan sama ada sandaran itu berjaya. PostgreSQL mendedahkan metadata yang berguna untuk tujuan ini.

Bagi sandaran fizikal, pg_stat_archiver menunjukkan pengarkiban terakhir yang berjaya serta sebarang kegagalan. Bagi sandaran logik, bungkus pg_dump dalam skrip yang merekodkan masa mula, masa tamat, saiz fail dan kod keluar ke jadual pemantauan atau sistem amaran.

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

Semakan Pantas: Logik berbanding Fizikal

Uji pemahaman anda tentang strategi sandaran logik dan fizikal dalam PostgreSQL.

Jawab dalam pelajaran interaktif

Rumusan Pelajaran: Sandaran Logik berbanding Fizikal

Dalam pelajaran ini, anda telah meneroka dua strategi sandaran asas PostgreSQL:

Sandaran logik menggunakan pg_dump untuk mengeksport pangkalan data sebagai pernyataan SQL. Sandaran ini mudah dipindahkan, boleh dibaca manusia dan menyokong pemulihan separa, tetapi boleh menjadi perlahan untuk pangkalan data yang sangat besar.
Sandaran fizikal menggunakan pg_basebackup untuk menyalin fail data mentah. Apabila digabungkan dengan pengarkiban WAL, sandaran ini membolehkan pemulihan pantas dan Pemulihan pada Titik Masa (PITR), walaupun khusus kepada versi tertentu dan sentiasa memulihkan seluruh kluster.
Sistem pengeluaran lazimnya menggabungkan kedua-dua strategi: sandaran asas fizikal dengan pengarkiban WAL untuk pemulihan yang pantas dan berbutir halus, serta eksport logik berkala untuk kemudahalihan.
Sentiasa uji pemulihan anda. Sandaran yang tidak diuji tidak boleh dipercayai dalam situasi bencana sebenar.

Memahami kedua-dua pendekatan ini penting untuk mereka bentuk pelan pemulihan bencana yang kukuh bagi mana-mana penggunaan PostgreSQL.

Percuma untuk bermula

Pelajari SQL dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 46
Pelajaran: 183

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Sandaran Logikal berbanding Fizikal” percuma?

Ya — teks penuh “Sandaran Logikal berbanding Fizikal” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus SQL Academy, tingkat taraf kepada CoddyKit PRO. Kursus SQL Academy merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Sandaran Logikal berbanding Fizikal”?

pg_dump dan sandaran asas. Anda berlatih SQL Academy menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan SQL Academy?

Tiada pengalaman terdahulu diperlukan. Pembelajaran SQL Academy di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 1 daripada 4.

Berapa lamakah pelajaran “Sandaran Logikal berbanding Fizikal” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran SQL Academy ini?

Ya. Setiap pelajaran SQL Academy menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

← Kembali ke SQL Academy