I have this taskfile tasks to make running commands easier:
version: 3
tasks:
backup:
desc: Backup the local CockroachDB database
cmds:
- task: backup-to
vars:
FOLDER: "backup/local-node-backup"
restore:
desc: Restore the local CockroachDB backup database
cmds:
- task: restore-from
vars:
FOLDER: "backup/local-node-backup"
backup-to:
requires:
vars:
- FOLDER
cmds:
- cmd: mkdir -p cockroach-extern
ignore_error: true
- |
cockroach sql --insecure --host=localhost --port=26257 --execute="BACKUP INTO 'nodelocal://1/{{.FOLDER}}';"
restore-from:
requires:
vars:
- FOLDER
cmds:
- |
cockroach sql --insecure --host=localhost --port=26257 --execute="DROP DATABASE IF EXISTS defaultdb;"
- |
cockroach sql --insecure --host=localhost --port=26257 --execute="RESTORE DATABASE defaultdb FROM LATEST IN 'nodelocal://1/{{.FOLDER}}';"
And I have my docker-compose.yml setup like this:
services:
crdb:
image: cockroachdb/cockroach:latest-v25.1
ports:
- '26257:26257'
- '8080:8080'
command: start-single-node --insecure
volumes:
- crdb:/cockroach/cockroach-data
- type: bind
source: ./cockroach-extern
target: /cockroach/cockroach-data/extern
volumes:
crdb:
And from my own project docs:
Database Backup and Restore
You will notice in docker-compose.yaml these few lines of code at the database declaration. Let me explain what's going on.
- type: bind
source: ./cockroach-extern
target: /cockroach/cockroach-data/extern
CockroachDB saves all backups, exports etc. to folder /cockroach/cockroach-data/extern so we will bind this folder to a local folder in the project directory so we can have access to this data and manipulate it.
Backups
Info about backups https://www.cockroachlabs.com/docs/stable/take-full-and-incremental-backups
To run a full backup we will run the following command
BACKUP TO 'nodelocal://1/cockroach-backup';
This will backup all the data into the cluster to the local directory of nodeID 1 and into the cockroach-backup folder in the extern root directory of the respective node.
Now if you look into the local folder you will notice ./cockroach-extern/cockroach-backup contains some data.
Restores
To run a restore of defaultdb, the database the app uses locally we have to run the following command
RESTORE DATABASE defaultdb FROM LATEST IN 'nodelocal://1/cockroach-backup' WITH new_db_name = 'new_defaultdb';
This command will look at the LATEST backup in the cockroach-backup folder and will restore database defaultdb and assign it a new name called new_defaultdb. Now you can access the restored version of the database by changing the connection string to point to new_defaultdb.
