📦3. pg_basebackup —📘 PostgreSQL Base Backup using

pg_basebackup

🧰 What is pg_basebackup?

pg_basebackup is a PostgreSQL utility used to take hot (online) base backups of an entire PostgreSQL database cluster.

✅ Use Cases:

• Full cluster backups for disaster recovery
• Setting up streaming replication
• Enabling Point-in-Time Recovery (PITR)

---

🚀 Key Features

• Automatically puts the database into and out of backup mode
• Requires no downtime
• Takes backup of the entire cluster (not individual databases)
• Supports streaming WALs and compression

---

🧾 Command Options

Option	Description
-D, --pgdata=DIR	Output directory for the base backup
-F, `--format=p	t`
-I, --incremental=OLD	Incremental backup (PostgreSQL 17+)
-r, --max-rate=RATE	Limit transfer rate (e.g., 100M)
-t, --target=TARGET	Recovery target (rarely used directly)
-T, --tablespace-mapping=OLDDIR=NEWDIR	Remap tablespace paths
--waldir=WALDIR	Location for WAL segment files
-X, `--wal-method=none	fetch




    none: no WAL

• fetch: copy after backup

• stream: stream during backup |
  | -z, --gzip | Compress tar format using gzip |
  | -Z, --compress=LEVEL | Compression level (0–9) |

---

🧑‍💻 Create a Replication Backup User

CREATE USER bkpadmin WITH REPLICATION ENCRYPTED PASSWORD 'abc@123';

GRANT pg_write_server_files TO bkpadmin;

🔐 The user does not need to be a superuser.

---

💾 Example Backup Commands

✅ Plain format, local backup:

pg_basebackup -U bkpadmin -D "/var/lib/pgsql/backups/full" -P

✅ Compressed tar format with streamed WAL:

pg_basebackup -U bkpadmin -D "/var/lib/pgsql/backups/full" -Ft -z -Xs -P

✅ Run backup from client machine:

pg_basebackup -h <server_ip_or_hostname> -p 5433 -U bkpadmin -D "/backups/prod" -Ft -z -Xs -P

📦 Adjust Transfer Rate with --max-rate

You can control the maximum speed at which the backup data is transferred from the source server using the --max-rate option. This is useful to avoid overloading the network or disk I/O.

✅ Example:

pg_basebackup -D "C:\Prod\bkp\full_backup" --max-rate=5M -X stream -P -U bkpadmin

🔍 Explanation:

Option	Meaning
--max-rate=5M	Limit transfer to 5 megabytes per second
-X stream	Stream WAL files during backup
-P	Show progress bar
-U bkpadmin	Backup user

---

🗜️ Server-Side Compression (PostgreSQL 15+)

PostgreSQL 15 introduced server-side compression using --compress=server-gzip:N, where N is the compression level (0 = none, 9 = max).

✅ Example:

pg_basebackup -h localhost -D "C:\prod_bkp_full_backup" -Ft --compress=server-gzip:9 -U bkpadmin -P

🔍 Explanation:

Option	Meaning
-Ft	Tar format
--compress=server-gzip:9	Use server-side gzip, level 9
-U bkpadmin	Replication user
-P	Show progress

⚠️ Requirements & Notes

• --compress=server-gzip requires PostgreSQL 15 or later

• For server-side compression, the server must support the method and client must connect with protocol version 3.1 or later

• On older versions, use -z (client-side gzip compression for tar format)

---

🆚 Quick Comparison

Compression Method	Command Flag	Compression Happens On	Version
Client-side gzip	-Ft -z or -Ft -Z 9	Client machine	All
Server-side gzip	--compress=server-gzip:9	Source (PostgreSQL server)	15+

---

📌 Notes

• The target directory must not exist or must be empty
• WAL streaming (-X stream) ensures you can restore from this backup immediately
• pg_basebackup must be allowed in pg_hba.conf (method = replication)
• backup_label : give you info like location , checkpoint , Start Time , Label this is very important for PITR
• backup_mainfest : This file used for incremental backup. 
• Requires:

wal_level = replica
max_wal_senders >= 1

---

📦 Restoring from a Base Backup

To use this for recovery or replication:

1. Extract the backup if it's tarred.
2. Place it into PGDATA directory.
3. Configure recovery.signal and restore_command if doing PITR.
4. Start PostgreSQL.