Database Backups - Using Supabase
Every single Pro and Enterprise-grade Supabase project has a daily automated backup. Daily Backups would be the most dependable technique to guarantee that your data is secure and secured in terms of Recovery Point Objective (RPO). With regular backups, you can be confident that your data is secure and that, in the case of a system crash or data loss, you can restore it. To make sure that all of your data is adequately backed up and safeguarded, you must have a solid backup system in place. Even if something were to happen to your physical device, knowing that your data is secure and secured thanks to a backup mechanism in place can ease your mind.
Enterprise-grade
Database Backups
Backups of databases are a crucial component of any disaster recovery strategy. Disasters can take many different forms. It may be as easy as removing a table field by mistake, the database collapsing, or even a natural disaster destroying the hardware a database is running on. These situations' hazards and effects can never be completely removed; they can only be reduced or even neutralized. An insurance policy having database backups. In essence, they are snapshots of the database taken at different times. Database backups enable the project to be restored to any of these points in time in the event of a disaster, preventing a crisis.
The Recovery Point Objective (RPO), a crucial business statistic, should be taken into account when determining how frequently a database should be backed up. RPO is the limit for how much data, measured in time, a company might lose in the event of a disaster. This sum entirely depends on the business and it's underlying needs. Database backups would need to be performed more often throughout the day if the RPO was low. Daily Backups and Point-in-Time Recovery are two types of backups that are available to each Supabase project. Which option fits a project the best would depend on the agreed-upon RPO.
Since the database only contains metadata about these items, objects stored via the Storage API are not included in database backups. Objects that have been removed after the last backup are not recovered when an old backup is restored. This is an important concept to understand when it comes to data backup and recovery, as it means that any data that has been deleted since the last backup will not be recoverable. This is why it is important to make sure that backups are done regularly.
Restoration Process
Choose the most recent backup that was created before the desired point in time to restore to when choosing a backup to restore to. Although earlier backups are always an option, keep in mind how much data might be lost in terms of days.
Before starting the repair, the Dashboard will then request confirmation. After this, the project won't be available. As a result, make sure to schedule downtime in advance. The size of the database affects this. The downtime will be longer the bigger it gets. The chosen backup's underlying SQL is then performed against the project when confirmation has been received.
The PostgreSQL utility psql is used to facilitate the restoration. The Dashboard will display a notification once the restoration is completed.
Creating a local backup of a Supabase database
The first thing you'll need to find to back up your Supabase database is your connection URI. You can find this from the Supabase admin interface by
'Selecting your database > Going to Settings > Selecting Database from the sidebar > Scrolling all the way to the bottom'
Supabase is an incredible Firebase alternative as well as our favorite managed Postgres provider at the moment.
It's incredibly easy to use, but without having access to the server the database is hosted on some people might find creating manual backups confusing. So here's how you can create manual backups of your Supabase database.
How do I create a backup of a Supabase Database?
Supabase utilizes PostgreSQL instances for its databases, as was already mentioned, and luckily Postgres also provides some really helpful CLI tools. One of these, pg dump, which comes pre-installed with postgres, is the one we'll utilize today. Every Postgres user should have access to pg dump since it makes backing up your whole database or just particular tables quick and simple. It may make it easier for you to keep track of your information and is a terrific approach to making sure that all of your data is safe and secure. Having a safe database can help to protect your data from unscrupulous actors. Database security is a crucial component of every organization.
Installing PostgreSQL on your machine is a must.
On Ubuntu you can use this command:
sudo apt-get install postgresql-12
On Windows, you'll have to run the Postgres Windows Installer. The installer won't add all the Postgres commands to your $PATH, which unfortunately means you'll have to either add them yourself or go find them in their respective folders.
for example, pg_dump was located at C:\Program Files\PostgreSQL\13\bin\pg_dump.
How to use the pg_dump command to backup Supabase
Your connection URI is the first item you'll need to locate in order to backup your Supabase database. By choosing your database, going to Settings, choosing Database from the sidebar, and then scrolling all the way to the bottom of the Supabase admin interface, you may obtain this information.
Your string will look something like:
Copy your string and then open your terminal.
The aforementioned command instructs pg dump to establish a connection to your remote database using the given URI and dump the contents into your current folder under the name database-dump.sql.
On Windows, if pg_dump is not part of your PATH, you'll have to run this command after moving to the correct folder:
Restoring a local backup to a Supabase database
The method is quite similar if your database experiences a problem and you need to restore from a backup.
Simply go to the folder containing your backup, then execute:
If you installed Postgres using the preceding instructions, the psql command is already installed on your computer.
Supabase Has Backups. Why Use Ottomatik?
Supabase does have backups. But take control of your data by suiting your recovery plans to your needs. Superbase Pro and Enterprise customers are backed up daily - meaning you should be prepared to lose 24 hours of data if a disaster were to strike. Don’t be caught unprepared. It is essential to have a plan in place to protect your data in the event of a disaster. This plan should include regular backups of all your data, stored in a secure offsite location, and a reliable disaster recovery system to quickly restore your data is essential for any business. Having a secure offsite location for your data is paramount for protecting it from potential threats.
Also, if you self-host the supabase, no backups are included. A backup regiment is essential for data safety and recovery.
Note: Database backups do not include objects stored via the Storage API, as the database only includes metadata about these objects. Restoring an old backup does not restore objects that have been deleted since then.
Our Solution
Ottomatik backs up Supabase databases by using our Postgres Serverless Backup solution. This solution lets you run regular backups of your data (hourly, daily, weekly, monthly) without the need to set up a server.
To get started you just need your Supabase connection data. Create a database credential in your Ottomatik account and then create a job or parachute using our “remote” servers.
Conclusion
While Supabase itself offers a pretty solid backup solution by itself, Ottomatik offers more powerful options for your backups. Setting up backups with Ottomatik is way more hassle-free and easier for your peace of mind.
