دیتابیس و SQLدیتابیس در کار واقعیدرس ۹ از ۱۰

بکاپ و بازیابی

از داده نسخه‌ی پشتیبان بگیر و مطمئن شو برمی‌گردد: pg_dump در قالب متنی و custom، دیدن محتوای بکاپ با pg_restore --list، بازیابی در یک دیتابیس جدا، نجات یک جدول پاک‌شده، بکاپ نقش‌ها با pg_dumpall --globals-only، اسکریپت بکاپ خودکار با cron و نگه‌داری ۷ روزه، قانون ۳-۲-۱، خروجی و ورودی CSV با ‎\copy، و مرور بکاپ فیزیکی.

حدود ۲۲ دقیقه مطالعه
۷ مثال اجراشده
۳ تمرین با جواب
مقدماتی تا متوسط
به‌روزرسانی: ۲۹ مهر ۱۴۰۵

توی این درس یاد می‌گیری از دیتابیس بکاپ بگیری و، مهم‌تر از آن، برگردانی‌اش. با pg_dump یک نسخه‌ی کامل می‌گیری (هم به‌صورت فایل SQL خوانا، هم در قالب فشرده‌ی custom)، داخلش را نگاه می‌کنی، آن را در یک دیتابیس جدا بازیابی و بررسی می‌کنی، یک جدول را که «اشتباهی» پاک شده نجات می‌دهی، و در آخر یک اسکریپت بکاپ خودکار می‌نویسی که هر شب با cron اجرا شود، بکاپ‌های قدیمی را پاک کند و نتیجه را در لاگ بنویسد.

مسئله: بکاپی که هیچ‌وقت امتحان نشده

دیسک خراب می‌شود، سرور ابری حذف می‌شود، کسی DROP TABLE را در دیتابیس اشتباه می‌زند، یک باگ نیمی از سطرها را به‌روز می‌کند. در همه‌ی این‌ها فقط یک چیز نجاتت می‌دهد: نسخه‌ای از داده که جای دیگری است و واقعاً برمی‌گردد. بیشتر تیم‌ها بکاپ دارند؛ تعداد کمی بازیابی را امتحان کرده‌اند. و روز حادثه می‌فهمند فایل‌ها خالی‌اند، نیمه‌کاره‌اند، یا روی همان دیسکی بوده‌اند که خراب شد.

تشبیه: کپی شناسنامه

بکاپ مثل کپی گرفتن از مدارک مهم است. کپی‌ای که کنار اصل در همان کشو باشد، در آتش‌سوزی با اصل می‌سوزد؛ پس یک کپی را جای دیگری (خانه‌ی پدر و مادر) می‌گذاری. کپی‌ای که ناخوانا باشد، به درد نمی‌خورد؛ پس یک بار نگاهش می‌کنی که واضح است. و کپی سه سال پیش، آدرس جدید را ندارد؛ پس هر از گاهی کپی تازه می‌گیری. بکاپ دیتابیس همین سه قانون را دارد: جای دیگر، امتحان‌شده، مرتب.

بکاپ فقط وقتی کامل است که بازیابی‌اش امتحان شده باشد؛ هر مرحله می‌تواند بی‌صدا شکست بخورد.

مثال‌های عملی

داده و دسترسی‌ها را تازه کن (grants.sql را در تمرین درس قبل ساختی؛ اگر نساختی، خط دوم را رد کن):

psql shop -f ~/shop.sql
psql shop -f ~/grants.sql
mkdir -p ~/backups

مثال ۱: اولین بکاپ، فایل SQL

pg_dump shop > ~/backups/shop.sql
ls -lh ~/backups/shop.sql
head -30 ~/backups/shop.sql

-rw-rw-r-- 1 ali ali 9.3K Oct 21 20:31 /home/ali/backups/shop.sql
--
-- PostgreSQL database dump
--

\restrict KmI7oteYA8LCiz7qMPiGgcvh0R23Edi6Y3Ek9TQ5sN9HbSOdldZANpqnoVXqY3c

