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.

Types of backups#

Database backups can be categorized into two types: logical and physical. You can learn more about them here.

As a general rule of thumb, projects will either have logical or physical backups based on plan, database size, and add-ons:

You can confirm your project's backup type by navigating to Database Backups > Scheduled backups and if you can download a backup then it is logical, otherwise it is physical.

However, if your project has the Point-in-Time Recovery (PITR) add-on then the backups are physical and you can view them in Database Backups > Point in time.

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#

All Pro, Team and Enterprise Plan 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 Plan projects can access the last 7 days' worth of daily backups. Team Plan projects can access the last 14 days' worth of daily backups, while Enterprise Plan projects can access up to 30 days' worth of daily backups. Users can restore their project to any one of the backups. If you wish to generate a logical backup on your own, you can do so through the Supabase CLI.

Backup process for large databases#

Databases larger than 15GB¹, if they're on a recent build² of the Supabase platform, get automatically transitioned³ to use daily physical backups. Physical backups are a more performant backup mechanism that lowers the overhead and impact on the database being backed up, and also avoids holding locks on objects in your database for a long period of time. While restores are unaffected, the backups created using this method cannot be downloaded from the Backups section of the dashboard.

This class of physical backups only allows for recovery to a fixed time each day, similar to daily backups. You can upgrade to PITR for access to more granular recovery options.

Once a database is transitioned to using physical backups, it continues to use physical backups, even if the database size falls back below the threshold for the transition.

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.

If your project is using subscriptions or replication slots, you will need to drop them prior to the restoration, and re-create them afterwards. The slot used by Realtime is exempted from this, and will be handled automatically.

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 your 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.

Troubleshooting#

Logical backups#

search_path issues#

During the pg_restore process, the search_path is set to an empty string for predictability, and security. Using unqualified references to functions or relations can cause restorations using logical backups to fail, as the database will not be able to locate the function or relation being referenced. This can happen even if the database functions without issues during normal operations, as the search_path is usually set to include several schemas during normal operations. Therefore, you should always use schema-qualified names within your SQL code.

You can refer to an example PR on how to update SQL code to use schema-qualified names.

Invalid check constraints#

Postgres requires that check constraints be:

1. immutable
2. not reference table data other than the new or updated row being checked

Violating these requirements can result in numerous failure scenarios, including during logical restorations.

Common examples of check constraints that can result in such failures are:

• validating against the current time, e.g. that the row being inserted references a future event
• validating the contents of a row against the contents of another table