Why is Backup Important?

Before going into technical details, let's look back at a true story: A technology startup in Vietnam once lost all customer data because there was no proper backup. Consequences? It took them 6 months to restore customer trust and almost went bankrupt.

Backup is not just a technical job, but a strategy to protect your business.

Part 1: PostgreSQL Backup Methods

1.1. Logical Backup with pg_dump

This is the most common method, suitable for most cases.

Backup a single database

# Format SQL plain text (dễ đọc, dễ edit)
pg_dump -U postgres -d myapp_db > myapp_backup.sql
Format custom (nén tốt, restore linh hoạt)
pg_dump -U postgres -d myapp_db -F c -f myapp_backup.dump
Format directory (backup song song, nhanh nhất)
pg_dump -U postgres -d myapp_db -F d -j 4 -f myapp_backup_dir/

Compare formats:

Format	Advantages	Disadvantages	When to use
Plain SQL (-F p)	Easy to read and edit	No compression, slow restore	Small database, need to view content
Custom (-F c)	Good compression, selective restore	Cannot read directly	Most cases
Directory (-F d)	Parallel backup, fastest	Takes a lot of files	Large database (>100GB)
Tar (-F t)	Can be compressed	Do not restore in parallel	Rarely used

Selective backup

# Chỉ backup schema (cấu trúc tables, indexes, constraints)
pg_dump -U postgres -d myapp_db --schema-only > schema.sql
Chỉ backup data
pg_dump -U postgres -d myapp_db --data-only > data.sql
Backup một table cụ thể
pg_dump -U postgres -d myapp_db -t users -t orders > important_tables.sql
Backup tất cả trừ table logs (thường rất lớn)
pg_dump -U postgres -d myapp_db -T logs > backup_no_logs.sql
Backup theo schema
pg_dump -U postgres -d myapp_db -n public -n reporting > selected_schemas.sql

Practical example: Backup database production

#!/bin/bash

Script: backup_production.sh
DB_NAME="myapp_production"
DB_USER="postgres"
DB_HOST="localhost"
BACKUP_DIR="/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.dump"
Tạo thư mục nếu chưa có
mkdir -p $BACKUP_DIR
Backup với compression level 9
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME 

-F c -Z 9 

-f $BACKUP_FILE 

--verbose
Check kết quả
if [ $? -eq 0 ]; then
echo "✓ Backup thành công: $BACKUP_FILE"
SIZE=$(du -h $BACKUP_FILE | cut -f1)
echo "✓ Kích thước: $SIZE"
else
echo "✗ Backup thất bại!"
exit 1
fi
Xóa backup cũ hơn 7 ngày
find $BACKUP_DIR -name "${DB_NAME}_*.dump" -mtime +7 -delete
echo "✓ Đã xóa backup cũ hơn 7 ngày"

1.2. Backup the entire cluster with pg_dumpall

When you need to backup all databases, roles, and tablespaces:

# Backup toàn bộ
pg_dumpall -U postgres > all_databases.sql

Chỉ backup global objects (roles, tablespaces)
pg_dumpall -U postgres --globals-only > globals.sql
Chỉ backup roles
pg_dumpall -U postgres --roles-only > roles.sql

When to use pg_dumpall?

• When migrating the entire PostgreSQL server to a new machine
• When you need to backup both user permissions and roles
• When there are many databases related to each other

1.3. Physical Backup with Base Backup

This is a backup method at the file system level, suitable for very large databases.

# Bước 1: Tạo base backup
pg_basebackup -U postgres -D /backups/base -F tar -z -P

Bước 2: Configure WAL archiving (trong postgresql.conf)
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'
max_wal_senders = 3

Advantages:

• Very fast for large databases (TB-level)
• Point-in-Time Recovery (PITR) support
• Can be used for replication

Disadvantages:

• More complicated than logical backup
• Must backup the entire cluster, cannot select individual databases
• Requires the same PostgreSQL version when restoring

Part 2: Restore PostgreSQL

