SQL Academy · บทเรียน

การสำรองข้อมูลเชิงตรรกะเทียบกับเชิงกายภาพ

pg_dump และการสำรองข้อมูลพื้นฐาน

การสำรองข้อมูลเชิงตรรกะเทียบกับเชิงกายภาพ เป็นบทเรียน SQL Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 1 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

การสำรองฐานข้อมูลคืออะไร

การสำรองข้อมูลคือสำเนาของข้อมูลในฐานข้อมูลที่สามารถใช้กู้คืนระบบหลังเกิดการสูญหาย ความเสียหาย หรือภัยพิบัติ หากไม่มีข้อมูลสำรองที่เชื่อถือได้ ความขัดข้องของฮาร์ดแวร์เพียงครั้งเดียวหรือการใช้ DELETE โดยไม่ตั้งใจอาจทำลายข้อมูลที่สะสมมาหลายเดือนหรือหลายปีได้อย่างถาวร

PostgreSQL มีกลยุทธ์การสำรองข้อมูลหลักอยู่สองประเภท ได้แก่ การสำรองข้อมูลเชิงตรรกะ และ การสำรองข้อมูลเชิงกายภาพ แต่ละประเภทมีลักษณะ การใช้งาน และข้อแลกเปลี่ยนที่แตกต่างกัน ซึ่ง DBA ทุกคนควรทำความเข้าใจ

อธิบายการสำรองข้อมูลเชิงตรรกะ

การสำรองข้อมูลเชิงตรรกะจะส่งออกฐานข้อมูลเป็นคำสั่งภาษาเอสคิวแอลที่มนุษย์อ่านได้ — CREATE TABLE, INSERT, COPY และคำสั่งลักษณะเดียวกัน เครื่องมือที่ใช้กันมากที่สุดสำหรับงานนี้ใน PostgreSQL คือ pg_dump

เนื่องจากผลลัพธ์เป็นคำสั่งภาษาเอสคิวแอลธรรมดา การสำรองข้อมูลเชิงตรรกะจึงย้ายข้ามระบบได้ คุณสามารถกู้คืนไปยัง PostgreSQL รุ่นอื่น ระบบปฏิบัติการอื่น หรือเลือกกู้คืนเฉพาะตารางหรือแบบแผนบางรายการได้ ข้อแลกเปลี่ยนคือการส่งออกและกู้คืนฐานข้อมูลขนาดใหญ่อาจใช้เวลานาน

การใช้เครื่องมือสำรองข้อมูลเชิงตรรกะ

ยูทิลิตี pg_dump ทำงานจากบรรทัดคำสั่ง ไม่ได้ทำงานภายในภาษาเอสคิวแอล ยูทิลิตีนี้เชื่อมต่อกับเซิร์ฟเวอร์ PostgreSQL ที่กำลังทำงานอยู่และส่งออกฐานข้อมูลที่เลือก คุณสามารถส่งออกเป็นภาษาเอสคิวแอลธรรมดา รูปแบบไบนารีที่บีบอัดแบบกำหนดเอง หรือรูปแบบไดเรกทอรีได้

ภาษาเอสคิวแอลด้านล่างจำลองสิ่งที่การสำรองข้อมูลเชิงตรรกะเก็บไว้ นั่นคือโครงสร้างและข้อมูลของตารางในรูปคำสั่งที่สร้างซ้ำได้

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

รูปแบบผลลัพธ์ของเครื่องมือสำรองข้อมูล

เครื่องมือนี้รองรับรูปแบบผลลัพธ์สี่แบบ โดยแต่ละแบบเหมาะกับกระบวนการกู้คืนที่แตกต่างกัน:

ข้อความธรรมดา — สคริปต์ภาษาเอสคิวแอลธรรมดาที่อ่านได้ในโปรแกรมแก้ไขข้อความทุกชนิด
กำหนดเอง — รูปแบบไบนารีที่บีบอัด มีความยืดหยุ่นมากที่สุด และรองรับการกู้คืนแบบขนาน
ไดเรกทอรี — หนึ่งไฟล์ต่อตาราง รองรับการส่งออกและกู้คืนแบบขนาน
ทาร์ — แฟ้มจัดเก็บแบบทาร์ของรูปแบบไดเรกทอรี

แนะนำให้ใช้รูปแบบกำหนดเองกับฐานข้อมูลขนาดใหญ่ เพราะ pg_restore สามารถกู้คืนวัตถุแบบขนานโดยใช้หน่วยทำงาน -j N ได้

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

การกู้คืนข้อมูลสำรองเชิงตรรกะ

การสำรองข้อมูลเชิงตรรกะแบบภาษาเอสคิวแอลธรรมดาจะกู้คืนด้วย psql ส่วนข้อมูลสำรองรูปแบบกำหนดเองต้องใช้ pg_restore เครื่องมือทั้งสองจะเรียกใช้คำสั่งภาษาเอสคิวแอลซ้ำเพื่อสร้างตาราง ดัชนี ข้อจำกัด และข้อมูลขึ้นใหม่

