🐘1: PostgreSQL Backup and Restore Guide (With PostgreSQL 17 Features)

This guide covers essential PostgreSQL backup strategies, including logical backups using pg_dump, full cluster file system backups, and advanced features like filtered backups and Point-in-Time Recovery (PITR) introduced in PostgreSQL 17.

---

Backup : Backup is copy of data take from database and can be used to reconstruct in case of failure. Backup can be divided into logical & physical backup (Like Binary file) . 

Logical Backup: are simple & textual representation of data in database.  It supports various output like plain text tar. Small database are perfect candidates for logical backups. 

PG_DUMP & PG_DUPMALL utilities are used to perform logical dumps (less then 100 GB). 

🧩 Logical Backups Using pg_dump

Backup a Single Database

pg_dump micro > singledb.sql

---

Backup Using Custom Format

• -Fc = Custom Format (more flexible for restores)

pg_dump -Fc micro -U backup > customdb.tar

---

Backup All Databases

pg_dumpall -U backup > alldb.sql

---

Backup a Single Table

pg_dump -t company -d testdb -U postgres > singletbl.sql

---

🔍 Filtered Backup (PostgreSQL 17 Feature)

1. Create a filter.txt file:

include table mytable*

exclude table mytables*

2. Run the filtered dump:

pg_dump --filter=filter.txt -U postgres dbname > filterdb.sql

---

🗜️ Compress and Split Dumps

Compress Dump File

pg_dump -U postgres -d micro | gzip > /var/lib/pgsql/micro.gz

---

Split Large Dumps into Parts

pg_dump -U postgres -d testdb | split -b 1k - backup.dump.part_

---

Restore From Split Dumps

cat backup.dump* | psql -U postgres -d testdb

---

Physical  Backup : 

• Physical files used in storing & recovering of database , such as data files , wall file , archive files. 
• The database server must be shutdown before restore data & Partial restore or single table restore not possible . 
• This Approach is suitable only for complete backup or complete restore of entire database cluster & Consistent snapshot of data directory is consider a better approach then file system level.  

💾 File System Backups

Offline Backup (File System Level) : Requires PostgreSQL to be stopped.

Linux: tar -cvzf backup.tar /usr/local/pgsql/data

Windows: tar -cvzf full_backup.tar "C:\Program Files\PostgreSQL\17\data"

---

🔄 Continuous Archiving & Point-In-Time Recovery (PITR) :

• Continuous archiving is the process of archiving Write Ahead Log (WAL) files.
• Point in Time Recovery refers to PostgreSQL ability to start from the restore from restore of a full backup and apply archived WAL files up to a specified timestamp.
• Steps to setup up Continuous Archiving and PITR 
• Setting up WAL Archiving .
• Making a Base backup using the Low Level API
• Making a Base Backup.
• Making an Incremental backup ( New Feature PostgreSQL- 17)
• Recovering Using a Continuous Archive backup
• This backup Strategy which is suitable for your production environment. 

What is it?

• WAL Archiving: Continuous archiving of Write-Ahead Logs.
• PITR: Restore from a base backup + replay WAL logs to reach a specific point in time.

---

Step-by-Step: Setup WAL Archiving

1. Check Current Archive Mode: SHOW archive_mode;

2. Stop PostgreSQL Cluster: systemctl stop postgresql-15.service

3. Edit postgresql.conf: vim /var/lib/pgsql/15/data/postgresql.conf

Add or modify: Parameter List for setting up archiving on Linux:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/version/archive/%f'

Add or modify: Parameter List for setting up archiving on Window :

wal_level = replica
archive_mode = on
archive_command = 'copy " %p" "C:\\server\archive\\"%f" ' 

WAL LEVEL : There is three type minimal, replica, logical  
minimal - the information written in wal file will be very minimal and that is enough to recover if there is cash.
Replica - PostgreSQL will write more information than minimal. Good info we can setup streaming replication
Logical - I want to set up kind of replication if i want to set logical replication, i have to keep my wal level as replica as for archiving.

Note : This directory keep in different server or different drive

4. Start PostgreSQL:

systemctl start postgresql-15.service

---

Switch WAL File Manually

SELECT pg_switch_wal();

---

Example Directory Structure

[root@pgbouncer backups]# ls

000000010000000000000010

These are your archived WAL files, ready for recovery if needed.

---

✅ Summary

• Use pg_dump for logical backups of databases and tables.
• PostgreSQL 17 adds powerful filtering to pg_dump.
• pg_dumpall backs up entire clusters (roles, settings, and all databases).
• Enable archive_mode and archive_command for PITR readiness.
• Always verify your backups and test your restore process!

Click here for learn Online backup Part 2- PostgreSQL Online Backup Using Low-Level API

🔐 Step 1: Enable Password Authentication

To allow password-based access instead of peer authentication:

1. Edit pg_hba.conf:

sudo vi /etc/postgresql/<version>/main/pg_hba.conf  Replace:

local   all             postgres                                peer

local   all             postgres                                md5

2. Restart PostgreSQL:

sudo systemctl restart postgresql

---

🔑 Step 2: Configure .pgpass File

1. Login as postgres:

sudo -i -u postgres

2. Edit .pgpass:

sudo vi /root/.pgpass

Add this line (adjust as needed):

localhost:5432:yourdbname:postgres:yourpassword

3. Set correct permissions:

chmod 600 /root/.pgpass

4. Test login:

psql -U postgres -W

---