PostgreSQL: النسخ الاحتياطي والاستعادة والتوفر العالي في…

# تنسيق مخصص (موصى به، مضغوط، استعادة متوازية ممكنة)
pg_dump -h localhost -U postgres -Fc shop_db > /backup/shop_db_$(date +%Y%m%d).dump

# تنسيق دليل مع 4 عمال متوازيين
pg_dump -h localhost -U postgres -Fd shop_db -j 4 -f /backup/shop_db_dir/

# تنسيق SQL عادي (قابل للقراءة البشرية، قابل للتعديل قبل الاستعادة)
pg_dump -h localhost -U postgres -Fp shop_db > /backup/shop_db.sql

# تم تنفيذ الأمر بنجاح

# نسخ احتياطي لجدولي orders و order_items فقط
pg_dump -h localhost -U postgres -Fc \
  -t orders -t order_items \
  shop_db > /backup/order_tables.dump

# نسخ احتياطي لجميع الجداول المطابقة للنمط
pg_dump -h localhost -U postgres -Fc \
  -t 'order_*' \
  shop_db > /backup/order_prefix_tables.dump

# استبعاد جداول السجلات الكبيرة
pg_dump -h localhost -U postgres -Fc \
  --exclude-table='access_log_*' \
  shop_db > /backup/shop_no_logs.dump

# تم تنفيذ الأمر بنجاح

# نسخ احتياطي لجميع قواعد البيانات والأدوار (SQL عادي فقط)
pg_dumpall -h localhost -U postgres > /backup/cluster_full_$(date +%Y%m%d).sql

# نسخ احتياطي للأدوار فقط (مفيد للترحيل)
pg_dumpall -h localhost -U postgres --roles-only > /backup/roles_only.sql

# نسخ احتياطي لتعريفات مساحات الجداول فقط
pg_dumpall -h localhost -U postgres --tablespaces-only > /backup/tablespaces.sql

# تم تنفيذ الأمر بنجاح

# استعادة إلى قاعدة بيانات جديدة
createdb -h localhost -U postgres shop_db_restore
pg_restore -h localhost -U postgres -d shop_db_restore /backup/shop_db.dump

# استعادة متوازية (4 عمال، تنسيق دليل)
pg_restore -h localhost -U postgres -d shop_db -j 4 /backup/shop_db_dir/

# استعادة مع تنظيف (حذف الكائنات الموجودة أولاً)
pg_restore -h localhost -U postgres -d shop_db \
  --clean --if-exists /backup/shop_db.dump

# تم تنفيذ الأمر بنجاح

# سرد محتويات الأرشيف للعثور على أرقام إدخالات الجدول
pg_restore --list /backup/shop_db.dump

# استعادة جداول محددة فقط بالاسم
pg_restore -h localhost -U postgres -d shop_db \
  -t products -t categories \
  /backup/shop_db.dump

# استعادة الهيكل فقط (بدون بيانات)
pg_restore -h localhost -U postgres -d shop_db \
  --section=pre-data /backup/shop_db.dump

# تم تنفيذ الأمر بنجاح

# استعادة من مخرجات pg_dumpall
psql -h localhost -U postgres -f /backup/cluster_full_20250601.sql

# استعادة من تنسيق pg_dump العادي
createdb -h localhost -U postgres shop_db_new
psql -h localhost -U postgres -d shop_db_new -f /backup/shop_db.sql

# تم تنفيذ أمر psql بنجاح

-- تصدير إلى CSV مع رأس
COPY (SELECT order_id, customer_id, total_amount, order_date
      FROM orders
      WHERE order_status = 'completed'
      ORDER BY order_date DESC)
TO '/tmp/completed_orders.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- تصدير جدول كامل
COPY products TO '/tmp/products.csv' WITH (FORMAT csv, HEADER true);

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- استيراد من CSV
COPY products(product_name, unit_price, category, stock_qty)
FROM '/data/new_products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- استيراد مع معالجة الأخطاء (PG 17+)
COPY products(product_name, unit_price, category, stock_qty)
FROM '/data/new_products.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR ignore);

-- تم تنفيذ عبارة SQL بنجاح

-- تصدير ثنائي (أسرع، أصغر)
COPY orders TO '/tmp/orders.bin' WITH (FORMAT binary);

-- استيراد ثنائي
COPY orders FROM '/tmp/orders.bin' WITH (FORMAT binary);

-- تم تنفيذ عبارة SQL بنجاح

# إعدادات postgresql.conf
cat >> /etc/postgresql/16/main/postgresql.conf << 'EOF'
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
archive_timeout = 300
max_wal_senders = 3
EOF

# إعادة تشغيل PostgreSQL
pg_ctlcluster 16 main restart

# تم تنفيذ الأمر بنجاح

# نسخ احتياطي مادي كامل (أساس PITR)
pg_basebackup -h localhost -U replicator \
  -D /backup/base_$(date +%Y%m%d) \
  -Ft -z -P \
  --checkpoint=fast

# -Ft: تنسيق tar
# -z: ضغط gzip
# -P: إظهار التقدم
# --checkpoint=fast: فرض نقطة تحقق قبل النسخ الاحتياطي

# تم تنفيذ الأمر بنجاح

# الخطوة 1: إيقاف PostgreSQL
pg_ctlcluster 16 main stop