-- Dumped from database version 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
-- Dumped by pg_dump version 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: customers; Type: TABLE; Schema: public; Owner: ali
--

CREATE TABLE public.customers (
    id integer NOT NULL,

pg_dump کل دیتابیس را به‌صورت دستورهای SQL می‌نویسد: CREATE TABLEها، داده، قیدها، ایندکس‌ها و GRANTها. اجرای این فایل روی یک دیتابیس خالی، همان دیتابیس را دوباره می‌سازد. (خط \restrict یک قفل امنیتی psql است که نسخه‌های جدید اضافه می‌کنند تا داده‌ی دستکاری‌شده در فایل نتواند دستور psql اجرا کند؛ کاری با آن نداری.) داده چطور ذخیره شده؟

grep -n 'COPY public.products' -A3 ~/backups/shop.sql
grep -n GRANT ~/backups/shop.sql | head -4

222:COPY public.products (id, name, category, price, stock, discount, added_on) FROM stdin;
223-1  کتاب پایتون  کتاب  320000  12  10  2026-03-10
224-2  کتاب لینوکس  کتاب  280000  0  \N  2026-01-22
225-3  کتاب داکر  کتاب  350000  5  \N  2026-08-01
319:GRANT SELECT,INSERT,UPDATE ON TABLE public.customers TO shop_app;
326:GRANT SELECT(id) ON TABLE public.customers TO readonly;
333:GRANT SELECT(city) ON TABLE public.customers TO readonly;
340:GRANT SELECT(joined_on) ON TABLE public.customers TO readonly;

داده با دستور COPY (سریع‌تر از هزاران INSERT) و ستون‌ها با Tab جدا شده‌اند؛ \N یعنی NULL. دسترسی‌ها هم در بکاپ هستند، ولی خود نقش‌ها (shop_app و رمزش) نه؛ آن‌ها مال کل سرورند، نه یک دیتابیس (مثال ۶).

pg_dump روی دیتابیسی که در حال کار است اجرا می‌شود و برنامه‌ها را متوقف نمی‌کند. با این حال بکاپ سازگار است: عکسی از یک لحظه‌ی واحد، حتی اگر وسط کار سفارش‌های تازه ثبت شوند (پشت پرده).

مثال ۲: قالب custom

pg_dump -Fc shop -f ~/backups/shop.dump
ls -lh ~/backups/
pg_restore --list ~/backups/shop.dump | head -25

total 24K
-rw-rw-r-- 1 ali ali  12K Oct 21 20:31 shop.dump
-rw-rw-r-- 1 ali ali 9.3K Oct 21 20:31 shop.sql
;
; Archive created at 2026-10-21 20:31:17 +0330
;     dbname: shop
;     TOC Entries: 42
;     Compression: gzip
;     Dump Version: 1.15-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
;     Dumped by pg_dump version: 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
;
;
; Selected TOC Entries:
;
218; 1259 25517 TABLE public customers ali
3440; 0 0 ACL public TABLE customers ali
3441; 0 0 ACL public COLUMN customers.id ali
3442; 0 0 ACL public COLUMN customers.city ali
3443; 0 0 ACL public COLUMN customers.joined_on ali
217; 1259 25516 SEQUENCE public customers_id_seq ali
3444; 0 0 SEQUENCE OWNED BY public customers_id_seq ali
3445; 0 0 ACL public SEQUENCE customers_id_seq ali
221; 1259 25543 TABLE public order_items ali
3446; 0 0 ACL public TABLE order_items ali

-Fc (format custom) یک فایل دودویی فشرده می‌سازد. اینجا بزرگ‌تر شد چون داده‌ی ما خیلی کم است و سربار فرمت غالب است؛ روی دیتابیس واقعی معمولاً چند برابر کوچک‌تر از فایل متنی است. مزیت اصلی‌اش انعطاف است: pg_restore --list فهرست محتوا (TOC، table of contents) را نشان می‌دهد و می‌شود فقط بخش‌هایی را برگرداند. (ACL = access control list، یعنی همان GRANTها.)

فایل SQL را با چشم می‌خوانی و با psql برمی‌گردانی؛ custom فشرده است و با pg_restore انتخابی برمی‌گردد.

مثال ۳: بازیابی، همیشه اول در دیتابیس جدا

هرگز بکاپ را «امتحانی» روی دیتابیس اصلی برنگردان. یک دیتابیس خالی بساز:

createdb shop_restore
pg_restore -d shop_restore ~/backups/shop.dump
echo "exit=$?"
psql shop_restore -c "SELECT (SELECT count(*) FROM products) AS products, (SELECT count(*) FROM orders) AS orders, (SELECT count(*) FROM order_items) AS items"

exit=0
 products | orders | items
----------+--------+-------
       12 |      6 |     9
(1 row)

کد خروج صفر و تعداد سطرها با اصل یکی است. فایل SQL متنی را با psql برمی‌گردانی:

dropdb shop_restore
createdb shop_restore
psql -q shop_restore -f ~/backups/shop.sql
psql shop_restore -c '\dt'

 set_config
------------

(1 row)

 setval
--------
      5
(1 row)

 setval
--------
      6
(1 row)

 setval
--------
     12
(1 row)

          List of relations
 Schema |    Name     | Type  | Owner
--------+-------------+-------+-------
 public | customers   | table | ali
 public | order_items | table | ali
 public | orders      | table | ali
 public | products    | table | ali
(4 rows)

setvalها sequenceها را به آخرین عدد برمی‌گردانند تا سطر بعدی id تکراری نگیرد. dropdb shop_restore را بزن تا جا نگیرد.

مثال ۴: حادثه، یک جدول پاک شد

psql shop -c "DROP TABLE order_items"
psql shop -c "SELECT count(*) FROM order_items"

DROP TABLE
ERROR:  relation "order_items" does not exist
LINE 1: SELECT count(*) FROM order_items
                             ^

بقیه‌ی دیتابیس سالم است و نمی‌خواهی کل آن را به دیشب برگردانی (سفارش‌های امروز از بین می‌روند). راه امن: بکاپ را در دیتابیس جدا برگردان و فقط همان جدول را از آن به اصلی منتقل کن:

createdb shop_restore
pg_restore -d shop_restore ~/backups/shop.dump
pg_dump -t order_items shop_restore | psql -q shop
psql shop -c "SELECT count(*) FROM order_items" -c '\d order_items'
dropdb shop_restore

 set_config
------------

(1 row)

 count
-------
     9
(1 row)

              Table "public.order_items"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 order_id   | integer |           | not null |
 product_id | integer |           | not null |
 qty        | integer |           | not null |
 unit_price | integer |           | not null |
Indexes:
    "order_items_pkey" PRIMARY KEY, btree (order_id, product_id)
Check constraints:
    "order_items_qty_check" CHECK (qty > 0)
Foreign-key constraints:
    "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)
    "order_items_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id)

pg_dump -t جدول فقط یک جدول را، با کلید و قیدها و ایندکس‌هایش، بیرون می‌دهد و | آن را مستقیم به psql روی دیتابیس اصلی می‌فرستد (همان pipe دوره‌ی لینوکس). جدول با همه‌ی قیدهایش برگشت. قیمتش: هر قلم سفارشی که بعد از بکاپ دیشب ثبت شده بود، گم شده. فاصله‌ی بین بکاپ‌ها یعنی حداکثر داده‌ای که حاضری از دست بدهی (به آن RPO می‌گویند).

⚡ بررسی سریع

کسی DELETE بدون WHERE روی جدول سفارش‌های امروز زده. بهترین قدم اول؟

جواب درست: بکاپ را در دیتابیس جدا برگردان، بررسی کن، بعد فقط داده‌ی لازم را منتقل کن

برگرداندن کامل روی اصلی، سفارش‌های بعد از بکاپ را هم پاک می‌کند.

مثال ۵: خروجی و ورودی CSV با ‎`\copy`

گاهی بکاپ کامل لازم نیست؛ تیم مالی یک فایل اکسل می‌خواهد یا داده از یک فایل CSV می‌آید:

psql shop -c "\copy (SELECT name, category, price FROM products ORDER BY id LIMIT 3) TO ~/products.csv WITH (FORMAT csv, HEADER)"
cat ~/products.csv

COPY 3
name,category,price
کتاب پایتون,کتاب,320000
کتاب لینوکس,کتاب,280000
کتاب داکر,کتاب,350000

و برعکس، وارد کردن CSV به یک جدول:

psql shop -c "CREATE TABLE price_import (name TEXT, category TEXT, price INTEGER)" \
          -c "\copy price_import FROM ~/products.csv WITH (FORMAT csv, HEADER)" \
          -c "SELECT * FROM price_import" \
          -c "DROP TABLE price_import"

CREATE TABLE
COPY 3
    name     | category | price
-------------+----------+--------
 کتاب پایتون | کتاب     | 320000
 کتاب لینوکس | کتاب     | 280000
 کتاب داکر   | کتاب     | 350000
(3 rows)

DROP TABLE

\copy (با بک‌اسلش) دستور psql است و فایل را روی کامپیوتر خودت می‌خواند و می‌نویسد. COPY بدون بک‌اسلش دستور SQL است و فایل را روی سرور دیتابیس، با کاربر postgres، باز می‌کند؛ برای همین برای کاربر معمولی مجاز نیست.

مثال ۶: بکاپ نقش‌ها

نقش‌ها و رمزهایشان در هیچ pg_dumpی نیستند. با pg_dumpall --globals-only (فقط چیزهای «سراسری» سرور) جدا بکاپ بگیر:

sudo -u postgres pg_dumpall --globals-only > ~/backups/globals.sql
grep -v '^--' ~/backups/globals.sql | grep -v '^$' | cut -c1-110

\restrict eAtU2SrxjeE3iEDviHsquCTWJJmEayddoNyJbAWAawfGTggp3nTluEduyIyBEQD
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
CREATE ROLE ali;
ALTER ROLE ali WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE auditor;
ALTER ROLE auditor WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD '
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE readonly;
ALTER ROLE readonly WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE report;
ALTER ROLE report WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'S
CREATE ROLE shop_app;
ALTER ROLE shop_app WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD
GRANT readonly TO auditor WITH INHERIT TRUE GRANTED BY postgres;
GRANT readonly TO report WITH INHERIT TRUE GRANTED BY postgres;
\unrestrict eAtU2SrxjeE3iEDviHsquCTWJJmEayddoNyJbAWAawfGTggp3nTluEduyIyBEQD

(cut -c1-110 خط‌ها را کوتاه کرد تا هش رمزها چاپ نشود.) > را شِل تو اجرا می‌کند، برای همین فایل در پوشه‌ی خودت و مال خودت ساخته می‌شود، با اینکه pg_dumpall با postgres اجرا شد. این فایل هش رمزها را دارد؛ با آن مثل کلید رفتار کن: chmod 600 ~/backups/globals.sql (اشتباه رایج ۵).

مثال ۷: بکاپ خودکار

اسکریپت ~/bin/backup-db.sh (پوشه را با mkdir -p ~/bin بساز):

#!/usr/bin/env bash
# بکاپ روزانه‌ی یک دیتابیس با نگه‌داری ۷ روزه
set -euo pipefail

DB="${1:-shop}"
DIR="$HOME/backups"
KEEP_DAYS=7
STAMP=$(date +%F_%H%M)
FILE="$DIR/$DB-$STAMP.dump"

mkdir -p "$DIR"
trap 'rm -f "$FILE.tmp"' EXIT          # اگر وسط کار خطا شد، فایل نیمه‌کاره نماند

pg_dump -Fc "$DB" -f "$FILE.tmp"
pg_restore --list "$FILE.tmp" > /dev/null   # فایل سالم و خواناست؟
mv "$FILE.tmp" "$FILE"

find "$DIR" -name "$DB-*.dump" -mtime +"$KEEP_DAYS" -print -delete
echo "$(date '+%F %T') ok $FILE $(du -h "$FILE" | cut -f1)"

همه‌ی ابزارهای دوره‌ی bash اینجا هستند: set -euo pipefail تا با اولین خطا بایستد (درس مدیریت خطا)، ${1:-shop} مقدار پیش‌فرض، trap ... EXIT برای پاک‌سازی، و find -mtime +7 -delete برای پاک کردن فایل‌های قدیمی‌تر از ۷ روز. بکاپ اول در ‎.tmp نوشته می‌شود و فقط بعد از بررسی، اسم نهایی را می‌گیرد؛ پس هر فایل ‎.dump در پوشه، یک بکاپ کامل است.

امتحانش کن. دو فایل قدیمی ساختگی بساز (touch -d زمان تغییر فایل را عوض می‌کند):

chmod +x ~/bin/backup-db.sh
touch -d "10 days ago" ~/backups/shop-2026-10-11_0230.dump
touch -d "3 days ago" ~/backups/shop-2026-10-18_0230.dump
~/bin/backup-db.sh shop
ls ~/backups/

/home/ali/backups/shop-2026-10-11_0230.dump
2026-10-21 20:31:37 ok /home/ali/backups/shop-2026-10-21_2031.dump 12K
globals.sql
shop-2026-10-18_0230.dump
shop-2026-10-21_2031.dump
shop.dump
shop.sql

فایل ۱۰ روزه پاک شد (find با -print اسمش را چاپ کرد) و ۳ روزه ماند. حالا ببین با خطا چه می‌شود:

~/bin/backup-db.sh nosuchdb; echo "exit=$?"
ls ~/backups/

pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "nosuchdb" does not exist
exit=1
globals.sql
shop-2026-10-18_0230.dump
shop-2026-10-21_2031.dump
shop.dump
shop.sql

خطا، کد خروج ۱، و هیچ فایل خالی یا نیمه‌کاره‌ای. دقیقاً برعکس سناریو. حالا با crontab -e (درس cron) اجرای شبانه را اضافه کن:

30 2 * * * $HOME/bin/backup-db.sh shop >> $HOME/backups/backup.log 2>&1

هر شب ساعت ۲:۳۰ و خروجی و خطا در backup.log. چون cron با کاربر تو اجرا می‌شود و psql از سوکت محلی با روش peer وصل می‌شود، رمزی لازم نیست.

پشت پرده: عکس لحظه‌ای و بکاپ فیزیکی

pg_dump در یک تراکنش با سطح ایزوله‌ی بالا اجرا می‌شود و از مکانیزم MVCC (چندنسخه‌ای) PostgreSQL استفاده می‌کند: هر تراکنش دیتابیس را همان‌طور می‌بیند که در لحظه‌ی شروعش بود، حتی اگر بقیه هم‌زمان چیزی عوض کنند (نسخه‌های قدیمی سطرها تا تمام شدن تراکنش نگه داشته می‌شوند). برای همین بکاپ سازگار است و برنامه‌ها مسدود نمی‌شوند. البته روی دیتابیس خیلی بزرگ، بکاپ ساعت‌ها طول می‌کشد و بار زیادی روی دیسک می‌گذارد.

pg_dump بکاپ منطقی است (دستورهای ساختن داده). برای دیتابیس‌های بزرگ از بکاپ فیزیکی هم استفاده می‌کنند: pg_basebackup کپی خود فایل‌های دیتابیس را می‌گیرد و با آرشیو WAL (همان دفتر ثبت تغییرات درس insert-update-delete) می‌شود دیتابیس را به هر لحظه‌ی دلخواه برگرداند (point-in-time recovery، مثلاً «ساعت ۱۶:۵۹، یک دقیقه قبل از DROP»). ابزارهایی مثل pgBackRest این کار را مدیریت می‌کنند. برای دیتابیس‌های تا چند ده گیگابایت، pg_dump شبانه کاملاً کافی است.

نوع	ابزار	مزیت	عیب
منطقی	`pg_dump`	ساده، انتخابی، بین نسخه‌ها قابل انتقال	کند روی دیتابیس بزرگ؛ فقط تا لحظه‌ی بکاپ
فیزیکی + WAL	`pg_basebackup`، pgBackRest	سریع، بازیابی تا هر لحظه	پیچیده‌تر؛ فقط همان نسخه‌ی اصلی PostgreSQL

جدول‌های مرجع

دستور	کار
`pg_dump db > f.sql`	بکاپ متنی
`pg_dump -Fc db -f f.dump`	بکاپ custom فشرده
`pg_dump -t table db`	فقط یک جدول
`pg_dump db \| gzip > f.sql.gz`	متنی فشرده
`psql db -f f.sql`	بازیابی متنی
`pg_restore -d db f.dump`	بازیابی custom
`pg_restore --list f.dump`	فهرست محتوا
`pg_restore --no-owner --no-privileges -d db f.dump`	بازیابی بدون صاحب و GRANT
`pg_dumpall --globals-only`	نقش‌ها و رمزها
`\copy (SELECT ...) TO f.csv WITH (FORMAT csv, HEADER)`	خروجی CSV
`\copy t FROM f.csv WITH (FORMAT csv, HEADER)`	ورودی CSV

قانون ۳-۲-۱: حداقل ۳ نسخه از داده (اصل + دو بکاپ)، روی ۲ نوع رسانه یا سرور متفاوت، و ۱ نسخه در مکان دیگر (یک سرور در دیتاسنتر دیگر یا فضای ذخیره‌سازی ابری). بکاپ روی همان دیسکِ دیتابیس فقط در برابر «اشتباه انسانی» کمک می‌کند، نه خرابی دیسک یا سرور.

اشتباهات رایج

۱) بکاپ امتحان‌نشده

