💾 2. PostgreSQL Online Backup Using Low-Level API

In addition to pg_basebackup or pg_dump, PostgreSQL provides a low-level API for performing online physical backups. This method gives you more control over the backup process, often used by experienced DBAs in large-scale production systems.

---

📌 Prerequisites

• WAL Archiving must be enabled and working.

• You must have superuser privileges to run the backup functions.

• This method backs up the data directory (physical backup), not SQL data (like pg_dump).

---

🛠️ Steps for Online Backup Using Low-Level API

---

✅ Step 1: Start the Backup

Run the pg_backup_start() function as a superuser:

SELECT pg_backup_start(label => 'my_backup_label', fast => false);

📝 fast => true can be used to force an immediate checkpoint, which may speed up the process.

---

📁 Step 2: Copy the Data Directory (File System Level)

Use a file system tool like tar, rsync, or cpio to copy the entire data directory.

Example (Linux)

tar -cvzf /backups/full_backup.tar.gz /var/lib/pgsql/15/data

⚠️ Make sure you exclude the pg_wal directory if WAL files are being archived separately.

---

🛑 Step 3: Stop the Backup

Once the file system copy is complete, end the backup session

End the backup session and ensure all necessary WAL segments are archived for recovery:
SELECT * FROM pg_backup_stop(wait_for_archive => true);  < It is work in pgsql 17 and > lower version work as screen shot.


Waits until all required WAL files are safely archived.
Ensures the backup is recoverable and complete.

---

🔄 What This Process Does

✅ Ensures a consistent physical snapshot of your PostgreSQL instance.
✅ Ensures all WAL segments from the start to the end of the backup are archived.
✅ Database remains online and fully available during the entire process.

---

🧠 Summary

Step	Description
pg_backup_start()	Marks the beginning of a consistent backup window
tar / rsync / cpio	Copies the data directory while PostgreSQL is running
pg_backup_stop()	Ends the backup and finalizes WAL file archiving

---