Database Backups

Database backups are an integral part of any disaster recovery plan. Disasters come in many shapes and sizes. It could be as simple as accidentally deleting a table column, the database crashing, or even a natural calamity wiping out the underlying hardware a database is running on. The risks and impact brought by these scenarios can never be fully eliminated, but only minimized or even mitigated. Having database backups is a form of insurance policy. They are essentially snapshots of the database at various points in time. When disaster strikes, database backups allow the project to be brought back to any of these points in time, therefore averting the crisis.

Frequency of Backups#

When deciding how often a database should be backed up, the key business metric Recovery Point Objective (RPO) should be considered. RPO is the threshold for how much data, measured in time, a business could lose when disaster strikes. This amount is fully dependent on a business and its underlying requirements. A low RPO would mean that database backups would have to be taken at an increased cadence throughout the day. Each Supabase project has access to two forms of backups, Daily Backups and Point-in-Time Recovery (PITR). The agreed upon RPO would be a deciding factor in choosing which solution best fits a project.

Daily Backups and PITR are mutually exclusive. If your project opts into using PITR, Daily Backups will no longer be taken. They're also unnecessary, as PITR supports a superset of functionality, in terms of the granular recovery that can be performed.

Daily Backups#

All Pro and Enterprise tier Supabase projects are backed up automatically on a daily basis. In terms of Recovery Point Objective (RPO), Daily Backups would be suitable for projects willing to lose up to 24 hours worth of data if disaster hits at the most inopportune time. If a lower RPO is required, enabling Point-in-Time Recovery should be considered.

Backup Process #

The PostgreSQL utility pg_dumpall is used to perform daily backups. An SQL file is generated, zipped up, and sent to our storage servers for safe keeping.

You can access daily backups in the Scheduled backups settings in the Dashboard. Pro tier projects can access the last 7 days’ worth of daily backups while Enterprise tier projects can access up to 30 days’ worth of daily backups. Users can restore their project to any one of the backups or download them as a zipped SQL file.

Restoration Process #

When selecting a backup to restore to, select the closest available one made before the desired point in time to restore to. Earlier backups can always be chosen too but do consider the number of days’ worth of data that could be lost.

The Dashboard will then prompt for a confirmation before proceeding with the restoration. The project will be inaccessible following this. As such, do ensure to allot downtime beforehand. This is dependent on the size of the database. The larger it is, the longer the downtime will be. Once the confirmation has been given, the underlying SQL of the chosen backup is then run against the project. The PostgreSQL utility psql is used to facilitate the restoration. The Dashboard will display a notification once the restoration completes.

Point-in-Time Recovery#

Point-in-Time Recovery (PITR) allows a project to be backed up at much shorter intervals. This provides users an option to restore to any chosen point of up to seconds in granularity. Even with daily backups, a day’s worth of data could still be lost. With PITR, backups could be performed up to the point of disaster.

Backup Process #

As discussed here, PITR is made possible by a combination of taking physical backups of a project, as well as archiving Write Ahead Log (WAL) files. Physical backups provide a snapshot of the underlying directory of the database, while WAL files contain records of every change made in the database.

Supabase uses WAL-G, an open source archival and restoration tool, to handle both aspects of PITR. On a daily basis, a snapshot of the database is taken and sent to our storage servers. Throughout the day, as database transactions occur, WAL files are generated and uploaded.

By default, WAL files are backed up at two minute intervals. If these files cross a certain file size threshold, they are backed up immediately. As such, during periods of high amount of transactions, WAL file backups become more frequent. Conversely, when there is no activity in the database, WAL file backups are not made. Overall, this would mean that at the worst case scenario or disaster, the PITR achieves a Recovery Point Objective (RPO) of two minutes.

You can access PITR in the Point in Time settings in the Dashboard. The recovery period of a project is indicated by the earliest and latest points of recoveries displayed in one’s preferred timezone. If need be, the maximum amount of this recovery period can be modified accordingly.

Note that the latest restore point of the project could be significantly far from the current time. This occurs when there has not been any recent activity in the database, and therefore no WAL file backups have been made recently. This is perfectly fine as the state of the database at the latest point of recovery would still be indicative of the state of the database at the current time given that no transactions have been made in between.

Restoration Process #

A date and time picker will be provided upon pressing the Start a restore button. The process will only proceed if the selected date and time fall within the earliest and latest points of recoveries.

After locking in the desired point in time to recover to, The Dashboard will then prompt for a review and confirmation before proceeding with the restoration. The project will be inaccessible following this. As such, do ensure to allot for downtime beforehand. This is dependent on the size of the database. The larger it is, the longer the downtime will be. Once the confirmation has been given, the latest physical backup available is downloaded to the project and the database is partially restored. WAL files generated after this physical backup up to the specified point-in-time are then downloaded. The underlying records of transactions in these files are replayed against the database to complete the restoration. The Dashboard will display a notification once the restoration completes.