PostgreSQL — Backup
pg_dump
# Backup single database
pg_dump -U postgres mydb > backup.sql
# Backup with custom format
pg_dump -U postgres -Fc mydb > backup.dump
pg_dumpall
# Backup all databases
pg_dumpall -U postgres > all_backup.sql
Restore
# From SQL file
psql -U postgres mydb < backup.sql
# From custom format
pg_restore -U postgres -d mydb backup.dump
Automated Backup
#!/bin/bash
DATE=$(date +%Y%m%d)
pg_dump -U postgres mydb | gzip > /backups/mydb_$DATE.sql.gz
Backup Options
| Option | Description |
|---|---|
| -Fc | Custom format |
| -Fd | Directory format |
| -Ft | Tar format |
| -Fp | Plain SQL |
Point-in-Time Recovery
# Enable WAL archiving
# Restore base backup
# Apply WAL files up to desired time
Mini Practice
- Backup a database
- Restore from backup
- Create automated backup
- Test restore process
Up Next
Congratulations! You've completed the PostgreSQL course.
Related Topics
Frequently Asked Questions about Backup
What is Backup in PostgreSQL?
Backup is a fundamental concept in PostgreSQL. This lesson explains it step by step with clear examples, making it easy for beginners to understand.
How do I learn Backup?
Start by reading the explanation above, then try the code examples. Practice by modifying the examples and experimenting with different values. Hands-on practice is the best way to learn Backup.
Why is Backup important in PostgreSQL?
Backup is essential for PostgreSQL development. Understanding this concept will help you write better code and solve real-world problems more effectively.