Backup and Recovery¶
If you decide to run your own SeaTable Server, it is essential to ensure that you have a backup in place.
To understand what needs to be backed up, you must first understand the architecture of a SeaTable Server. If you have not yet read the relevant chapter, please do so now.
Backup container for single-node server
For single-node servers, we offer a user-friendly backup container that simplifies the process. Even with this tool, it is important to read this article to gain a thorough understanding of SeaTable backup procedures.
This article details what should and must be included in your backup, providing you with the knowledge to create your own backup procedure.
Data and folder structure¶
If you set up your SeaTable server according to this manual, your server should have a folder structure similar to the following.
/opt
├── caddy
│ ├── ...
├── containerd
│ ├── ...
├── mariadb
│ ├── ...
├── seatable-compose
│ ├── .env
│ ├── caddy.yml
│ ├── ...
└── seatable-server
├── nginx-logs
├── seatable
│ ├── ccnet
│ ├── conf
│ ├── db-data
│ ├── logs
│ ├── pids
│ ├── scripts
│ ├── seafile-data
│ ├── seahub-data
│ ├── seatable-license.txt
│ └── storage-data
└── ssl
There are generally four parts of data you have to save to keep your data secure.
- MariaDB database
- SeaTable base data (including Big Data)
- Configuration files with private keys
- Credentials and deployment settings
Let us clarify where you find which content and how we should backup them.
Custom changes require custom backup
It's impossible to describe all variations of possible custom configurations. Therefore, keep the following rule in mind: if you made custom changes, you must check if they need to be included in the backup. For example, if you added custom certificates in the Caddy folder, ensure these certificates are included in your backup.
What to backup?¶
Mariadb database¶
Base data is not stored in the MariaDB database
A common misunderstanding is the assumption that the content of SeaTable bases is stored in the MariaDB container. This is not the case. Refer to the chapter about SeaTable architecture for more details.
SeaTable creates three database in the mariadb
Docker container, storing these kind of data types:
- ccnet_db: User accounts, Groups and team assignment.
- seafile_db: Workspace definitions and versioning information for the files and images.
- dtable_db: Meta-information for the bases, statistic and log information and operation log
The mariadb container persists the database information in the directory /opt/mariadb
but instead of saving this directory you should create database dumps. Use the following commands to create such dump files.
# you can copy these commands to a shell script and execute this via a cronjob.
# Beware that this method will expose your mysql password in the process list
# and shell history of the docker host.
source /opt/seatable-compose/.env
mkdir -p /opt/seatable-backup && cd /opt/seatable-backup
docker exec -it mariadb mariadb-dump -u root -p${SEATABLE_MYSQL_ROOT_PASSWORD} --opt ccnet_db > ./ccnet_db.sql
docker exec -it mariadb mariadb-dump -u root -p${SEATABLE_MYSQL_ROOT_PASSWORD} --opt seafile_db > ./seafile_db.sql
docker exec -it mariadb mariadb-dump -u root -p${SEATABLE_MYSQL_ROOT_PASSWORD} --opt dtable_db > ./dtable_db.sql
Cronjob require other parameters
If you want to execute the docker ... mariadb-dump
command directly as a cronjob, your have to remove the parameters -it
. Otherwise you will only create an empty dump file.
Reduce database dump size
To reduce the size of your database dump, you can exclude certain database tables that typically grow large and are not always necessary. Examples include:
- operation_log
- delete_operation_log
- session_log
- activities
To exclude these tables, use the --ignore-table
parameter one or more times in the mariadb-dump
command.
Base content¶
Inside the /opt/seatable-server/seatable
directory, there are multiple folders containing user data, most importantly, the content of all SeaTable bases. Each base has a unique ID (base_uuid) used in the folder structure. The three folders that must be included in the backup are:
- storage-data: contains base base, base snapshots and big data dumps.
- seafile-data: contains uploaded files for file and image columns.
- seahub-data: contains data used by web front-end, such as avatars
Big Data is dumped automatically
The db-data
directory contains big data content in a sql-like database structure. It is not recommended to save this folder. Instead the big data content is dumped to the storage-data folder automatically every 24 hours by default. Therefore it is already part of your backup.
- To backup big data more often, change the dtable-db settings.
- To force the dump of big data you could execute the following command:
docker exec -it seatable-server /opt/seatable/scripts/seatable.sh backup-all
Configuration files¶
The conf
directory contains all the configuration files for your SeaTable Server. While these files are not mandatory for restoring your server, they simplify the process. Therefore, we recommend including the conf
folder in your backup.
Credentials & deployment settings¶
The /opt/seatable-compose/
directory contains your .env
file with secrets and your SeaTable license file. Including this directory in your backup is essential.
Manual backup process¶
MariaDB replaces old commands
In MariaDB 11, the commands mysql
and mysqldump
have been deprecated. Please use mariadb
or mariadb-dump
instead. Ensure your backup scripts create database dumps correctly.
Now that you understand the theory behind which files and directories need to be backed up, the following script could serve as a good starting point to consolidate all necessary files into the directory /opt/seatable-backup
. From there, you can choose the backup method that best suits your needs.
#!/bin/bash
# create the backup directory
mkdir -p /opt/seatable-backup/
# mariadb dumps
source /opt/seatable-compose/.env
cd /opt/seatable-backup
docker exec mariadb mariadb-dump -u root -p${SEATABLE_MYSQL_ROOT_PASSWORD} --opt ccnet_db > ./ccnet_db.sql
docker exec mariadb mariadb-dump -u root -p${SEATABLE_MYSQL_ROOT_PASSWORD} --opt seafile_db > ./seafile_db.sql
docker exec mariadb mariadb-dump -u root -p${SEATABLE_MYSQL_ROOT_PASSWORD} --opt dtable_db > ./dtable_db.sql
# force dump of big data to storage-data folder
docker exec -it seatable-server /opt/seatable/scripts/seatable.sh backup-all
# backup files (exclude unnecessary directories)
rsync -az --exclude 'ccnet' --exclude 'logs' --exclude 'db-data' --exclude 'pids' --exclude 'scripts' /opt/seatable-server/seatable /opt/seatable-backup
rsync -az /opt/seatable-compose /opt/seatable-backup/
Special cases and advanced topics¶
This section covers typical custom cases and provides additional advanced topics.
Backup Order: database first or data directory first?
For larger SeaTable instances, a complete backup may take some time. Therefore, you should consider whether to backup the MariaDB database first and then the data directory, or vice versa. In general, there is no issue with a time difference between the database and data backup. However, if you have to choose, we recommend backing up the database first, followed by the data folder. Here's why:
- If you back up the data directory first, there's a risk that new objects referenced in the MariaDB database may not yet exist in the file system. This could potentially lead to problems, even resulting in base corruption.
- If you back up the database first, you can be confident that all entries in the database reference valid objects.
S3 Object storage instead of local storage
If you're using S3 Object Storage instead of local storage, the folders storage-data
and seafile-data
aren't stored on the local file system. Instead, all data is stored in four S3 buckets. Three buckets fs
, commits
and blocks
contain assets from file/image column and storage
contains the base data.
A recommended backup method would be to implement an S3 sync with active versioning and lifecycle management.
Example of a S3 sync job with rclone
We have had positive experiences using these rclone
parameters to synchronize buckets containing millions of objects and terabytes of data. Be sure to customize the command according to your bucket names and rclone configuration.
rclone sync source:bucket-name target:bucket-name \
--config /opt/rclone.conf \
--stats 30m \
--stats-one-line \
--stats-log-level NOTICE \
--transfers=16 \
--checkers=16 \
--skip-links \
--s3-no-check-bucket \
--log-file="/opt/backup.log" \
--log-level=NOTICE
--size-only
URL Change during restore
If you restore your SeaTable Server with a new URL, don't forget to execute the command line tool to update the URL. Otherwise assets from the image and file columns will not be accessable.
Take care to take over the PLUGINS_REPO_ID
During the initial startup of the SeaTable container, SeaTable writes a variable called PLUGIN_REPO_ID
to dtable_web_settings.py
. This variable represents a hidden library that stores the installation files of the SeaTable plugins. If you delete (commenting is not sufficient) this value from the configuration file, SeaTable will generate a new value upon the next start.
When performing a migration or restore, you have two options: you can either retain the existing value, ensuring that this repository exists in dtable-storage, or you can delete the value from dtable_web_settings.py
and allow SeaTable to create a new one.
Restore¶
To restore your server, simply install a fresh new SeaTable Server and then import the mariadb dumps and copy all the user data.
Restore the databases¶
# replace <your_mysql_password> with your actual MySQL password (might be still present in /opt/seatable-compose/.env)
# beware that this method will expose your mysql password in the process list and shell history of the docker host
docker exec -i "mariadb" "/usr/bin/mariadb" -u"root" -p'<your_mysql_password>' ccnet_db < /opt/seatable-backup/ccnet_db.sql
docker exec -i "mariadb" "/usr/bin/mariadb" -u"root" -p'<your_mysql_password>' seafile_db < /opt/seatable-backup/seafile_db.sql
docker exec -i "mariadb" "/usr/bin/mariadb" -u"root" -p'<your_mysql_password>' dtable_db < /opt/seatable-backup/dtable_db.sql
Restore the SeaTable data and deployment settings¶
Simply copy all files back to their original position.
rsync -az /opt/seatable-backup/seatable /opt/seatable-server
rsync -az /opt/seatable-backup/seatable-compose /opt
Restore the dtable-db data¶
To restore the big data, you can execute the following command. This will recreate the sql-like database structure from the dumps inside the storage-data
folder.
docker exec -it seatable-server /opt/seatable/scripts/seatable.sh restore-all