راه‌حل: حداقل ماهی یک بار (بهتر: خودکار) آخرین بکاپ را در دیتابیس جدا برگردان و تعداد سطرها را با اصل مقایسه کن (تمرین سخت).

۲) اسکریپت بکاپی که خطا را قورت می‌دهد

بدون set -e و pipefail، pg_dump db | gzip > f.gz حتی وقتی pg_dump شکست بخورد یک فایل کوچک «موفق» می‌سازد. راه‌حل: set -euo pipefail، نوشتن در فایل موقت، بررسی با pg_restore --list.

۳) بکاپ کنار دیتابیس

دیسک یا سرور که برود، بکاپ هم رفته. راه‌حل: کپی خودکار به جای دیگر (rsync یا scp درس SSH، یا فضای ابری).

۴) بازیابی روی دیتابیس اصلی

یک pg_restore اشتباه روی دیتابیس اصلی، داده‌ی بعد از بکاپ را هم خراب می‌کند. راه‌حل: همیشه اول در دیتابیس جدا (shop_restore).

۵) بکاپ بدون حفاظت

فایل بکاپ همه‌ی داده‌ی مشتری‌ها و هش رمزهاست. اگر روی سرور با مجوز 644 یا در یک bucket عمومی باشد، نشت داده است. راه‌حل: chmod 600 (یا پوشه با 700)، و برای نسخه‌ی خارج از سرور، رمزنگاری (مثلاً با gpg).

