Local CockroachDB Backup and Restore: A Simple Developer's Guide

Want to master local CockroachDB backups and restores for faster development? This guide provides a streamlined approach, perfect for developers seeking a repeatable and efficient workflow. Learn how to easily back up and restore your local database using docker-compose and simple commands. This is very helpful to have for local development.

The Problem: Painless CockroachDB Backups and Restores

Setting up reliable CockroachDB database backup and restore procedures can be a hassle, especially during local development. You need a way to:

• Regularly save your database state.
• Quickly restore from backups after data corruption or accidental changes.
• Keep your backups organized and accessible.

Solution: Automating Backups and Restores

This guide uses simple scripting alongside a docker-compose.yml file to automate the backup and restore processes for your local CockroachDB instance.

1. The docker-compose.yml Configuration

First, let's look at how to set up the docker-compose.yml file for seamless backup access:

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:

The magic happens in the volumes section. We're binding the /cockroach/cockroach-data/extern directory inside the container to a local folder named cockroach-extern in your project. Then CockroachDB saves all backups to this folder, making the backup files easily accessible from your host machine.

2. Taskfile for Easy CockroachDB Command Execution

To simplify running backup and restore commands, we'll use a Taskfile.yml:

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

• Backup Task: Executes the backup-to task, creating a backup in the specified folder.
• Restore Task: Executes the restore-from task, dropping the existing defaultdb and restoring it from the latest backup.

3. Backup your CockroachDB database

To take a full backup of your local cockroachdb database, simply run:

task backup

This command executes the SQL command to create a full backup of your CockroachDB instance and stores the resulting data in the cockroach-extern/backup/local-node-backup directory.

4. Restoring your CockroachDB database

If you need to restore cockroachdb default database from a previous backup, run:

task restore

This command restores the database from the most recent backup located in the specified directory (cockroach-extern/backup/local-node-backup), first dropping the existing database.

Benefits of using CockroachDB backup

• Fast Recovery: Quickly revert to a previous state after errors or accidental data changes.
• Local Accessibility: Backups are stored locally, making them easy to access and manage.
• Automation: The Taskfile.yml automates the entire process, eliminating manual steps and potential errors.

Taking it Further with local development

This setup provides a solid foundation for managing local CockroachDB backups. Consider exploring these enhancements:

• Scheduled Backups: Use cron jobs or similar tools to schedule regular backups.
• Version Control: Store your cockroach-extern directory in Git to track backup history.
• Cloud Storage: Sync your local backups to a cloud storage service for offsite redundancy.

By implementing these strategies, you can ensure the safety and accessibility of your CockroachDB data, streamlining your local development workflow. With these techniques, managing CockroachDB backups becomes a breeze, freeing you to focus on building great applications.