Back up a database and restore it

By the end of this walkthrough you will have a fresh dump of a database, checked and saved on your own machine, and a tested way to load it back. The console's Restore is not available yet, so loading a dump is done by hand with the database's own tools; this page shows the approach and the checks that tell you it worked.

You need:

• a PostgreSQL, MySQL, MariaDB or MongoDB database that is running. Redis has no backups;

• a backup destination on your account, which exists once any database or volume was created with an Automatic backup (Backups explains);

• for the restore part, the client tools of the same database engine on your machine.

1. Find the schedule

   Open the Storage page and its Backups tab, or the database's own Storage section. The database's schedule is a card titled "Backup for" the project and the service.

   Read Protects on the card: it names the database the schedule dumps, such as "Database app". Make sure it is the database your application uses.

   • A MongoDB database's automatic schedule dumps only the test database.

   • A database renamed on the Connection card after it was created keeps a schedule that dumps the old name.

   In either case, or when the database has no schedule, add one that names the right database with Add backup schedule, as Backups describes.

2. Back up now

   Press the first of the four icon buttons on the card, Back up now. The console answers "Backup queued. Its status will update after verification." The schedule's own timer is not moved.

   

   If it is refused with "Wait for the service deployment to finish." or "This service has unfinished backup work.", wait for the deployment or the earlier backup to end and press it again.

3. Wait for Completed

   The new row in Backup history moves through Queued, Preparing, Starting backup and Verifying backup to Completed, and the page checks again every 15 seconds while it does. Once completed, the row reads "Artifact: retained" and the dump's size in MB.

   If it ends Failed or stays at Needs reconciliation, the sentence under the status says why; Backups explains each status.

4. Download it

   Press Download on the row. The file is named after the service and the time of the dump, and ends in .sql.gz for PostgreSQL, MySQL and MariaDB, or .bson.gz for MongoDB.

The download is a gzip file. What is inside it is the database's own dump format, produced by the database's dump tool rather than by Cloud, so look at it before you rely on it. The examples use a file named postgres-backup.sql.gz; use your own file's name.

1. Test that the gzip file is whole. The command prints nothing when it is:

   TerminalBashCopy

   gzip -t postgres-backup.sql.gz

2. Compare its size with Backup history. The size there is the file's size in bytes divided by 1,048,576, so a download that stopped early shows up as a smaller number:

   TerminalBashCopy

   ls -l postgres-backup.sql.gz

3. Unpack a copy, keeping the original:

   TerminalBashCopy

   gunzip -k postgres-backup.sql.gz

4. Look at the first bytes of the unpacked file to learn its format:

   TerminalBashCopy

   head -c 300 postgres-backup.sql

   • Readable SQL, such as comments and CREATE statements, is a plain SQL dump, which the engine's SQL client reads.

   • A PostgreSQL file that starts with PGDMP is PostgreSQL's own archive format, which pg_restore reads and psql does not.

   • A MongoDB .bson.gz file unpacks to binary data rather than text. MongoDB's documentation names the restore tool and options for it.

The format decides which tool and which options load it. Take them from that database engine's own documentation for the version you run, and try them on a copy first, as below.

Load the dump into a new, empty database first, check it there, and move your applications to it only when it holds what you expect. The database you took the dump from stays untouched until then.

1. Create a database to restore into

   In the same project, press Add a service and create a database of the same engine. Choose the same Version as the original, and the same Database name and Username, which its Connection card shows: a dump can refer to them. Press Create service, then Deploy.

2. Open it to your machine

   On the new database's Connection card, press Turn on on the Public address row and confirm. The database restarts with a public port. Copy the address from Public address.

   If the row reads Not available on this platform, the platform has no public address, and the console offers no other way to bring a file to a database: Terminal and SSH open a shell only, with no file transfer. Ask Support for help instead.

3. Load the dump

   On your machine, run the tool that matches the format you found, pointed at the public address: the user, the password, the host and the port are all in it. Use the new database's address, never the one your applications use, while you try this.

4. Check the result

   Connect to the new database with the same client and compare it with the original:

   • the tables or collections your application uses are all there;

   • the row or document counts of the ones that matter are close to the original's, allowing for writes made after the dump's Requested time;

   • the newest records are from shortly before that time.

5. Move your applications

   When the new database holds what you expect, copy its Internal address into each application's environment in place of the old one and save. Then press Turn off on its Public address row.

   Keep the old database until you are sure. When you delete it, take and download one last dump first: its Delete service confirmation says "no backup is taken".

To load a dump into the database it came from instead, the steps are the same against that database's public address, with one more before them: stop the applications that write to it, so nothing changes under the load.

• Keep Automatic backup on for every database, and add a schedule for each MongoDB database your application uses.

• Turn on Backup before deployment on the database's Storage section, so each deployment of the database is preceded by a dump.

• Choose Backup completed and Backup failed on a notification channel, so a failed dump reaches you.

• Download a dump now and then and run the checks above, so you know the files open before the day you need one.