تمرین

تمرینآسان

از دیتابیس shop یک بکاپ متنی فشرده بگیر (pg_dump و gzip با pipe)، اندازه‌اش را ببین، و بدون باز کردن کامل فایل، سه خط اولش را نشان بده.

دیدن جواب

pg_dump shop | gzip > ~/backups/shop.sql.gz
ls -lh ~/backups/shop.sql.gz
gunzip -c ~/backups/shop.sql.gz | head -3

-rw-rw-r-- 1 ali ali 2.2K Oct 21 20:31 /home/ali/backups/shop.sql.gz
--
-- PostgreSQL database dump
--

۹٫۳ کیلوبایت به ۲٫۲ کیلوبایت رسید. gunzip -c خروجی را به‌جای فایل روی صفحه می‌دهد. بازیابی: gunzip -c ~/backups/shop.sql.gz | psql shop_restore.

تمرینمتوسط

فرض کن قیمت همه‌ی کتاب‌ها اشتباهی صفر شده (UPDATE products SET price = 0 WHERE category = 'کتاب'). بدون برگرداندن کل جدول (چون موجودی‌ها از دیشب عوض شده‌اند)، فقط قیمت کتاب‌ها را از بکاپ برگردان.

دیدن جواب

psql shop -c "UPDATE products SET price = 0 WHERE category = 'کتاب'"
createdb shop_restore
pg_restore -d shop_restore ~/backups/shop.dump
psql shop_restore -c "\copy (SELECT id, price FROM products WHERE category = 'کتاب') TO ~/book_prices.csv WITH (FORMAT csv)"
psql shop <<'SQL'
BEGIN;
CREATE TEMP TABLE fix (id INTEGER, price INTEGER);
\copy fix FROM ~/book_prices.csv WITH (FORMAT csv)
UPDATE products p SET price = fix.price FROM fix WHERE p.id = fix.id;
SELECT name, price FROM products WHERE category = 'کتاب' ORDER BY id;
COMMIT;
SQL
dropdb shop_restore

