🐘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.

---

🔐 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

---

🧩 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

---

💾 File System Backups

Offline Backup (File System Level)

Requires PostgreSQL to be stopped.

Linux:

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

Windows:

cmd

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

---

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

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;

Output:

2. Stop PostgreSQL Cluster:

systemctl stop postgresql-15.service

3. Edit postgresql.conf:

vim /var/lib/pgsql/15/data/postgresql.conf

Add or modify:

wal_level = replica

archive_mode = on

archive_command = 'cp %p /var/lib/pgsql/version/archive/%f'

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!