เนื่องจากข้อมูลสำรองเชิงตรรกะมีคำสั่งภาษาเอสคิวแอล คุณจึงแก้ไขข้อมูลสำรองได้ก่อนกู้คืน เช่น กู้คืนเพียงตารางเดียวหรือเปลี่ยนชื่อแบบแผน ความยืดหยุ่นนี้เป็นข้อดีที่สำคัญที่สุดประการหนึ่งของแนวทางเชิงตรรกะ

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

อธิบายการสำรองข้อมูลเชิงกายภาพ

การสำรองข้อมูลเชิงกายภาพ หรือที่เรียกว่าการสำรองข้อมูลพื้นฐาน จะคัดลอกไฟล์ข้อมูลดิบที่ PostgreSQL ใช้บนดิสก์ ได้แก่ หน้า เซกเมนต์ WAL (บันทึกการเขียนล่วงหน้า) และไฟล์การกำหนดค่า ผลลัพธ์คือภาพสถานะไบนารีของทั้งคลัสเตอร์ ณ ช่วงเวลาหนึ่ง

โดยทั่วไป การสำรองข้อมูลเชิงกายภาพจะกู้คืนฐานข้อมูลขนาดใหญ่ได้เร็วกว่ามาก เพราะไม่ต้องเรียกใช้ภาษาเอสคิวแอลซ้ำ PostgreSQL เพียงอ่านไฟล์กลับไปยังตำแหน่งเดิมและเรียกใช้ WAL ซ้ำเพื่อให้ได้สถานะที่สอดคล้องกัน

การสำรองข้อมูลเชิงกายภาพด้วยเครื่องมือมาตรฐาน

pg_basebackup เป็นเครื่องมือมาตรฐานของ PostgreSQL สำหรับการสำรองข้อมูลเชิงกายภาพ เครื่องมือนี้ส่งสตรีมไดเรกทอรีข้อมูลจากเซิร์ฟเวอร์หลักที่กำลังทำงานอยู่ผ่านการเชื่อมต่อสำหรับจำลองข้อมูล คุณต้องมีผู้ใช้ที่มีสิทธิ์สำหรับการจำลองข้อมูล และตั้งค่า wal_level เป็น replica หรือสูงกว่า

ภายในฐานข้อมูล คุณสามารถสืบค้นการตั้งค่าการจำลองข้อมูลเพื่อตรวจสอบว่าเซิร์ฟเวอร์ได้รับการกำหนดค่าอย่างถูกต้องก่อนเริ่มการสำรองข้อมูลพื้นฐาน

-- 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 และการกู้คืน ณ เวลาใดเวลาหนึ่ง

การสำรองข้อมูลพื้นฐานจะบันทึกช่วงเวลาหนึ่ง หากต้องการกู้คืนไปยังจุดเวลาใด ๆ หลังจากการสำรองข้อมูลนั้น PostgreSQL จะเรียกใช้เซกเมนต์ WAL ที่จัดเก็บไว้อีกครั้ง ซึ่งเรียกว่า การกู้คืน ณ เวลาใดเวลาหนึ่ง (PITR)

เมื่อกำหนดค่า archive_mode = on และ archive_command แล้ว PostgreSQL จะคัดลอกเซกเมนต์ WAL ที่เสร็จสมบูรณ์ไปยังตำแหน่งจัดเก็บ ในระหว่างการกู้คืน restore_command จะดึงเซกเมนต์เหล่านั้นกลับมา เพื่อให้เซิร์ฟเวอร์เรียกใช้ซ้ำจนถึงเวลาปลายทางที่ต้องการ

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

เปรียบเทียบการสำรองข้อมูลเชิงตรรกะกับเชิงกายภาพ

การเลือกใช้การสำรองข้อมูลเชิงตรรกะหรือเชิงกายภาพขึ้นอยู่กับข้อกำหนดของคุณ:

เชิงตรรกะ: ย้ายข้ามรุ่นได้ รองรับการกู้คืนบางส่วน มนุษย์อ่านได้ แต่ทำงานช้ากับฐานข้อมูลขนาดใหญ่และไม่มีความละเอียดระดับธุรกรรมย่อย
เชิงกายภาพ: กู้คืนคลัสเตอร์ขนาดใหญ่ได้รวดเร็ว รองรับ PITR ใช้ได้กับรุ่นเฉพาะ (ต้องกู้คืนไปยังรุ่นหลักเดียวกัน) และกู้คืนทั้งคลัสเตอร์ คุณไม่สามารถกู้คืนเพียงตารางเดียวได้

โดยทั่วไป สภาพแวดล้อมจริงจะใช้ทั้งสองแบบ ได้แก่ การสำรองข้อมูลพื้นฐานเชิงกายภาพทุกคืนพร้อมการจัดเก็บ WAL อย่างต่อเนื่อง รวมถึงการส่งออกข้อมูลเชิงตรรกะเป็นระยะเพื่อให้ย้ายข้ามระบบได้และกู้คืนเฉพาะส่วนอย่างเจาะจง

การตรวจสอบความสมบูรณ์ของข้อมูลสำรอง