BEGIN
CREATE TABLE
COPY 3
UPDATE 3
    name     | price
-------------+--------
 کتاب پایتون | 320000
 کتاب لینوکس | 280000
 کتاب داکر   | 350000
(3 rows)

COMMIT

CREATE TEMP TABLE جدولی می‌سازد که با بسته شدن اتصال خودش پاک می‌شود. UPDATE ... FROM یک UPDATE همراه با JOIN است: برای هر محصول، قیمت را از سطر جورشده‌ی fix بردار. فقط ستون price سه سطر عوض شد و موجودی امروز دست نخورد. همه داخل تراکنش، تا اگر عدد UPDATE غیر از ۳ بود، ROLLBACK بزنی.

تمرینسخت

یک اسکریپت ~/bin/verify-backup.sh بنویس که آخرین فایل shop-*.dump را پیدا کند، در یک دیتابیس موقت برگرداند، تعداد سطرهای هر جدول را با دیتابیس اصلی مقایسه کند، دیتابیس موقت را پاک کند (حتی اگر وسط کار خطا شد) و اگر فرقی بود با کد ۱ خارج شود. (اگر بعد از بکاپ چیزی در اصلی عوض شده، فرق طبیعی است؛ برای امتحان، درست بعد از یک بکاپ اجرایش کن.)

