Backing up a database is not like backing up a folder of documents. A running database changes its files constantly, keeps part of the data in memory and writes to several places at once. Copying those files with an ordinary sync program while the server is working often produces an inconsistent copy which, when you need it, will not start or contains corrupted data. Specific tools and methods are required.
Two families of backup
The logical dump
A logical dump exports the contents of the database as SQL statements or in a format specific to the tool: table creation first, then data, then indexes and constraints. The best-known tools are pg_dump for PostgreSQL and mysqldump for MySQL and MariaDB.
pg_dump --format=custom --file=erp_2025-03-10.dump erp
These tools read data consistently: even if the database keeps receiving changes, the dump represents the state at a single instant. For MySQL with InnoDB tables, the --single-transaction option exists precisely to achieve this consistency without blocking writes.
Advantages of a logical dump:
- it is portable across database versions and operating systems;
- a single table can be restored on its own;
- it is readable and verifiable.
Disadvantages: on large databases it takes a long time to create and, above all, to restore, because indexes have to be rebuilt from scratch.
The physical backup
A physical backup copies the database files as they are on disk, using tools that know how to handle consistency: for PostgreSQL pg_basebackup, for MySQL solutions such as Percona XtraBackup or MySQL Enterprise Backup, or volume snapshots coordinated with the database.
Advantages: it is much faster to restore on large volumes and allows recovery to a precise moment. Disadvantages: it is tied to the database version and architecture, and is usually restored all or nothing.
Recovery to a precise moment
A nightly backup protects you up to the previous night. If at 4 pm someone accidentally drops a table, with only the dump you lose every change since midnight.
Databases record every change in a sequential log: in PostgreSQL it is the write-ahead log (WAL), in MySQL the binary log. By archiving these logs continuously alongside a base physical backup, the database can be rebuilt at any moment: you restore the base backup and replay the recorded changes up to, say, 3:59 pm, one minute before the mistake. This is called point-in-time recovery.
| Need | Suitable solution |
|---|---|
| Small database, losing a day is acceptable | Daily logical dump |
| Fast restore of a large database | Physical backup |
| Maximum loss of a few minutes | Physical backup plus continuous log archiving |
| Recovering a single table | Logical dump, also as a complement |
Two numbers to decide first
Before choosing tools, it is worth setting two targets together with the people who use the data:
- RPO (Recovery Point Objective): how much data you are willing to lose, measured in time. An hour? A day?
- RTO (Recovery Time Objective): how long the service can be down during a restore.
An RPO of 24 hours is achieved with a nightly dump. An RPO of five minutes requires continuous log archiving. A short RTO on a large database rules out a logical dump alone.
A replica is not a backup
Many systems have a replica server that receives every change from the primary in real time. It is excellent for service continuity if the primary fails, but it replicates mistakes too: a table dropped on the primary is dropped on the replica a fraction of a second later. A replica protects against hardware failure, not against human error or faulty programs.
Testing the restore
A backup that has never been restored is a hypothesis. The most common problems only surface at restore time: incomplete files, missing permissions, extensions not installed on the target server, forgotten encryption passwords, times far longer than expected.
A periodic test should:
- restore the backup to a separate server, never to production;
- measure the time taken and compare it with the RTO;
- check the row counts of the main tables and some known values;
- start the application against the restored database, if possible;
- write the procedure down step by step, so that anyone can repeat it.
Automatically restoring the latest dump every night to a test server and running a few check queries is an inexpensive way to know every morning that the backups work.
Where to keep them
Copies must live off the database server and, at least one, outside the same infrastructure. It is good practice to encrypt them, because a dump contains the entire body of data in a single file, and to protect the archive from deletion with immutable copies or separate credentials: if whoever attacks the server can also delete the backups, the backups are useless.
In short
- Copying the files of a running database does not guarantee a consistent copy.
- A logical dump is portable and flexible; a physical backup is faster to restore.
- Continuous log archiving lets you return to a precise moment.
- Decide RPO and RTO before choosing tools; a replica is no substitute for a backup.
- Restores must be tested regularly on a separate server, measuring how long they take.
