Backup and restore

Querybara makes logical backups: the structure and the data, written as SQL or into a Querybara archive (.qbak) with one file per object. A restore can take everything or only the objects you pick, into the same database or another one. Backups and restores run as jobs.

Backup and restore

1. 1A backup reads PostgreSQL, MySQL or MariaDB in one consistent snapshot.
2. 2Each object becomes its own entry in the .qbak archive: its definition, then its rows.
3. 3A manifest lists the entries, so a restore can pick objects without reading the rest.
4. 4With a passphrase, every entry is encrypted with AES-256-GCM under a key derived with scrypt.
5. 5Restore lists what it would drop before it asks you to go ahead.
6. 6Then it writes only the objects you selected, into any database.

What a backup holds

Engine	Contents
PostgreSQL, MySQL, MariaDB	Tables, views and other objects with their data, read in one consistent snapshot (on MySQL and MariaDB, InnoDB tables)
MongoDB	The collections of a database, with their options and indexes
Redis	The keys matching a pattern, with their time to live

Back up a database

Back up to an encrypted Querybara archive with no native tools needed.

1. Right-click a connection, a database or a schema in the sidebar and choose Back up… (Back up keys… on Redis).
2. Objects: everything starts ticked under Everything. Untick objects you do not need, or untick rows (documents on MongoDB) to keep an object’s structure without its data. What the chosen objects need comes along: the tables of a view, a column’s type, a trigger’s function.
3. Options: pick the method, format and what to include (see below), and choose Choose file….
4. Choose Back up and follow the progress.

To back up regularly, choose Schedule… instead of Back up on the last step. No file is chosen in the dialog then: the schedule writes a new file each run, named from a template, and can keep only the newest ones. An encrypted backup’s passphrase is kept in your computer’s secure storage, never with the schedule.

Backup options

Option	What it does
Method	Querybara (built in, no tools needed), or Native tool (pg_dump, mysqldump or mariadb-dump) when it is installed
Format	Querybara archive (.qbak), SQL script (.sql) or Compressed SQL script (.sql.gz); with pg_dump, also pg_dump custom format (.dump). MongoDB and Redis back up to .qbak
Include	Structure (DDL), Data, Privileges (GRANT), Owners and definers
Consistent snapshot	Reads every table in one snapshot transaction
Deferrable snapshot	PostgreSQL: waits for a snapshot free of serialization anomalies before reading
Compress each object (gzip)	.qbak archives
Encrypt with a passphrase (AES-256-GCM)	.qbak archives; the passphrase needs at least 8 characters
Documents as	MongoDB: BSON or Extended JSON (canonical)
Keys matching	Redis: the key pattern

Keep the passphrase

Querybara does not keep the passphrase. Without it, an encrypted backup cannot be restored. Any change to an encrypted archive is detected when it is read.

The archive layout is documented in The .qbak format.

Redis server snapshot

On Redis, the backup options also offer Server snapshot (BGSAVE)…. It asks the server to save its whole dataset to its own RDB file, on the server rather than on your computer, after a confirmation.

Restore a backup

Before a restore runs, the review lists everything it would drop or overwrite.

1. Right-click a connection or a database and choose Restore… (Restore keys… on Redis).
2. File: choose Choose backup file…. An encrypted archive asks for the Backup passphrase; choose Unlock. The step shows the format, size, where the backup came from, when it was made and how many objects it holds.
3. Objects (archives): keep Everything, or tick only the objects to restore. What they need comes along; a foreign key comes only when both its tables do. For SQL archives, choose Structure and Data.
4. Target: on PostgreSQL, MySQL and MariaDB, pick An existing database or A new database under Restore into and enter its name; on MongoDB enter the database, and on Redis the database number. For a SQL script, choose whether Querybara (statement by statement) or the native tool runs it. Under When a statement fails, pick Stop (and roll back what can be) or Log it and continue. On Redis, choose Replace keys that exist (REPLACE) and Keep the original expiry times (keys already expired are skipped).
5. Review: read what will be restored, what comes along because other objects need it, and what already exists and will be dropped or overwritten. If anything is listed, tick I understand: restore over these objects.
6. Choose Restore and follow the progress.

Restoring over existing objects

Restoring over objects that exist drops and recreates them (on Redis, REPLACE overwrites keys). The review lists every one before you can start, every time. A read-only connection cannot restore, and a production or confirm-writes connection asks once more before the restore starts. PostgreSQL restores run in one transaction; MySQL and MariaDB commit table by table.

From the command line

querybara backup and querybara restore do the same from a terminal. The passphrase is never read from the command line: set QUERYBARA_BACKUP_PASSPHRASE (or name another variable with --passphrase-env), or type it when asked.

QUERYBARA_BACKUP_PASSPHRASE=… querybara backup prod --out shop.qbak --encrypt
querybara backup prod --out shop.sql.gz --schema shop --exclude-data shop.audit_log
querybara backup "redis://localhost/2" --out carts.qbak --pattern "cart:*"
querybara restore dev shop.qbak --list
querybara restore dev shop.qbak --select shop.orders --dry-run
querybara restore dev shop.qbak --database shop_copy --create-database

--dry-run shows what would be restored and dropped without changing anything; --yes restores over existing objects without asking.

Related

• The .qbak format
• Schedules
• Jobs
• Export