دیدن جواب

#!/usr/bin/env bash
set -euo pipefail

LATEST=$(ls -t "$HOME"/backups/shop-*.dump | head -1)
TMPDB="verify_$(date +%s)"
trap 'dropdb --if-exists "$TMPDB"' EXIT

createdb "$TMPDB"
pg_restore -d "$TMPDB" "$LATEST"

COUNT_SQL="SELECT 'products', count(*) FROM products
UNION ALL SELECT 'customers', count(*) FROM customers
UNION ALL SELECT 'orders', count(*) FROM orders
UNION ALL SELECT 'order_items', count(*) FROM order_items"

if diff <(psql -At shop -c "$COUNT_SQL") <(psql -At "$TMPDB" -c "$COUNT_SQL"); then
    echo "ok: $LATEST matches"
else
    echo "MISMATCH: $LATEST" >&2
    exit 1
fi

chmod +x ~/bin/verify-backup.sh
~/bin/backup-db.sh shop && ~/bin/verify-backup.sh
psql shop -c "DELETE FROM order_items WHERE order_id = 6"
~/bin/verify-backup.sh; echo "exit=$?"

2026-10-21 20:34:12 ok /home/ali/backups/shop-2026-10-21_2034.dump 12K
ok: /home/ali/backups/shop-2026-10-21_2034.dump matches
DELETE 2
4c4
< order_items|7
---
> order_items|9
MISMATCH: /home/ali/backups/shop-2026-10-21_2034.dump
exit=1

