A Practical DBA Guide with Real-World Validation
By Muthukumar | muthukumar.cloud
Introduction
Backing up a PostgreSQL database is easy.
Creating a backup that can actually restore your production environment during a disaster is what separates an experienced DBA from someone who only knows the commands.
Many engineers stop after running:
pg_dump
and assume their backup strategy is complete.
It isn’t.
A production-grade backup strategy includes:
- Pre-backup validation
- Consistent logical backup
- Backup verification
- Restore testing
- Data validation
- Performance validation
- Disaster Recovery readiness
In this article, I’ll walk through the complete production workflow that PostgreSQL DBAs commonly follow.
Lab Environment
|
Component |
Value |
|
OS |
Rocky Linux 9 |
|
PostgreSQL |
16 |
|
Database |
salesdb |
|
Backup Format |
Custom (-Fc) |
|
Database Size |
2–3 GB |
|
Restore Method |
Parallel Restore |
Production Workflow
Production Database
│
▼
Health Check
│
▼
pg_dump Backup
│
▼
Verify Backup
│
▼
Copy to DR Server
│
▼
Create Restore Database
│
▼
pg_restore
│
▼
Validate Data
│
▼
Performance Validation
Step 1 – Pre-Backup Health Check
Before taking any backup, verify the health of the database.
Check Database Size
SELECT pg_size_pretty(pg_database_size('salesdb'));
Knowing the database size helps estimate:
- Backup duration
- Storage requirements
- Restore duration
- Maintenance window
Check Active Sessions
SELECT
pid,
usename,
state,
query
FROM pg_stat_activity;
Long-running sessions may impact backup planning.
Check Long Transactions
SELECT
pid,
now()-xact_start,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL;
Large open transactions increase WAL generation and should be reviewed.
Step 2 – Take Production Backup
Use the Custom Format.
pg_dump \
-U postgres \
-d salesdb \
-F c \
-v \
-f /backup/salesdb_2026-08-06.dump
Why Custom Format?
- Compressed
- Faster backup
- Parallel restore support
- Selective restore
- Industry standard
Step 3 – Verify Backup
Never assume the backup is valid.
Check the backup file.
ls -lh /backup/
Example
salesdb_2026-08-06.dump
2.3 GB
Inspect backup contents.
pg_restore -l salesdb_2026-08-06.dump
Verify:
- Tables
- Indexes
- Constraints
- Functions
- Sequences
Step 4 – Copy Backup to Disaster Recovery Server
scp salesdb_2026-08-06.dump postgres@dr-server:/backup/
Never store your only backup on the same server hosting the database.
Step 5 – Create Restore Database
Never restore directly over production.
createdb salesdb_restore
This provides a safe environment for validation.
Step 6 – Restore
pg_restore \
-U postgres \
-d salesdb_restore \
-j 4 \
-v \
--no-owner \
--no-privileges \
salesdb_2026-08-06.dump
Why use:
–no-owner
Avoid ownership conflicts between environments.
–no-privileges
Avoid missing-role errors.
-j 4
Parallel restore significantly reduces restore time for larger databases.
Step 7 – Validate Restore
A restore without validation is incomplete.
Row Count Validation
SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM products;
Compare results with the source database.
Validate Database Size
SELECT pg_size_pretty(pg_database_size('salesdb_restore'));
Check Invalid Indexes
SELECT indexrelid::regclass
FROM pg_index
WHERE NOT indisvalid;
Expected result:
0 rows
Validate Constraints
SELECT conname
FROM pg_constraint;
Step 8 – Performance Validation
Run critical business queries.
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id=100;
Compare execution plans with the production database.
A restore should meet both data integrity and performance expectations.
Common Production Mistakes
❌ Backup stored on the same server
❌ Never testing restore
❌ Restoring directly into production
❌ No validation after restore
❌ Ignoring indexes and constraints
❌ Assuming a successful backup guarantees recovery
Production Best Practices
✔ Automate backups
✔ Encrypt backup files
✔ Compress backups
✔ Store backups offsite
✔ Test restores regularly
✔ Monitor backup jobs
✔ Validate row counts
✔ Validate indexes
✔ Maintain retention policies
✔ Document recovery procedures
Key Takeaways
A backup is only the first half of disaster recovery.
Recovery is successful only when you can:
- Restore the database
- Validate the data
- Validate the indexes
- Validate the constraints
- Validate application performance
Only then can you trust your backup.
Final Thought
“A backup is only as good as your last successful restore.”
This principle is followed by experienced PostgreSQL DBAs because the ultimate goal of a backup is recovery—not just creating a .dump file.
Comments
Post a Comment