# الخطوة 2: مسح دليل البيانات الحالي
rm -rf /var/lib/postgresql/16/main/*

# الخطوة 3: استعادة النسخ الاحتياطي الأساسي
tar -xzf /backup/base_20250601/base.tar.gz \
  -C /var/lib/postgresql/16/main/

# الخطوة 4: إنشاء تكوين الاستعادة
cat >> /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2025-06-01 02:59:00'
recovery_target_action = 'promote'
EOF

# الخطوة 5: إنشاء ملف▶�شارة الاستعادة
touch /var/lib/postgresql/16/main/recovery.signal

# الخطوة 6: بدء PostgreSQL (سيستعيد إلى الوقت المستهدف)
pg_ctlcluster 16 main start

# سيقوم PostgreSQL بإعادة تشغيل WAL حتى 02:59:00 ثم الترقية

# تم تنفيذ الأمر بنجاح

# على الرئيسي: postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = '1GB'

# على الرئيسي: pg_hba.conf
echo 'host replication replicator 192.168.1.0/24 md5' >> pg_hba.conf

# على الرئيسي: إنشاء مستخدم نسخ
psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'RepP@ss';"

# على الاحتياطي: أخذ نسخ احتياطي أساسي
pg_basebackup -h primary_host -U replicator \
  -D /var/lib/postgresql/16/main -Fp -Xs -P -R

# -R: إنشاء standby.signal وتكوين تلقائي
# سيتصل الاحتياطي تلقائيًا بالرئيسي عند بدء التشغيل

# تم تنفيذ أمر psql بنجاح

-- على الناشر (قاعدة البيانات المصدر)
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- أو نشر جميع الجداول في مخطط
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- على المشترك (قاعدة البيانات الهدف)
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary_host dbname=shop_db user=replicator password=RepP@ss'
  PUBLICATION pub_orders;

-- التحقق من حالة النسخ
SELECT * FROM pg_stat_replication;       -- على ا��ناشر
SELECT * FROM pg_stat_subscription;      -- على المشترك

CREATE TABLE

-- التحقق من تأخر النسخ على الرئيسي
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag
FROM pg_stat_replication;

-- التحقق مما إذا كان الاحتياطي في وضع الاستعادة
SELECT pg_is_in_recovery();

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

#!/bin/bash
# سكريبت نسخ احتياطي يومي لـ shop_db

BACKUP_DIR="/backup/daily"
DATE=$(date +%Y%m%d_%H%M%S)
RETAIN_DAYS=7

# نسخ احتياطي منطقي كامل (pg_dump تنسيق مخصص)
pg_dump -h localhost -U postgres -Fc -Z6 \
  shop_db > ${BACKUP_DIR}/shop_db_${DATE}.dump

# نسخ احتياطي للأدوار بشكل منفصل
pg_dumpall -h localhost -U postgres --roles-only \
  > ${BACKUP_DIR}/roles_${DATE}.sql

# تنظيف النسخ الاحتياطية القديمة (الاحتفاظ بـ 7 أيام)
find ${BACKUP_DIR} -name "*.dump" -mtime +${RETAIN_DAYS} -delete
find ${BACKUP_DIR} -name "*.sql" -mtime +${RETAIN_DAYS} -delete

echo "تم إكمال النسخ الاحتياطي: shop_db_${DATE}.dump"

# تم تنفيذ الأمر بنجاح

# التحقق من أن نسخة pg_dump الاحتياطية قابلة للقراءة
pg_restore --list /backup/shop_db.dump > /dev/null
if [ $? -eq 0 ]; then
  echo "تم التحقق من النسخ الاحتياطي - سليم"
else
  echo "النسخ الاحتياطي تالف - تنبيه فريق العمليات!"
fi

# التحقق من أن أرشيف WAL لا يتأخر
psql -c "SELECT pg_current_wal_lsn(), pg_last_archive_lsn();"

# تم تنفيذ أمر psql بنجاح

-- الخطوة 1: تأكيد وقت الحادث وفقدان البيانات
SELECT pg_current_wal_lsn();  -- تسجيل LSN الحالي
SELECT COUNT(*) FROM products WHERE category = 'Electronics';  -- التحقق من الفقدان

-- الخطوة 2: التحقق من أن أرشيف WAL يحتوي على المقاطع المطلوبة
SELECT pg_last_archive_lsn();
-- يجب أن يكون >= LSN في 02:59 صباحًا

# الخطوة 3: إيقاف PostgreSQL فورًا للحفاظ على الحالة
pg_ctlcluster 16 main stop

# الخطوة 4: الحفاظ على ملفات WAL الحالية (لا تحذف!)
cp -r /var/lib/postgresql/16/main/pg_wal /tmp/pg_wal_backup/

# الخطوة 5: استعادة النسخ الاحتياطي الأساسي
rm -rf /var/lib/postgresql/16/main/*
tar -xzf /backup/base_20250531/base.tar.gz \
  -C /var/lib/postgresql/16/main/

# الخطوة 6: نسخ WAL المحفوظ مرة أخرى للاستعادة الكاملة
cp /tmp/pg_wal_backup/* /var/lib/postgresql/16/main/pg_wal/

# الخطوة 7: تكوين هدف PITR
cat >> /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2025-06-01 02:59:00'
recovery_target_action = 'promote'
EOF

touch /var/lib/postgresql/16/main/recovery.signal

# الخطوة 8: بدء التشغيل والتحقق
pg_ctlcluster 16 main start

# الخطوة 9: التحقق من الاستعادة
psql -c "SELECT COUNT(*) FROM products WHERE category = 'Electronics';"

-- الخطوة 10: بعد الاستعادة الناجحة، أخذ نسخة احتياطية جديدة
-- تشغيل في psql بعد التحقق من سلامة البيانات
SELECT pg_switch_wal();  -- فرض تبديل WAL لنقطة أرشيف نظيفة

# الخطوة 11: أخذ نسخة احتياطية أساسية جديدة لـ PITR المستقبلي
pg_basebackup -h localhost -U postgres \
  -D /backup/base_$(date +%Y%m%d) -Ft -z -P