Backup va Restore — pg_dump, pg_restore, pg_basebackup

~20 daq · PostgreSQL · Xavfsizlik va Boshqaruv

Server ishdan chiqdi. Disk o'chdi. Yoki kimdir DROP TABLE qildi. Backup bo'lmasa — ma'lumot yo'qoldi.

PostgreSQL uchun uchta asosiy backup vositasi: pg_dump, pg_restore, pg_basebackup.

pg_dump — mantiqiy backup

Database yoki jadvallarni SQL fayl ko'rinishida saqlash:

# Butun database
pg_dump myapp > backup.sql

# Siqilgan format (tezroq, kichikroq)
pg_dump -Fc myapp > backup.dump

# Faqat bir jadval
pg_dump -t foydalanuvchilar myapp > users.sql

# Ulanish parametrlari bilan
pg_dump -h localhost -U postgres -d myapp > backup.sql

pg_restore — tiklash

# SQL fayldan tiklash
psql myapp < backup.sql

# .dump fayldan tiklash
pg_restore -d myapp backup.dump

# Parallel tiklash (tez)
pg_restore -j 4 -d myapp backup.dump
# -j 4 — 4 parallel jarayon

# Faqat bir jadval
pg_restore -t foydalanuvchilar -d myapp backup.dump

pg_dumpall — barcha databaselar

# Barcha databaselar + foydalanuvchilar + sozlamalar
pg_dumpall > full_backup.sql

# Faqat global ob'ektlar (userlar, rollar)
pg_dumpall --globals-only > globals.sql

pg_basebackup — fizik backup

Butun PostgreSQL klasterining fizik nusxasi — eng tez backup va tiklash:

pg_basebackup -D /backup/pgdata -Ft -z -P
# -D  — saqlash joyi
# -Ft — tar format
# -z  — gzip siqish
# -P  — progress ko'rsatish

Point-in-time recovery (PITR) uchun WAL arxivlash bilan birga ishlatiladi.

Avtomatik backup — cron

# /etc/cron.d/postgres-backup
# Har kuni soat 02:00 da
0 2 * * * postgres pg_dump -Fc myapp > /backup/myapp_$(date +%Y%m%d).dump

# Eski backuplarni o'chirish (30 kundan eski)
0 3 * * * find /backup -name "*.dump" -mtime +30 -delete

Backup strategiyasi

Kunlik   — pg_dump (mantiqiy)
Haftalik — pg_basebackup (fizik)
Real-time — WAL arxivlash (PITR)

Tekshirish — backupni tiklash, to'g'ri ekanligini sinash
Saqlash    — boshqa server yoki S3/bulut

Tez-tez uchraydigan xatolar

Backupni hech qachon tekshirmaslik:

# Backup bor — lekin tiklash ishlarmikan?
pg_restore -d test_db backup.dump
# Test serverda sinash — har oy

Backup va production bir serverde:

Server o'chsa — backup ham yo'qoladi. Backup boshqa joyda bo'lishi shart.

Faqat pg_dump — PITR yo'q:

Kunlik backup bo'lsa — bugun yaratilgan ma'lumot yo'qolishi mumkin. WAL arxivlash bilan har daqiqalikgacha tiklash mumkin.

Xulosa

# Backup
pg_dump -Fc myapp > backup.dump       # bir database
pg_dumpall > full.sql                  # hammasi

# Tiklash
pg_restore -d myapp backup.dump
psql myapp < backup.sql

# Fizik backup
pg_basebackup -D /backup/pgdata -Ft -z -P

# Tekshirish
pg_restore -d test_db backup.dump && echo "OK"

3-2-1 qoidasi: 3 nusxa, 2 xil media, 1 tasi offsite (bulutda).

Keyingi dars

56-dars: Replication — Streaming Replication, asoslar