Backup and restore cockroach DB

1. Create / generate gcp key, which is the key can be used to access google cloud storage. So please add account service which having google cloud storage access.

2. Klick add key, then download the in json format. After that convert key file to be base 64 format, we can user online base64 converter.

3. Create folder backup on google cloud storage

4. Create backup scheduler on cockroach DB

CREATE SCHEDULE backup_schedule
FOR BACKUP INTO ‘gs://backup?AUTH=specified&CREDENTIALS={base 64 gcp key}’
RECURRING ‘0 21 * * *’
FULL BACKUP ALWAYS
WITH SCHEDULE OPTIONS ignore_existing_backups;

Note : replace {base 64 gcp key} with base64 converted gcp service account key file.

Based on query above, RECURRING ‘0 21 * * *’ means the scheduler will run every 09:00 PM every day, and ignore_existing_backups means we ignore existing backup, so that the backup can be proceed.

We can check whatever schedule that we have created before with query :

SHOW SCHEDULES;

The important column is nest_run column.

5. Check backup file in google cloud storage

SHOW BACKUPS IN ‘gs://backup?AUTH=specified&CREDENTIALS={base 64 gcp key}’

6. Restore existing backup to new DB environment

restore database example FROM ‘2022/04/03–100000.42’ IN ‘gs://backup?AUTH=specified&CREDENTIALS={base 64 gcp key}’;

source : https://www.cockroachlabs.com/docs/stable/backup.html?filters=gcs