Database backups and restores: dumps that come back

Database backups fail differently from file backups. A half-copied world file is obviously broken - it will not load, and you know within a minute. A half-consistent database dump restores cleanly and is quietly wrong: the orders table has rows the order_items table does not, a foreign key points at nothing, and the application works until somebody opens the one page that touches both. The tooling to avoid this exists and costs you one flag.

The short version: use pg_dump for PostgreSQL and mongodump for MongoDB, never a file copy of a running data directory, write the dump somewhere off the machine, and restore it into a scratch database on a schedule so you know it works. Everything below is the detail, including the flags that matter, the things dumps leave behind, and what to do when losing an hour of writes is not acceptable.

Why a file copy of a running database is not a backup#

A database is a set of files that only make sense together at one instant. While it runs, pages are being written, the write-ahead log is ahead of the data files, and some of what the application thinks is committed is still in memory. tar walking that directory takes file A at 03:00:01 and file Z at 03:04:12, and the result is a set of files that never existed together.

For PostgreSQL the recovery from that is sometimes possible and always ugly. For MongoDB with the WiredTiger engine it is usually just a corrupt data directory. "I backed up the folder" is the single most common way a backup turns out not to be one.

There are three legitimate approaches, and they answer different questions:

For one or two databases behind a game server, a bot or a web app, logical dumps are the right answer and the rest of this post assumes them. Continuous archiving gets its own section at the end because the moment you cannot afford to lose an hour of writes, nothing else will do.

PostgreSQL: taking a dump you can trust#

pg_dump opens a single transaction at the repeatable-read isolation level and reads the whole database from that one snapshot. Everything in the file is from the same instant, no matter how long the dump takes or what the application does meanwhile. That is the property you are paying for.

$ export PGPASSWORD=... # better: use ~/.pgpass, see below$ pg_dump --host=db.example.net --port=5432 --username=app \    --format=custom --no-owner --no-privileges \    --file=/backups/app-$(date +%F).dump app

Two things pg_dump does not include, and both bite on restore day:

• Roles, passwords and tablespaces. These are cluster-wide, not per database. Dump them separately with pg_dumpall --globals-only > globals.sql and keep the file beside the dump. Without it, a restore onto a fresh server produces a database whose application user does not exist.
• The extensions themselves. The dump contains CREATE EXTENSION postgis; but not PostGIS. The target server must already have the extension available or the restore stops there.

Keep credentials out of the command line - anything typed there is visible in the process list to every other user on the machine. Use a password file instead:

db.example.net:5432:app:app_user:the-password

One version rule, worth memorising: run the pg_dump and pg_restore binaries from a version at least as new as the server you are talking to. An older pg_dump against a newer server refuses to run, and a dump produced by a newer major version will not always load into an older one. When you are moving between majors, dump with the new version's tools.

PostgreSQL: putting it back#

Restore into an empty database, never over a live one you have not first dumped.

$ createdb -h db.example.net -U app app_restore$ pg_restore --host=db.example.net --username=app --dbname=app_restore \    --no-owner --no-privileges --jobs=4 /backups/app-2026-09-21.dump

--jobs is the flag that turns an hour into fifteen minutes on anything sizeable; it loads tables and builds indexes in parallel and works with custom or directory archives. On a plan with one or two vCPU there is little to gain past -j 2, because you are limited by the same throttle everything else is.

Useful variations:

• Replace an existing database in place: add --clean --if-exists. Read that twice before running it against a production name. It drops objects before recreating them, and if the dump is short a table, that table is now gone.
• Restore one table: pg_restore -d app_restore -t orders app.dump. Indexes and constraints belonging to it are not automatically included, so check with pg_restore -l app.dump first - that prints the table of contents, which you can edit and feed back with -L for a precise selective restore.
• A plain SQL dump (-Fp, or anything produced by pg_dumpall) is not restored with pg_restore at all. It is a script: psql -d app_restore -f app.sql. Add -v ON_ERROR_STOP=1 or it will cheerfully run past the failure and tell you nothing.

