How I do local development CockroachDB Backups

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.

Apr 26, 2025 - 22:35

How I do local development CockroachDB Backups

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.