BACKUP keyword
Creates a backup for one, several, or all database tables.
caution
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 directoryBacking 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:
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:
Given a BACKUP
query run on 2021-02-25
, the data and metadata files will be
written following the
db directory structure
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:
#
Creating a full backupWhen 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:
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:
#
Restoring from a backupIn 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.
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:
#
ExamplesThe following example sets up a cronjob which triggers a daily backup via REST API:
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:
The backup file can be expanded using the same utility: