Backup

Links

1. BACKUP statement to efficiently back up cluster's schemas and data to NFS
2. RESTORE statement to efficiently restore schema and data as necessary.

Backup a cluster (To S3)

Full Backup

BACKUP INTO 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' AS OF SYSTEM TIME '-10s';

Backup database/s

Requires an enterprise license

BACKUP DATABASE bank, employees INTO 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' AS OF SYSTEM TIME '-10s';

Perform Core backup and restore

Backup

cockroach dump <database_name> <flags> > backup.sql

Restore

cockroach sql --database=[database name] < backup.sql

Scheduling automated backups

Fully automated backups

CREATE SCHEDULE core_schedule_label
  FOR BACKUP INTO 's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    RECURRING '@daily'
    FULL BACKUP ALWAYS
    WITH SCHEDULE OPTIONS first_run = 'now';

Incremental Backups

If cluster grows too large for nightly full backups,

1. less frequent full backups (e.g., weekly)
2. Nightly incremental backups.

Incremental backups are storage efficient and faster than full backups for larger clusters.

BACKUP INTO LATEST IN '{collectionURI}';

This will add the incremental backup to the default /incrementals directory at the root of the backup collection's directory.

Incremental Backups from existing full backups

Backup collections

1. A backup collection defines a set of backups and their metadata
2. The collection can contain multiple full backups and their subsequent incremental backups.

View Backups at collection URI

SHOW BACKUPS IN 's3://{bucket name}/{path}?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}';

Restore from Collection URI

RESTORE FROM LATEST IN '{collectionURI}';

Reference

1. Backup and Restore Overview | CockroachDB Docs
2. Take Full and Incremental Backups | CockroachDB Docs
   1. incremental backup
   2. collections
3. Use Cloud Storage for Bulk Operations.
4. CREATE SCHEDULE FOR BACKUP | CockroachDB Docs
5. Protect Your Data Using CockRoach Backups - Justuno