نکته‌ها: psql -At خروجی را بدون جدول‌کشی و سرتیتر می‌دهد (A = unaligned، t = tuples only)، پس diff فقط عددها را مقایسه می‌کند. <(...) (process substitution در bash) خروجی یک دستور را مثل یک فایل به diff می‌دهد. UNION ALL نتیجه‌ی چند SELECT را زیر هم می‌چسباند. trap حتی بعد از exit 1 دیتابیس موقت را پاک کرد. داده را با psql shop -f ~/shop.sql برگردان.

✓ چک‌لیست بکاپ یک دیتابیس واقعی

pg_dump شبانه با cron و لاگ
اسکریپت با set -euo pipefail و فایل موقت
نگه‌داری چند روزه و پاک کردن خودکار قدیمی‌ها
pg_dumpall --globals-only برای نقش‌ها
کپی خودکار به سرور یا فضای دیگر (قانون ۳-۲-۱)
مجوز 600 روی فایل‌های بکاپ؛ رمزنگاری برای نسخه‌ی بیرونی
بازیابی آزمایشی منظم در دیتابیس جدا
مستند کردن قدم‌های بازیابی برای روز حادثه

آزمونک

pg_dump روی دیتابیسی که برنامه‌ها در حال نوشتن در آن هستند چه بکاپی می‌دهد؟
فایل pg_dump -Fc را با چه برمی‌گردانی؟
نقش‌ها و رمزهایشان کجا بکاپ می‌شوند؟
چرا بکاپ آزمایشی را در دیتابیس جدا برمی‌گردانیم؟
فرق \copy و COPY؟
قانون ۳-۲-۱ یعنی…

