Easy CockroachDB Backup Strategy: Local Development Guide

Want to protect your CockroachDB data during local development? This guide provides a straightforward method for backing up and restoring your CockroachDB instance, ensuring you never lose your progress. Learn how to use simple commands and a Docker Compose setup for efficient local database management using CockroachDB.

Why Back Up Your Local CockroachDB?

Data loss during development can be a nightmare. Backups act as your safety net, allowing you to:

• Quickly revert to a stable state after experimentation.
• Easily share database states with team members.
• Protect against accidental data corruption.

Setting up Docker Compose for CockroachDB

First setup the docker-compose.yml file. This configuration simplifies running a single-node CockroachDB instance:

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:

This setup maps a local folder, ./cockroach-extern, to CockroachDB's external data directory. This is essential for accessing and manipulating backups.

Automating Backups with Taskfile

Taskfile simplifies running commands, this makes managing CockroachDB easier. Here's how to set one up for backups and restores:

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}}';"

Key points:

• backup and restore tasks use sub-tasks for organization.
• Variables like FOLDER keep the backup location configurable.

Performing a Full CockroachDB Backup

To manually trigger a full backup, use this SQL command:

BACKUP TO 'nodelocal://1/cockroach-backup';

This command saves all database data to the cockroach-backup folder inside the extern directory on node ID 1. You'll find the backed-up data in ./cockroach-extern/cockroach-backup within your project directory. The CockroachDB backup is now safely stored in your defined directory.

Restoring Your CockroachDB Database

To restore a database (e.g., defaultdb) from the latest backup, execute:

RESTORE DATABASE defaultdb FROM LATEST IN 'nodelocal://1/cockroach-backup' WITH new_db_name = 'new_defaultdb';

This command restores defaultdb to a new database named new_defaultdb. You can then access the restored database by updating your connection string.

Connecting it All Together

By binding the external directory, automating tasks with Taskfile, and using simple backup/restore commands, you create a reliable local development workflow for CockroachDB. This setup makes experimenting with data and database schemas much safer.