querybara backup

querybara backup writes a logical backup of a database to a file. By default the file is a Querybara archive (.qbak): one entry per object and a manifest, so a restore can pick objects, with optional AES-256-GCM encryption. SQL databases can also go to a plain or gzipped SQL script, or through pg_dump, mysqldump or mariadb-dump when they are installed.

Usage

querybara backup --help

Usage: querybara backup [options] <target>

back up a database to a Querybara archive (.qbak), SQL, or with
pg_dump/mysqldump

Arguments:
  target                    profile name or id, or connection URI

Options:
  --out <file>              the backup file to write
  --format <format>         file format (default: from --out, else qbak)
                            (choices: "qbak", "sql", "sql-gz", "custom")
  --encrypt                 encrypt the archive with a passphrase (AES-256-GCM)
  --passphrase-env <VAR>    take the passphrase from this variable (default
                            QUERYBARA_BACKUP_PASSPHRASE, else a prompt)
  --no-compress             archives: store each object without gzip
  --native                  use pg_dump, mysqldump or mariadb-dump from PATH
  --schema <name>           PostgreSQL: back up this schema; repeatable
                            (default: every schema)
  --table <name>            back up this table and what it needs; repeatable
                            (default: everything)
  --exclude-table <name>    leave this table out; repeatable
  --exclude-data <name>     back up this table without its rows; repeatable
  --schema-only             the structure without data
  --data-only               the data without the structure
  --grants                  include privileges (GRANT statements)
  --owners                  include owners and definers
  --no-snapshot             do not read everything in one snapshot transaction
  --deferrable              PostgreSQL: wait for a snapshot free of
                            serialization anomalies
  --rows-per-insert <n>     rows per INSERT statement (default 500)
  --collection <name>       MongoDB: back up this collection; repeatable
  --documents <format>      MongoDB: documents as BSON or Extended JSON
                            (choices: "bson", "ejson")
  --pattern <glob>          Redis: back up the keys matching this pattern
                            (default *)
  --database <name>         database to back up (Redis: its number)
  --tls <mode>              TLS mode for this run: disable, require, verify-ca
                            or verify-full
  --ssh <user@host[:port]>  reach URI targets through this SSH server; repeat
                            for jump hosts, in order
  --ssh-key <path>          SSH private key file (OpenSSH, PEM or PuTTY .ppk)
  --ssh-password-env <VAR>  take the SSH password from this variable (default
                            QUERYBARA_SSH_PASSWORD, else a prompt)
  --ssh-agent               log in with the keys of ssh-agent (SSH_AUTH_SOCK) or
                            Pageant
  --proxy <url>             reach URI targets (or their first SSH server)
                            through socks5://host:port or http://host:port
  --ssh-accept-new          trust and remember an SSH host key not seen before
                            (a changed key is always refused)
  --known-hosts <path>      SSH known hosts file (default: the desktop app's)
  -h, --help                show help for a command

The Querybara archive holds one file per object and a manifest, so a restore can pick objects;
with --encrypt it is sealed with AES-256-GCM under a key derived from the passphrase (scrypt),
and any change to the file is detected. The passphrase is never read from the command line:
set QUERYBARA_BACKUP_PASSPHRASE (or --passphrase-env) or type it when asked. SQL databases are read in
one consistent snapshot (MySQL and MariaDB: InnoDB tables); MongoDB keeps collection options
and indexes; Redis keeps each key's TTL.

Examples:
  querybara backup prod --out shop.qbak --encrypt
  querybara backup prod --out shop.sql.gz --schema public --exclude-data public.audit_log
  querybara backup prod --out shop.dump --native
  querybara backup "mongodb://app@localhost/app" --out app.qbak
  querybara backup "redis://localhost/2" --out sessions.qbak --pattern "session:*"

The --tls and SSH options are described in Global options.

Examples

An encrypted archive, with the passphrase from the environment:

QUERYBARA_BACKUP_PASSPHRASE="$BACKUP_PASSPHRASE" querybara backup shop-prod --out shop.qbak --encrypt

One schema to gzipped SQL, without the rows of a large table:

querybara backup shop-prod --out shop.sql.gz --schema shop --exclude-data shop.audit_log

With pg_dump, in its custom format:

querybara backup shop-prod --out shop.dump --native

MongoDB and Redis:

querybara backup "mongodb://[email protected]/catalog" --out catalog.qbak
querybara backup "redis://cache.example.com/2" --out carts.qbak --pattern "cart:*"

Formats

When you leave out --format, the name given to --out decides it: .sql is SQL, .sql.gz or .gz gzipped SQL, .dump or .backup the pg_dump custom format, and anything else a Querybara archive.

--format	Engines	Notes
qbak	Every engine backup supports	One entry per object, gzip per entry, optional encryption
sql	PostgreSQL, MySQL, MariaDB	A SQL script
sql-gz	PostgreSQL, MySQL, MariaDB	A gzipped SQL script
custom	PostgreSQL	pg_dump’s custom format; needs --native

MongoDB and Redis backups are always Querybara archives. --native uses pg_dump, mysqldump or mariadb-dump from PATH and writes SQL or the custom format, not archives.

What is backed up

• PostgreSQL, MySQL and MariaDB are read in one consistent snapshot transaction (on MySQL and MariaDB, for InnoDB tables). --no-snapshot turns that off; on PostgreSQL, --deferrable waits for a snapshot free of serialization anomalies. Narrow the backup with --schema, --table, --exclude-table and --exclude-data, or take only the structure or the data with --schema-only and --data-only. --grants and --owners add privileges and owners.
• MongoDB collections keep their options and indexes. --collection picks collections, and --documents stores documents as BSON or Extended JSON.
• Redis keys keep their TTLs. --pattern picks keys, and --database the database number.

Elasticsearch

querybara backup does not back up Elasticsearch.

Encryption

--encrypt seals the archive with AES-256-GCM under a key derived from the passphrase with scrypt, and any change to the file is detected when it is read. The passphrase comes from QUERYBARA_BACKUP_PASSPHRASE, from the variable --passphrase-env names, or from a hidden prompt that asks twice. It must be at least 8 characters long. It is never read from the command line, and it is not stored anywhere: without it, the archive cannot be restored.

Encryption needs the Querybara archive format.

Keep the passphrase

There is no way to open an encrypted archive without its passphrase.

Related

• querybara restore
• The .qbak archive format
• querybara export