2.1. Restore from SQL file

# Tạo database mới (nếu cần)
createdb -U postgres myapp_db_restored

Restore từ SQL file
psql -U postgres -d myapp_db_restored < myapp_backup.sql
Restore với error handling
psql -U postgres -d myapp_db_restored 

-v ON_ERROR_STOP=1 

--echo-errors 

< myapp_backup.sql

2.2. Restore from Custom/Directory format

# Restore cơ bản
pg_restore -U postgres -d myapp_db myapp_backup.dump

Restore với clean (xóa objects cũ trước)
pg_restore -U postgres -d myapp_db -c myapp_backup.dump
Restore song song (nhanh hơn nhiều)
pg_restore -U postgres -d myapp_db -j 4 myapp_backup.dump
Restore chỉ một table
pg_restore -U postgres -d myapp_db -t users myapp_backup.dump
Restore vào database mới
pg_restore -U postgres -d postgres -C myapp_backup.dump

2.3. Restore complete script

#!/bin/bash

Script: restore_database.sh
BACKUP_FILE=$1
NEW_DB_NAME=$2
if [ -z "$BACKUP_FILE" ] || [ -z "$NEW_DB_NAME" ]; then
echo "Usage: $0 <backup_file> <new_db_name>"
exit 1
fi
echo "→ Kiểm tra file backup..."
if [ ! -f "$BACKUP_FILE" ]; then
echo "✗ File không tồn tại: $BACKUP_FILE"
exit 1
fi
echo "→ Tạo database mới: $NEW_DB_NAME"
createdb -U postgres $NEW_DB_NAME
if [ $? -ne 0 ]; then
echo "✗ Không thể tạo database"
exit 1
fi
echo "→ Đang restore..."
pg_restore -U postgres -d $NEW_DB_NAME -j 4 --verbose $BACKUP_FILE
if [ $? -eq 0 ]; then
echo "✓ Restore thành công!"
echo "→ Thông tin database:"
psql -U postgres -d $NEW_DB_NAME -c "\dt"
else
echo "✗ Restore thất bại!"
exit 1
fi

Part 3: Strategies and Best Practices

3.1. Backup Strategy 3-2-1

Here is the golden rule in backup:

• 3 copies of the data
• 2 different media types (e.g. disk and cloud)
• 1 Offsite version (in another physical location)

Implementation example:

#!/bin/bash

Script: backup_strategy_321.sh
DB_NAME="myapp_production"
LOCAL_BACKUP="/backups/local"
NAS_BACKUP="/mnt/nas/backups"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_NAME="${DB_NAME}_${DATE}.dump"
Backup 1: Local disk
echo "→ Creating local backup..."
pg_dump -U postgres -d $DB_NAME -F c -f "$LOCAL_BACKUP/$BACKUP_NAME"
Backup 2: NAS (different media)
echo "→ Copying to NAS..."
cp "$LOCAL_BACKUP/$BACKUP_NAME" "$NAS_BACKUP/"
Backup 3: Cloud (offsite) - AWS S3
echo "→ Uploading to S3..."
aws s3 cp "$LOCAL_BACKUP/$BACKUP_NAME" 

s3://mycompany-backups/postgresql/ 

--storage-class STANDARD_IA
echo "✓ 3-2-1 backup completed!"

3.2. Backup Schedule

Suggested backup schedules for different environments:

Development:

# Crontab: Backup hàng ngày lúc 2 giờ sáng
0 2 * * * /scripts/backup_dev.sh

Staging:

# Backup mỗi 6 giờ
0 */6 * * * /scripts/backup_staging.sh

Production:

# Full backup: Mỗi ngày lúc 2 giờ sáng
0 2 * * * /scripts/full_backup_prod.sh

Incremental backup: Mỗi giờ
0 * * * * /scripts/incremental_backup_prod.sh
WAL archiving: Continuous

3.3. Automation with systemd timer

# /etc/systemd/system/postgresql-backup.service
[Unit]
Description=PostgreSQL Backup Service
After=postgresql.service

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/backup_postgres.sh
StandardOutput=journal
StandardError=journal

# /etc/systemd/system/postgresql-backup.timer
[Unit]
Description=PostgreSQL Daily Backup Timer

[Timer]
OnCalendar=daily
OnCalendar=02:00
Persistent=true
[Install]
WantedBy=timers.target

Enable timer:

sudo systemctl enable postgresql-backup.timer
sudo systemctl start postgresql-backup.timer

3.4. Monitoring and Alerting

Script to check backup:

#!/bin/bash

Script: check_backup_health.sh
BACKUP_DIR="/backups/postgresql"
MAX_AGE_HOURS=24
SLACK_WEBHOOK="https://hooks.slack.com/services/YOUR/WEBHOOK/URL"
Tìm backup mới nhất
LATEST_BACKUP=$(find $BACKUP_DIR -name "*.dump" -type f -printf '%T@ %p\n' | sort -n | tail -1 | cut -f2- -d" ")
if [ -z "$LATEST_BACKUP" ]; then
MESSAGE="⚠️ CẢNH BÁO: Không tìm thấy backup nào!"
curl -X POST -H 'Content-type: application/json' 

--data "{"text":"$MESSAGE"}" 

$SLACK_WEBHOOK
exit 1
fi
Kiểm tra tuổi của backup
BACKUP_TIME=$(stat -c %Y "$LATEST_BACKUP")
CURRENT_TIME=$(date +%s)
AGE_HOURS=$(( ($CURRENT_TIME - $BACKUP_TIME) / 3600 ))
if [ $AGE_HOURS -gt $MAX_AGE_HOURS ]; then
MESSAGE="⚠️ CẢNH BÁO: Backup quá cũ! Age: ${AGE_HOURS}h\nFile: $LATEST_BACKUP"
curl -X POST -H 'Content-type: application/json' 

--data "{"text":"$MESSAGE"}" 

$SLACK_WEBHOOK
exit 1
fi
Kiểm tra kích thước backup (phải > 0)
SIZE=$(stat -c %s "$LATEST_BACKUP")
if [ $SIZE -eq 0 ]; then
MESSAGE="⚠️ CẢNH BÁO: Backup có kích thước 0 bytes!\nFile: $LATEST_BACKUP"
curl -X POST -H 'Content-type: application/json' 

--data "{"text":"$MESSAGE"}" 

$SLACK_WEBHOOK
exit 1
fi
echo "✓ Backup health check passed"
echo "  Latest: $LATEST_BACKUP"
echo "  Age: ${AGE_HOURS}h"
echo "  Size: $(du -h $LATEST_BACKUP | cut -f1)"

3.5. Testing Restore

IMPORTANT: Backup is of no value if you have never tested restore!

#!/bin/bash

