0Pricing
Linux Server Deployment & SSH Mastery · Lesson

Database Backup and Restore Strategies

Protect your data with reliable backups: dump and restore PostgreSQL and MySQL databases, automate backups with cron, and understand point-in-time recovery so you can survive disk failures and mistakes.

Database Backup and Restore Strategies is a free Linux Server Deployment & SSH Mastery lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Linux Server Deployment & SSH Mastery learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Backups Are Part of Security

You installed a database, locked down access, and secured connections. But a hardened server is still vulnerable to disk failure, accidental DROP TABLE, or ransomware.

Backups are the last line of defense. A database is only as safe as its most recent tested backup.

Logical vs Physical Backups

There are two broad approaches:

  • Logical — export SQL statements / data (e.g. pg_dump, mysqldump). Portable, human-readable, slower to restore.
  • Physical — copy the raw data files. Fast for huge databases, but tied to the server version.

Most setups start with logical backups.

Dumping a PostgreSQL Database

pg_dump exports a single database. The custom format (-Fc) is compressed and supports selective restore.

pg_dump -U appuser -Fc mydb > mydb_$(date +%F).dump

Restoring PostgreSQL

Restore a custom-format dump with pg_restore. For a plain SQL dump you pipe it into psql instead.

createdb -U appuser mydb_restored
pg_restore -U appuser -d mydb_restored mydb_2026-05-30.dump

Dumping and Restoring MySQL

MySQL/MariaDB use mysqldump to export and mysql to import. The dump is plain SQL by default.

mysqldump -u appuser -p mydb > mydb_$(date +%F).sql
mysql -u appuser -p mydb_restored < mydb_2026-05-30.sql

Backing Up All Databases

To capture an entire server, both engines offer a full-cluster dump. This is useful before major upgrades or migrations.

pg_dumpall -U postgres > all_$(date +%F).sql
mysqldump -u root -p --all-databases > all_$(date +%F).sql

Automating with Cron

A backup you have to remember to run will eventually be forgotten. Schedule it with cron. Put the dump command in a script and run it nightly.

# crontab -e  -> run at 02:30 every day
30 2 * * * /usr/local/bin/db_backup.sh >> /var/log/db_backup.log 2>&1

A Simple Backup Script

A good script timestamps files and prunes old ones so the disk does not fill up. This example keeps 14 days of dumps.

#!/bin/bash
DIR=/var/backups/db
pg_dump -U appuser -Fc mydb > $DIR/mydb_$(date +%F).dump
find $DIR -name 'mydb_*.dump' -mtime +14 -delete

Off-Site Storage

A backup on the same server dies with the server. Copy dumps somewhere else — object storage, another host, or both.

Use rsync or a cloud CLI as the final step of your backup script.

rsync -avz /var/backups/db/ backup@remote:/srv/db_backups/
# or: aws s3 cp $DIR/mydb_$(date +%F).dump s3://my-bucket/

Point-in-Time Recovery

Daily dumps lose up to a day of data. For zero/minimal loss, enable continuous archiving (WAL archiving in PostgreSQL, binary logs in MySQL). Combined with a base backup, you can restore to any moment.

This is the gold standard for production databases.

Best Practices

Make backups trustworthy:

  • Test restores regularly — an untested backup is a guess
  • Automate and monitor backup jobs for failures
  • Store copies off-site
  • Retain multiple generations, not just the latest

Quick Check

Test your backup knowledge.

Recap

You now have a database protection strategy:

  • Logical dumps with pg_dump/mysqldump and restores with pg_restore/mysql
  • Full-cluster dumps for migrations
  • Automated, self-pruning backups via cron
  • Off-site copies and point-in-time recovery for minimal data loss

Always test your restores — this completes secure database operation.

Frequently asked questions

Is the “Database Backup and Restore Strategies” lesson free?

Yes — the full text of “Database Backup and Restore Strategies” is free to read here on the web, and the Linux Server Deployment & SSH Mastery course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Linux Server Deployment & SSH Mastery course, upgrade to CoddyKit PRO.

What will I learn in “Database Backup and Restore Strategies”?

Protect your data with reliable backups: dump and restore PostgreSQL and MySQL databases, automate backups with cron, and understand point-in-time recovery so you can survive disk failures and mistak… You practise Linux Server Deployment & SSH Mastery with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Linux Server Deployment & SSH Mastery?

No prior experience is required. Linux Server Deployment & SSH Mastery on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Database Backup and Restore Strategies” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Linux Server Deployment & SSH Mastery lesson?

Yes. Every Linux Server Deployment & SSH Mastery lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Installing PostgreSQL/MySQL
  2. Database User and Access Control
  3. Secure Database Connections
  4. Database Backup and Restore Strategies
← Back to Linux Server Deployment & SSH Mastery