BACKUP keyword
Creates a backup for one, several, or all database tables.
The BACKUP statement is deprecated since QuestDB version 7.3.3 on all operating systems except Windows. We recommend the SNAPSHOT statements instead.
Syntax
Backup directory
Backing up a database or tables requires a backup directory which is set
using the cairo.sql.backup.root configuration key in a
server.conf file:
cairo.sql.backup.root=/Users/UserName/Desktop
The backup directory can be on a disk local to the server, a remote disk or
a remote filesystem. QuestDB will enforce that the backup is only written in a
location relative to the backup directory. This is a security feature to
disallow random file access by QuestDB.
The tables will be written in a directory with today's date with the default
format yyyy-MM-dd (e.g., 2020-04-20). A custom date format can be specified
using the cairo.sql.backup.dir.datetime.format
configuration key:
cairo.sql.backup.dir.datetime.format=yyyy-dd-MM
Given a BACKUP query run on 2021-02-25, the data and metadata files will be
written following the
db directory structure
├── 2021-02-25
│ ├── table1
│ │ ├── ...
│ ├── table2
│ │ ├── ...
│ ├── table3
│ ...
If a user performs several backups on the same date, each backup will be written a new directory. Subsequent backups on the same date will look as follows:
├── 2021-02-22 'first'
├── 2021-02-22.1 'second'
├── 2021-02-22.2 'third'
├── 2021-02-24 'first new date'
├── 2021-02-24.1 'first new date'
│ ...
Creating a full backup
When creating a backup in QuestDB, you can specify that the whole database or
specific tables should be backed up. This process will create a backup in the
backup directory.
A backup can then be triggered via SQL command and the backup is complete as soon as the SQL query has finished executing:
-- backup whole database
BACKUP DATABASE;
-- backup a specific table
BACKUP TABLE my_table;
Note that calling BACKUP TABLE <table_name> will only copy table data and
metadata to the destination folder. This form of backup will not copy entire
database configuration files required to perform a complete database restore.
Alternatively, the REST API can be used to execute the SQL for a database backup:
curl -G --data-urlencode "query=BACKUP DATABASE;" \
http://localhost:9000/exec
Restoring from a backup
In order to restore a backup, the QuestDB executable must be provided with the
directory location of an existing backup as the root directory. This can
done via the -d flag as -d /path/to/backup when starting up QuestDB.
java -p /path/to/questdb-<version>.jar \
-m io.questdb/io.questdb.ServerMain \
-d /path/to/backup_directory
Users who are starting QuestDB via systemd or the official AWS AMI may refer
to the
systemd file
for reference. To verify that database information has been successfully
imported, check logs via journalctl -u questdb which will contain a list
existing tables.
Docker instances may have a backup directory mounted to the root directory as follows:
docker run \
-p 9000:9000 -p 9009:9009 \
-p 8812:8812 -p 9003:9003 \
-v "/path/to/backup_directory:/root/.questdb/" questdb/questdb
Examples
BACKUP TABLE table1;
BACKUP TABLE table1, table2, table3;
BACKUP DATABASE;
The following example sets up a cronjob which triggers a daily backup via REST API:
# this will add crontab record that will run trigger at backup every-day at 01:00 AM
# copy paste this into server terminal
crontab -l | { cat; echo "0 1 * * * /usr/bin/curl --silent -G --data-urlencode 'query=BACKUP DATABASE;' http://localhost:9000/exec &>/dev/null"; } | crontab -
This example shows how to compress a backup using the tar utility. An archive
file questdb_backup.tar.gz will be created in the directory that the command
is run:
tar -zcvf questdb_backup.tar.gz /path/to/backup
The backup file can be expanded using the same utility:
tar -xf questdb_backup.tar.gz