Expect warnings about ownership and comments on extensions even in a clean restore; they are noise when you used --no-owner. What is not noise is a non-zero exit code, or any line containing ERROR:. Pipe the output to a file and read it.

MongoDB: mongodump and mongorestore#

The tools are not part of the server package any more - they ship as MongoDB Database Tools and are installed separately. Check with mongodump --version before you need them at two in the morning.

$ mongodump --uri="mongodb://app:PASSWORD@db.example.net:27017/?authSource=admin" \    --db=app --gzip --archive=/backups/app-$(date +%F).gz

--archive writes a single stream instead of a directory tree, which is what you want if the file is going to be uploaded or piped somewhere. --gzip compresses it. authSource is the database the user was created in, almost always admin, and getting it wrong produces an authentication failure that looks like a wrong password.

The consistency caveat is the important part. On a standalone mongod, mongodump reads collections one after another with no snapshot, so a dump of a database being written to is not guaranteed to be consistent across collections. On a replica set, --oplog records the operations that happened during the dump so that mongorestore --oplogReplay can roll them forward to a single point:

$ mongodump --uri="mongodb://...@host:27017/?authSource=admin" \    --oplog --gzip --archive=/backups/full-$(date +%F).gz

If you are on a standalone instance and the data matters, either dump during a quiet window or accept that cross-collection consistency is not guaranteed and design the application so that it does not matter.

Restoring:

$ mongorestore --uri="mongodb://app:PASSWORD@db.example.net:27017/?authSource=admin" \    --gzip --archive=/backups/app-2026-09-21.gz \    --nsFrom='app.*' --nsTo='app_restore.*'

That pair of --nsFrom and --nsTo flags is the MongoDB equivalent of restoring into a scratch database: the same data arrives under a different name, next to production, where you can count documents and point a copy of the application at it. Other flags worth knowing: --drop removes each collection before restoring it (destructive, same warning as --clean above), --nsInclude='app.orders' restores a single collection, and --restoreDbUsersAndRoles brings users and roles back when you are restoring a database that had them.

Verification for Mongo is a shell one-liner rather than a feeling:

db.getSiblingDB("app_restore").orders.countDocuments()db.getSiblingDB("app_restore").stats()

Compare against production. If the numbers are close but not equal, that is what an inconsistent dump looks like, and it is the reason to care about the replica set question above. Postgres or MongoDB covers the wider choice between the two if you are still deciding.

The database slot that came with your game or app plan#

A game plan includes one database slot and the app and web lines include two. These are created in the panel with a generated host, user and password, and administered through phpMyAdmin with a one-use sign-in token that expires after sixty seconds. For backups, use its Export tab: choose the whole database, pick SQL, and compress.

Two limits to know before you rely on it. Exporting through a browser is fine up to a few hundred megabytes and unpleasant beyond that, because the download is generated on the fly and a dropped connection means starting again. Importing has the harder limit: the upload size that the interface accepts. A dump that exports cleanly may refuse to import, and the usual fix is to split it or restore from a shell instead. phpMyAdmin import and export goes through the options in that dialog one at a time.

The dedicated database hosting line is different: PostgreSQL and MongoDB, a superuser password generated per server, reached directly on the plan's host and port. That means the command-line tools above work against it exactly as written, which is the main practical reason to move a growing database off a slot and onto its own plan.

Getting the dump off the machine#

A dump sitting on the same storage as the database it protects covers one failure: you dropping your own table. It covers nothing about the disk, the machine or the account. This is the same argument as in backups that actually restore, and it applies harder to databases because the dump is small and there is no excuse.

The pattern that works on a panel is to let the two systems stack:

1. A schedule writes the dump into the server's own folder, a few minutes before the backup task.
2. The backup task archives the folder, including the dump, and the archive is stored off the machine it protects.
3. Once a week you download one of those archives to somewhere unrelated to your host.

