BACKUP keyword

Creates a backup for one, several, or all database tables.


Flow chart showing the syntax of the BACKUP keyword

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:


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

โ”œโ”€โ”€ 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'
โ”‚ย ย  ...


Single table
Multiple tables
BACKUP TABLE table1, table2, table3;
All tables