Backup Commands (mysqldump, pg_dump)
Database backups are your safety net. Every production database needs regular backups. pg_dump (PostgreSQL) and mysqldump (MySQL) export the complete database to a SQL file that can be restored anywhere. Understanding backup options and restoration is essential for any developer.
Backup Options
- Full backup: all schema + all data (most common)
- Schema-only: structure without data (for migrations)
- Data-only: data without schema (for seeding)
- Custom format (PostgreSQL): compressed, parallelizable, selective restore
- Plaintext SQL format: human-readable, importable anywhere
- Continuous backup: WAL archiving (PostgreSQL) for point-in-time recovery
- Managed backups: AWS RDS automatic daily backups, Supabase PITR
Backup & Restore Commands
-- ── PostgreSQL Backup (pg_dump) ──────────────────
-- Full backup (plain SQL format)
pg_dump -U postgres -d ecommerce_db > backup_2024.sql
-- Full backup (custom compressed format - preferred for large DBs)
pg_dump -U postgres -d ecommerce_db -Fc -f backup_2024.dump
-- Schema only (no data)
pg_dump -U postgres -d ecommerce_db --schema-only > schema.sql
-- Data only (no schema)
pg_dump -U postgres -d ecommerce_db --data-only > data.sql
-- Backup specific tables only
pg_dump -U postgres -d ecommerce_db -t orders -t payments > orders_backup.sql
-- Restore from plain SQL
psql -U postgres -d ecommerce_db_new -f backup_2024.sql
-- Or: psql -U postgres < backup_2024.sql
-- Restore from custom format (supports parallel restore)
pg_restore -U postgres -d ecommerce_db_new -j 4 backup_2024.dump
-- -j 4 = 4 parallel restore workers
-- ── MySQL Backup (mysqldump) ────────────────────
-- Full backup
mysqldump -u root -p ecommerce_db > backup_2024.sql
-- With compression
mysqldump -u root -p ecommerce_db | gzip > backup_2024.sql.gz
-- Schema only
mysqldump -u root -p --no-data ecommerce_db > schema.sql
-- Data only
mysqldump -u root -p --no-create-info ecommerce_db > data.sql
-- Restore
mysql -u root -p ecommerce_db < backup_2024.sql
-- Compressed restore
gunzip < backup_2024.sql.gz | mysql -u root -p ecommerce_dbQuick Quiz
Tip
Tip
Practice Backup Commands mysqldump pgdump in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Common Mistake
Warning
A common mistake with Backup Commands mysqldump pgdump is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Backup Commands mysqldump pgdump from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Database backups are your safety net.
- Full backup: all schema + all data (most common)
- Schema-only: structure without data (for migrations)
- Data-only: data without schema (for seeding)