How to back up QuestDB

Question

I'm running QuestDB 7.3.3 on Linux and I'd like to back up my database. BACKUP SQL statement seems deprecated, so I probably should use SNAPSHOT, right?

Answer 1

That's correct, you should use the SNAPSHOT statements. To make a backup, you need to put the database in snapshot mode:

SNAPSHOT PREPARE;

Next, you need to copy your database root directory somewhere on the disk:

$ cp -r ~/.questdb/* /home/my_user/backup

Here, I'm using the default root directory location (~/.questdb), but in your case the location may be different depending on your setup. Once the directory is copied, you should turn the database back into normal (non-snapshot) mode:

SNAPSHOT COMPLETE;

Remember that you shouldn't run DDL statements, e.g. CREATE TABLE, when the database is put in snapshot mode. It's fine though to ingest and query data for the existing tables while a snapshot is on-going.

Finally, when you want to restore from a backup, first you need to stop your database. Next, copy the backup dir over the existing root directory contents:

$ rm -rf ~/.questdb/*
$ cp -r /home/my_user/backup/* ~/.questdb

Before restarting your QuestDB server, you need to set a non-default snapshot instance id in the configuration, so that the server restores the database state from the snapshot files when it starts. To do that, add the following to the ~/.questdb/conf/server.conf file:

cairo.snapshot.instance.id=1

Here the exact value doesn't matter. The only requirement is that it's different from the default one (an empty string) or the one that was configured at the backup time.

Now, you can restart the database. On start, it should print something like the following into the logs when it recovers the snapshot:

2023-11-02T10:09:53.205456Z I i.q.c.DatabaseSnapshotAgentImpl snapshot recovery finished [metaFilesCount=1, txnFilesCount=1, cvFilesCount=1, walFilesCount=0]