ข้อมูลสำรองที่ไม่เคยผ่านการทดสอบไม่ใช่ข้อมูลสำรอง แต่เป็นเพียงความหวัง ควรตรวจสอบข้อมูลสำรองเสมอด้วยการกู้คืนไปยังสภาพแวดล้อมทดสอบและตรวจสอบข้อมูล

สำหรับข้อมูลสำรองเชิงตรรกะ วิธีตรวจสอบความสมบูรณ์อย่างรวดเร็วคือการนับแถวและเปรียบเทียบผลรวมตรวจสอบ สำหรับข้อมูลสำรองเชิงกายภาพ PostgreSQL รุ่น 14 ขึ้นไปมี pg_verifybackup ซึ่งตรวจสอบไฟล์รายการที่ 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;

การเฝ้าติดตามและการจัดตารางการสำรองข้อมูล

การทำให้การสำรองข้อมูลทำงานอัตโนมัติและการเฝ้าติดตามมีความสำคัญพอ ๆ กับการสำรองข้อมูลเอง ควรติดตามว่าการสำรองข้อมูลทำงานครั้งล่าสุดเมื่อใด ใช้เวลานานเท่าใด และสำเร็จหรือไม่ PostgreSQL แสดงข้อมูลเมตาที่มีประโยชน์สำหรับงานนี้

สำหรับข้อมูลสำรองเชิงกายภาพ pg_stat_archiver แสดงการจัดเก็บที่สำเร็จล่าสุดและความล้มเหลวต่าง ๆ สำหรับข้อมูลสำรองเชิงตรรกะ ให้ครอบการทำงานของ pg_dump ด้วยสคริปต์ที่บันทึกเวลาเริ่มต้น เวลาสิ้นสุด ขนาดไฟล์ และรหัสการออกจากการทำงานลงในตารางเฝ้าติดตามหรือระบบแจ้งเตือน

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

ตรวจสอบอย่างรวดเร็ว: เชิงตรรกะกับเชิงกายภาพ

ทดสอบความเข้าใจเกี่ยวกับกลยุทธ์การสำรองข้อมูลเชิงตรรกะและเชิงกายภาพใน PostgreSQL

สรุปบทเรียน: การสำรองข้อมูลเชิงตรรกะกับเชิงกายภาพ

ในบทเรียนนี้ คุณได้สำรวจกลยุทธ์การสำรองข้อมูล PostgreSQL พื้นฐานสองแบบ:

การสำรองข้อมูลเชิงตรรกะใช้ pg_dump เพื่อส่งออกฐานข้อมูลเป็นคำสั่งภาษาเอสคิวแอล ย้ายข้ามระบบได้ มนุษย์อ่านได้ และรองรับการกู้คืนบางส่วน แต่ทำงานช้าเมื่อใช้กับฐานข้อมูลขนาดใหญ่มาก
การสำรองข้อมูลเชิงกายภาพใช้ pg_basebackup เพื่อคัดลอกไฟล์ข้อมูลดิบ เมื่อใช้ร่วมกับการจัดเก็บ WAL จะช่วยให้กู้คืนได้รวดเร็วและรองรับการกู้คืน ณ เวลาใดเวลาหนึ่ง แต่ใช้ได้กับรุ่นเฉพาะและกู้คืนทั้งคลัสเตอร์เสมอ
ระบบที่ใช้งานจริงมักผสานทั้งสองกลยุทธ์เข้าด้วยกัน ได้แก่ การสำรองข้อมูลพื้นฐานเชิงกายภาพพร้อมการจัดเก็บ WAL เพื่อการกู้คืนที่รวดเร็วและละเอียด รวมถึงการส่งออกข้อมูลเชิงตรรกะเป็นระยะเพื่อให้ย้ายข้ามระบบได้
ควรทดสอบการกู้คืนเสมอ ข้อมูลสำรองที่ไม่เคยทดสอบไม่อาจเชื่อถือได้เมื่อเกิดภัยพิบัติจริง

การทำความเข้าใจแนวทางทั้งสองนี้เป็นสิ่งจำเป็นสำหรับการออกแบบแผนกู้คืนจากภัยพิบัติที่แข็งแกร่งสำหรับการติดตั้งใช้งาน PostgreSQL ทุกประเภท

คำถามที่พบบ่อย

บทเรียน “การสำรองข้อมูลเชิงตรรกะเทียบกับเชิงกายภาพ” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “การสำรองข้อมูลเชิงตรรกะเทียบกับเชิงกายภาพ” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “การสำรองข้อมูลเชิงตรรกะเทียบกับเชิงกายภาพ”

pg_dump และการสำรองข้อมูลพื้นฐาน คุณปฏิบัติ SQL Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Academy หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 1 จากทั้งหมด 4 บทเรียน

บทเรียน “การสำรองข้อมูลเชิงตรรกะเทียบกับเชิงกายภาพ” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Academy นี้ได้ไหม

ได้ บทเรียน SQL Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

การสำรองข้อมูลเชิงตรรกะเทียบกับเชิงกายภาพ
การกู้คืน ณ เวลาใดเวลาหนึ่ง
การทดสอบการกู้คืนข้อมูลของคุณ
การวางแผนกู้คืนจากภัยพิบัติ

← กลับไปที่ SQL Academy