7.Incremental Backup & Restore Demo using pg_basebackup

🧪 Demo: Step-by-Step

---

✅ 1. Create Sample Table & Insert Data

CREATE TABLE exercise1(stri VARCHAR, cpu INT, memory BIGINT);

INSERT INTO exercise1
SELECT 'Hello World', (random() * 100)::INT AS cpu, (random() * 10)::BIGINT AS memory
FROM generate_series(1, 10000);

---

🗃️ 2. Take Full Backup

pg_basebackup -D /var/lib/bk_dir/sun_full \
  --max-rate=5M \
  -X stream \
  -l "This is weekly full backup" \
  -P \
  -U postgres

✅ Directory /var/lib/bk_dir/sun_full/ will contain:

• backup_label: Details like start time, end time, WAL info
• backup_manifest: Metadata for incremental backup tracking

---

✅ 3. Verify Full Backup

• pg_verifybackup -D /var/lib/bk_dir/sun_full
• Ensures the backup is valid and complete.

✅ 4. Make Data Changes

CREATE TABLE exercise2(stri VARCHAR, cpu INT, memory BIGINT);

INSERT INTO exercise2
SELECT 'Hello World', (random() * 100)::INT, (random() * 10)::BIGINT
FROM generate_series(1, 10000);

---

🔁 5. Perform Incremental Backup 1 (Monday)

pg_basebackup -D /var/lib/bk_dir/mon_inc \
  -l "This is incremental backup 1" \
  -i "/var/lib/bk_dir/sun_full/backup_manifest" \
  -c fast \
  -P \
  -U postgres

• -i or --incremental: Specifies the manifest from the full backup

• Only files modified since full backup are copied

---

🔁 6. Perform Incremental Backup 2 (Tuesday)

pg_basebackup -D /var/lib/bk_dir/tus_inc \
  -l "This is incremental backup 2" \
  -i "/var/lib/bk_dir/mon_inc/backup_manifest" \
  -c fast \
  -P \
  -U postgres

• Builds on top of Monday’s incremental manifest

---

📂 Summary of Backup Chain

Day	Directory	Manifest Source
Sunday	/var/lib/bk_dir/sun_full	Full backup
Monday	/var/lib/bk_dir/mon_inc	sun_full/backup_manifest
Tuesday	/var/lib/bk_dir/tus_inc	mon_inc/backup_manifest

---

📄 Notes

• Each backup_manifest must be saved and used for the next incremental backup.
• You can use pg_verifybackup after each backup to validate.
• Use -X stream or WAL archiving to ensure WAL segments are captured.

Restore : We need to combined directory 

Step 1: Stop postgreSQL 

Step 2: Deleted data dir   Note : work on test server

Step 3: Combined all backup data  : Note keep backup in sequence it is matter.

 

pg_combinebackup /var/lib/pgsql/Demo/sun_full  /var/lib/pgsql/Demo/mon_inc /var/lib/pgsql/Demo/tus_inc  -o /var/lib/pgsql/17/data 

Step 4: Start postgreSQL & validate data .