On a VDS or anywhere you have cron, the same thing with one line and a rotation:

0 4 * * * pg_dump -Fc -f /backups/app-$(date +\%F).dump app && \  find /backups -name 'app-*.dump' -mtime +14 -delete

Note the escaped % - unescaped, cron truncates the command at the first one, which is a classic reason a backup job silently produces nothing. Check that the file exists and has a plausible size the next morning, not in three months. The order and timing of these tasks on a panel is covered in scheduled tasks worth having.

When losing an hour is not acceptable#

Nightly dumps mean that a failure at 23:00 loses a day. If that is genuinely unacceptable - a shop, a ledger, anything where the rows are money - you need continuous archiving, and it changes what you have to run.

For PostgreSQL that is point-in-time recovery: set archive_mode = on and an archive_command that copies each completed write-ahead log segment somewhere safe, take a periodic base backup with pg_basebackup, and to recover, restore the base backup and let the server replay the logs to a recovery_target_time you choose. It gives you any second between the base backup and the last archived segment. It also requires a role with the REPLICATION attribute, somewhere to put the segments, and enough discipline to notice when archiving stops - a failing archive_command fills the data directory with unshipped logs until the disk is gone.

For MongoDB the equivalent is a replica set plus regular --oplog dumps, and the practical floor is how far back the oplog window reaches: if your oplog holds six hours of operations, you can recover to any point within six hours of your last dump and no further.

Both of these want a machine you control rather than a slot. That is the honest case for a VDS: not performance, but the ability to run an archiving command and store its output where you decide. Before you build it, be sure the requirement is real, because the operational cost is ongoing and a nightly dump that is actually tested beats a PITR setup nobody has exercised.

Proving the restore works#

The whole point of the sections above is a file. A file is not a backup until it has become a database again.

1. Create an empty database beside the real one, or a namespace with a different name.
2. Restore into it with the exact command you would use in an incident. Time it.
3. Count rows in the three tables that matter most and compare with production.
4. Point a copy of the application at it, if you can, and open the page that touches the most joins.
5. Drop it.

Do that once a quarter and you have a backup. Skip it and you have a file. The full drill, including how to measure the numbers it produces and what usually breaks, is in testing a restore before you need it. If your restore involves a schema that has moved on since the dump was taken, read migrations without downtime too, because restoring old data under new code is its own category of bad day.

FAQ#

Can I just copy the database files while it is running?

No. The files are only consistent together at one instant, and a copy taken over several minutes captures several instants. Use pg_dump or mongodump, or stop the database before copying the directory. A snapshot of a stopped database is a perfectly good backup; a copy of a running one is not.

How often should I dump?

Daily is the floor for anything with real users, and hourly is cheap for a database under a few gigabytes because a compressed dump of a small database takes seconds. Match the interval to how much work you are prepared to lose, then keep two weeks of them so that a problem introduced last Tuesday is still recoverable.

Does a panel backup of my server include the database?

Only if a dump of the database is inside the server's folder when the backup runs. The archive contains files; a database is a service. This is why the schedule takes the dump first and the backup second.

Will a dump from a newer version restore into an older server?

Often not. Dumps are forward compatible, not backward: restore into the same major version or a newer one, and always use the tools from the newer of the two ends. Plan version upgrades as dump, install, restore, verify, rather than assuming a file will load anywhere.

How do I restore a single table or collection?

For PostgreSQL, pg_restore -t tablename from a custom-format dump, after checking the contents with pg_restore -l. For MongoDB, mongorestore --nsInclude='db.collection'. In both cases restore into a scratch database first and copy the rows across deliberately, because a targeted restore does not know about the constraints and references pointing at that table from elsewhere.

What about the roles and passwords?

pg_dump does not include them; pg_dumpall --globals-only does, and it belongs in the same backup job. For MongoDB, users live in the admin database and come back with --restoreDbUsersAndRoles. A restore that forgets them produces a working database that your application cannot log in to. Database security checklist covers who should have which of those in the first place.