Back Up MySQL and PostgreSQL - mysqldump and pg_dump Guide

Your database is usually the most valuable part of an app or website. Code lives in Git and can be redeployed in minutes; orders, customers, bookings and content exist only in the database. Managed hosting often includes automatic database backups, and you should use them, but there are good reasons to know how to make your own: before a risky migration, to copy production data to a test environment (carefully, with POPIA in mind), or to keep an independent copy with a different provider. This guide covers the standard tools for MySQL and PostgreSQL, how to restore, and how to make backups a routine instead of a scramble.

Logical backups in one paragraph

mysqldump and pg_dump make logical backups: they read the database and write out the statements or data needed to recreate it. They work over a normal database connection, so you can run them from your own computer or a server, without access to the database server's files. They're ideal for small and medium databases. Very large databases may need physical or incremental backup tools, but most business apps never reach that point.

MySQL and MariaDB: mysqldump

Make a backup

mysqldump --single-transaction --routines --triggers \
  -h db.example.co.za -u app_user -p app_db | gzip > app_db-$(date +%F).sql.gz

• --single-transaction takes a consistent snapshot of InnoDB tables without locking the app out while the backup runs.
• --routines and --triggers include stored procedures, functions and triggers.
• -p on its own prompts for the password, which keeps it out of your shell history.
• Piping through gzip makes the file much smaller.

Restore it

gunzip < app_db-2026-10-04.sql.gz | mysql -h db.example.co.za -u app_user -p app_db

Restoring into a live database overwrites the tables in the dump. When in doubt, restore into a new, empty database first, check it, and then switch your app over or copy what you need.

PostgreSQL: pg_dump

Make a backup

pg_dump -h db.example.co.za -U app_user -d app_db -Fc -f app_db-$(date +%F).dump

• -Fc writes PostgreSQL's custom format: compressed, and you can restore all or part of it with pg_restore.
• For a plain SQL file you can read and edit, leave out -Fc and write to a .sql file instead.

Restore it

pg_restore -h db.example.co.za -U app_user -d app_db_restore --no-owner app_db-2026-10-04.dump

--no-owner stops the restore from trying to assign objects to the original owner, which is useful when you restore into a database with a different user, as is common on managed hosting. Add --clean only when you really mean to drop and recreate existing objects in the target database. A plain .sql dump is restored with psql -d app_db_restore -f app_db.sql.

Match the versions

Use a pg_dump version that is the same as, or newer than, the database server. An older pg_dump may refuse to back up a newer server.

Keep passwords out of scripts

Scheduled backups can't type a password, but putting it in the command line exposes it in process lists and logs. Use the tools' own credential files instead, readable only by the user that runs the backup:

• MySQL: an option file passed with --defaults-extra-file=/path/to/backup.cnf, containing a [client] section with user and password.
• PostgreSQL: a ~/.pgpass file with one line per connection (host:port:database:user:password), with permissions set to 600.

Use a database user with only the permissions needed to read the data if your host lets you create one.

Schedule it and keep several copies

A backup you have to remember to make will be forgotten. Schedule it with cron on a server you control, or with your platform's scheduled tasks. Our guide to cron jobs in Node.js covers schedules and time zones.

Then decide how long to keep backups. A simple scheme keeps daily backups for a week, weekly backups for a month and monthly backups for a year. Delete older files automatically so the disk doesn't fill up.

Follow the 3-2-1 rule from our website backup strategy: three copies of your data, on two different kinds of storage, with one copy off-site. A backup stored only on the same server as the database won't help if that server fails.

Test your restores

An untested backup is a hope, not a backup. At least every few months:

1. Restore the latest backup into a new, empty database.
2. Check that the tables exist and the row counts of important tables look right.
3. Point a test copy of the app at it and click through key pages.
4. Write down how long it took. That's roughly how long a real recovery will take.

Backups and POPIA

Database backups contain the same personal information as the live database, so they need the same protection:

• Encrypt backups stored outside your hosting, and protect the keys.
• Limit who can download or read them.
• Delete them when your retention period ends. Keeping backups forever means keeping personal information forever.
• Don't restore production data into test environments that are less secure. Use anonymised or sample data where you can.

Our guide to POPIA for developers covers the wider obligations.

Frequently asked questions

Can I back up a database while the app is running?

Yes. pg_dump always works from a consistent snapshot, and mysqldump --single-transaction does the same for InnoDB tables, so the app keeps running.

How often should I back up my database?

As often as you can afford to lose data. If losing a day of orders would hurt, daily is the minimum; busy shops and booking systems may need more frequent backups.

Are my hosting provider's backups enough?

They're an important first layer, especially for quick restores. An extra copy somewhere else protects you from a problem with the provider or the account itself.

Why is my restored database missing some objects?

Usually because routines, triggers or extensions weren't included or the target user lacks permissions to create them. Check the dump options and the restore output for errors.

On NewHost, database plans back up each MySQL and PostgreSQL database automatically, daily or weekly as the plan says, and you can restore or download a backup yourself. Web hosting includes weekly backups of files, databases and mail, and you can copy backups to your own S3, FTP or SFTP storage for an off-site copy.