جمع‌بندی

pg_dump بکاپ منطقی و سازگار بدون توقف برنامه‌ها؛ متنی (> f.sql، با psql برمی‌گردد) یا custom (-Fc، با pg_restore، انتخابی).
بازیابی همیشه اول در دیتابیس جدا؛ برای نجات یک جدول: بازیابی در جدا و pg_dump -t ... | psql.
نقش‌ها جدا: pg_dumpall --globals-only.
اسکریپت بکاپ: set -euo pipefail، فایل موقت، بررسی، نگه‌داری با find -mtime، cron با لاگ.
بکاپ امتحان‌نشده بکاپ نیست؛ قانون ۳-۲-۱؛ فایل‌ها را با 600 و رمزنگاری محافظت کن.
\copy برای خروجی و ورودی CSV.

برگه‌ی تقلب این درس

دستور	کاری که می‌کند
`pg_dump -Fc shop -f shop.dump`	بکاپ custom
`pg_restore --list shop.dump`	فهرست محتوا
`createdb x && pg_restore -d x shop.dump`	بازیابی در دیتابیس جدا
`pg_dump -t t x \| psql shop`	برگرداندن یک جدول
`sudo -u postgres pg_dumpall --globals-only`	بکاپ نقش‌ها
`\copy (SELECT ...) TO f.csv WITH (FORMAT csv, HEADER)`	خروجی CSV
`30 2 * * * ~/bin/backup-db.sh >> log 2>&1`	cron شبانه
`find dir -mtime +7 -delete`	پاک کردن قدیمی‌ها

مسئله: بکاپی که هیچ‌وقت امتحان نشده

تشبیه: کپی شناسنامه

مثال‌های عملی

مثال ۱: اولین بکاپ، فایل SQL

مثال ۲: قالب custom

مثال ۳: بازیابی، همیشه اول در دیتابیس جدا

مثال ۴: حادثه، یک جدول پاک شد

مثال ۵: خروجی و ورودی CSV با ‎\copy

مثال ۶: بکاپ نقش‌ها

مثال ۷: بکاپ خودکار

پشت پرده: عکس لحظه‌ای و بکاپ فیزیکی

جدول‌های مرجع

اشتباهات رایج

۱) بکاپ امتحان‌نشده

۲) اسکریپت بکاپی که خطا را قورت می‌دهد

۳) بکاپ کنار دیتابیس

۴) بازیابی روی دیتابیس اصلی

۵) بکاپ بدون حفاظت

تمرین

آزمونک

جمع‌بندی

مثال ۵: خروجی و ورودی CSV با ‎`\copy`