Script: test_restore.sh
BACKUP_FILE=$1
TEST_DB="test_restore_$(date +%s)"
echo "→ Testing restore from: $BACKUP_FILE"
Tạo test database
createdb -U postgres $TEST_DB
Restore
pg_restore -U postgres -d $TEST_DB $BACKUP_FILE
Kiểm tra
TABLES=$(psql -U postgres -d $TEST_DB -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public'")
if [ $TABLES -gt 0 ]; then
echo "✓ Restore test PASSED: $TABLES tables restored"
# Verify data
psql -U postgres -d $TEST_DB -c "SELECT tablename, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10"

else
echo "✗ Restore test FAILED: No tables found"
fi
Cleanup
dropdb -U postgres $TEST_DB
echo "✓ Test completed and cleaned up"

Part 4: Troubleshooting

4.1. Common errors when Backup

Error: "permission denied"

# Solution: Chạy với user có quyền
sudo -u postgres pg_dump mydb > backup.sql

Hoặc grant quyền
GRANT CONNECT ON DATABASE mydb TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

Error: "could not connect to server"

# Kiểm tra PostgreSQL đang chạy
sudo systemctl status postgresql

Kiểm tra port
netstat -tlnp | grep 5432
Kiểm tra pg_hba.conf
sudo vim /etc/postgresql/14/main/pg_hba.conf

Error: Backup took too long

# Solution: Dùng format directory với parallel
pg_dump -F d -j 8 -f backup_dir/ mydb

Hoặc exclude tables lớn
pg_dump -T large_log_table mydb > backup.sql

4.2. Common errors when restoring

Error: "database already exists"

# Solution 1: Drop database cũ
dropdb mydb
createdb mydb
pg_restore -d mydb backup.dump

Solution 2: Dùng flag -c (clean)
pg_restore -c -d mydb backup.dump

Error: "role does not exist"

# Solution: Restore roles trước
pg_dumpall --roles-only > roles.sql
psql -f roles.sql

Sau đó restore data
pg_restore -d mydb backup.dump

Error: Out of disk space

# Check disk space trước khi restore
df -h

Ước tính kích thước cần thiết (thường 2-3x backup file)
du -h backup.dump

Part 5: Advanced Topics

5.1. Point-in-Time Recovery (PITR)

PITR allows you to restore the database to a specific time in the past.

Setup WAL archiving:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'
archive_timeout = 300  # Force WAL rotation every 5 minutes

Implement PITR:

# Bước 1: Stop PostgreSQL
sudo systemctl stop postgresql

Bước 2: Restore base backup
rm -rf /var/lib/postgresql/14/main/*
tar -xzf /backups/base.tar.gz -C /var/lib/postgresql/14/main/
Bước 3: Tạo recovery.conf (PostgreSQL < 12) hoặc recovery.signal (>= 12)
cat > /var/lib/postgresql/14/main/recovery.signal << EOF
restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
recovery_target_action = 'promote'
EOF
Bước 4: Start PostgreSQL
sudo systemctl start postgresql

5.2. Continuous Archiving with pgBackRest

pgBackRest is an enterprise-grade backup tool for PostgreSQL:

# Cài đặt
sudo apt-get install pgbackrest

Config /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
[mydb]
pg1-path=/var/lib/postgresql/14/main
Full backup
pgbackrest --stanza=mydb --type=full backup
Incremental backup
pgbackrest --stanza=mydb --type=incr backup
Restore
pgbackrest --stanza=mydb restore

5.3. Backup with Docker

# Backup PostgreSQL trong Docker
docker exec my_postgres_container pg_dump -U postgres mydb > backup.sql

Restore
docker exec -i my_postgres_container psql -U postgres mydb < backup.sql
Docker Compose với automated backups
version: '3.8'
services:
postgres:
image: postgres:14
volumes:
- postgres_data:/var/lib/postgresql/data
backup:
image: prodrigestivill/postgres-backup-local
environment:
POSTGRES_HOST: postgres
POSTGRES_DB: mydb
POSTGRES_USER: postgres
POSTGRES_PASSWORD: password
SCHEDULE: "@daily"
volumes:
- ./backups:/backups

Conclusion

Backup and restore is not just a simple technical task, but an important part of a business's data protection strategy. Some important points to remember:

1. Always test restore - Backup not tested = no backup
2. Automate everything - Don't rely on remembering to run manual backups
3. Follow the 3-2-1 rule - 3 copies, 2 media types, 1 offsite
4. Monitor and alert - Know immediately when there is a problem
5. Document everything - The new team must also know how to restore

Final checklist

• [ ] Backup script has been written and tested
• [ ] Crontab/systemd timer has been set up
• [ ] Monitoring and alerting have been configured
• [ ] Restore has been tested at least once
• [ ] Documentation has been written
• [ ] Team has been trained on the backup/restore process
• [ ] Offsite backup has been set up
• [ ] Retention policy has been clearly defined

---

Reference source:

• PostgreSQL Official Documentation - Backup and Restore
• pgBackRest Documentation
• PostgreSQL Backup Best Practices

This article was last updated: December 2024