bramw_baserow/docs/decisions/002-baserow-data-backups.md

Baserow Data Backups

We need to be able to back-up all Baserow data for disaster recovery. There are a number of ways to do this detailed below, followed by proposal of which solution to pick.

With a back-up solution ideally we are looking for:

• A consistent back-up
• A process that works on a running, production version of Baserow without impacting user performance or preventing them from deleting or altering Baserow tables
• A solution that can handle hundreds of thousands of tables (this is common in a large Baserow database) or ideally an arbitrary number of tables

Option 1: pg_dump the entire database at once

pg_dump is the standard logical back-up tool for Postgres. It essentially generates a big SQL script which when run recreates the backed-up database.

To do this, pg_dump takes an ACCESS SHARE table level lock on all tables it wants to dump, for all tables at once in a single transaction. This is to guarantee it will get a constant snapshot of the data across the tables. Unfortunately this means that pg_dump will need to hold a lock per table all in the same transaction. Postgres controls how many locks a single transaction can hold at once with the (max_locks_per_transaction)[https://www.postgresql.org/docs/11/runtime-config-locks.html] configuration parameter, which defaults to 64. Baserow creates an actual Postgres database table per Baserow table resulting in a potentially huge number of tables in the database, which will continue to grow over time. This means if we want to pg_dump the entire database at once, we would need to set max_locks_per_transaction to at-least be the number of tables in the database. This is unfeasible as we would need to set this to a huge number, or be constantly updating it. Additionally, we would then need to configure other related config parameters to ensure Postgresql has enough shared memory to open that many locks at once 1 , 2 . This is undesirable as our back-up process would over time require an increasing amount of shared memory and potentially encounter unsupported behaviour with huge numbers of tables.

Another problem with pg_dump taking out an ACCESS SHARE lock on all tables during the back-up process is that during this time users cannot delete or alter tables and will receive database errors.

Option 2: run pg_dump multiple times on sub sets of the database

To avoid the max_locks_per_transaction issue encountered in Option 1 we could instead use the various pg_dump parameters to only dump a smaller part of the database. Then you could split the backup over multiple separate pg_dump commands/transactions. Also, this method still has the problem of preventing users altering fields or deleting tables whilst pg_dump for that particular table is running.

One way of doing this would be using the -t pg_dump parameter and dumping each table at once. However here we run into data consistency issues. What if we dump a through relation table, then a user deletes a row referenced in that table before we are able to dump the related table.

We could do something "smarter" by calculating all the connected tables and dumping them in one transaction. However that seems overly complex, and you still result in a database backup which isn't from a single snapshot in time. What if users are using tables in a "related" fashion without using actual FK's, after a restore they could see very odd results.

Finally, this method also means we could need to come up with our own custom script to find all the tables/workspaces of tables to back-up, loop over them running pg_dump many times, and then somehow combine the resulting SQL scripts and store them. It also means we can't use pg_dump's built in non SQL script output formats it provides, like the custom compressed format or the directory format, as we need to stitch together different pg_dump result files. Or if we did use the custom formats we would need another custom baserow restore script which knew how to loop over the many different back-up files comprising of a single backup. This custom script would also be a dangerous source of bugs, what happens if we accidentally miss out some key tables or database objects from our custom back-up logic.

Advantages

1. Can generate human-readable SQL
2. Uses minimal disk space when compressed
3. Can directly run pg_restore to apply a back-up to any existing postgres database, managed or not
4. If outputting as SQL then the back-up is super compatible as long as the SQL it uses is supported by future postgres versions
5. If outputting as compressed pg_dump custom file formats then still future versions of postgres are back-wards compatible. However older versions of postgres might not be supported.

Disadvantages

1. Not consistent across the entire DB, but consistent enough due to
2. If run on a live DB users will be prevented from altering / deleting tables for the portion of the back-up run over their table
3. Requires custom Baserow logic to split up the big pg_dump into many smaller ones
4. Higher chance of bugs in back-up due to having to do custom logic
5. Long running back-ups will not include any changes made during the back-up period

Option 3: fork the database and then run pg_dump (Chosen solution)

We could fork the database in digital ocean, hopefully then we could do Option 1 if we then manually raised max_locks_per_transaction for the fork (assuming this scales to hundreds of thousands of tables!), or instead do Option 2 as because it is a non-live fork and no longer be updated the back-up we generate will be consistent. In reality we cannot raise max_locks_per_transaction without making support ticket on a managed Digital Ocean postgres cluster so instead we had to go with this Option combined with Option 2.

Advantages

1. If digital ocean's forking works well they are doing the hard part for us
2. The same advantages of Option 1 and/or 2 but without the disadvantages of ending up with an inconsistent snapshot or having to raise config parameters to insane levels on a live production database, but instead do that on a temporary non public fork

Disadvantages

1. You have to pay for the fork whilst it is running
2. Still involves custom Baserow logic/scripting if Option 2 is used with this one.
3. Harder to automate, might require us to manually click fork in Digital ocean, ensure the new fork spins up, then trigger another script and then tear it manually down
4. Every single backup will take time to fork + time to pg_dump to do, which might be ages

Option 4: pg_basebackup (Potential Future solution)

We were not able to use this option as it is not possible to open a replication connection to a managed Digital Ocean postgres cluster, which is required to run pg_basebackup. In the future if Baserow switches to a different provider where this is possible and we want to start doing live back-ups directly on the production database then we should re-visit this option.

pg_basebackup is another built-in Postgres back-up tool which works at the file-system level and not logically like pg_dump. As a result it does not need to open and hold any locks over tables, nor does it affect clients who are connected and using the database. I believe this exact command is what Digital Ocean is doing when a managed cluster is forked. So you could see this solution as us manually forking the database. The advantage being is that we end up with the actual back-up files for the fork, which we could then archive. Which might be preferable compared to forking -> then running some sort of pg_dump -> then archiving as we skip the middle step and just get the archive files immediately.

Advantages

1. Single well-supported postgres builtin command, no custom Baserow logic needed
2. Generates tarred, compressed backup files automatically
3. No locks taken out on the database
4. Can be run on a live database with no impact on clients
5. If self-hosting our own database in the future then this is pre-requisite for point in time recovery and replicas
6. Back-up is completely consistent (if the transaction logs are included)
7. True clone of database, no chance of missing some schemas or objects etc
8. Back-up of database will include all changes made to the database whilst the back-up is running when running with -X stream! So say if the back-up takes an hour, you won't be missing that hours worth of data when it finishes.
9. Should be comparable in speed with digital oceans fork, and only when you actually need to restore do you need to do any potential pg_dumping of a copy at that point, instead of having to do that for every single backup compared to option 3.

Disadvantages

1. Only works if the major version of the target Postgres is the same and if both are running the same operating system
2. More low level and somewhat harder to understand than pg_dump
3. The backups a direct copy of the postgres file system, indexes and all, meaning more disk space will be used compared to pg_dump
4. Can't directly restore into an existing running postgres cluster without taking it down and replacing all of its main files
5. Restore steps are more complex and require filesystem access to the cluster
6. The back-up files are not human-readable SQL but instead raw postgres binary data
7. Can't directly restore from back-up straight into a Digital Ocean managed DB
   1. To do this you will have to launch you own postgres server where you do have file system access using the back-up
   2. Then once restored to your own DB you would need generate a normal compressed dump using pg_dump and then use pg_restore to load into the remote managed cluster.
   3. The same issues outlined above with pg_dump will still potentially stand with this method. Hopefully it is much easier to say, raise max_locks_per_transaction to the correct value in this temporary not live database to generate the pg_dump file successfully.

We also need to be slightly more careful with how exactly we run pg_basebackup as it is possible to create backups without transaction logs. If we did not have the transaction logs included (also known as WALs), it is almost certain that the backup is corrupted and un-usable. See https://www.cybertec-postgresql.com/en/pg_basebackup-creating-self-sufficient-backups/ for more details. As long as we properly understand what pg_basebackup is doing, test the resulting command line script works and test it can be used to recover Baserow, we are fine.

Chosen Solution

In the end due to limitations with the Digital Ocean managed cluster we went with Option 2 combined with Option 3. Two new scripts backup_baserow and restore_baserow were made which split the database into chunks to pg_dump separately and store in a tar. This is literally the only way we could back-up the DO cluster without making support tickets for them to manually change the config. Additionally, we need this script even if we went with Option 4 as once the basedump has been made we still need to turn it into something that can be pg_restored, and being able to do that without having to modify max_locks_per_transaction is a useful ability.