Cockroach dump deprecated, which is the replacement?

Hi. I have the job to create a database backup on request and deliver as one ZIP file. I normally would use

cockroach dump <database> --insecure --host= > backup.sql | gzip > /opt/backup/crdb-$(date +\%F-\%T).sql.gz

but cockroach dump is labeled as deprecated, only supporting schema, and the BACKUP and CREATE SCHEDULE FOR BACKUP do not create a file like needed for my purposes.

What is the recommended way to export a whole dump into one file that can be easily restored later?

Hi @Volker.Schmid! Have you tried using a userfile destination URL?

See BACKUP | CockroachDB Docs and Use Userfile for Bulk Operations | CockroachDB Docs for some more information.

The idea is that the file gets stored directly on your server.

Hi Rafi, but I don’t want CSV data. With this I can not restore from scratch (including create table etc). I simply need the SQL statements like from a SQL dump to put it in long term storage (single file per export). I really wonder why this useful functionality was deprecated?

If that would work, I could use that for easy restoring on a single node for later examination or even restore the production database if the cluster was lost. And I could do this with existing file backup and retention infrastructure.

I can not install a fileserver somewhere. I have a local mounted backup volume for files on my CentOS machine. This has to be used to backup the database on different times, preferrable like a snapshot into a single file (eg compressed).

I already tried CREATE SCHEDULE FOR BACKUP. Is it possible to get rid of the year and month folders while using that? Maybe I can take the files from there? The LATEST file points to 15-120000.00 for the moment (MONTH-HOURMINUTESECOND.??). Can I restore such file easily by pointing IMPORT to such file? Or do i have to put this into some special folder and structure before I’m able to use it for restoring?

I also tried

cockroach sql -e "SELECT * FROM schema.database.table" --format=sql --insecure --host= | gzip > crdb-$(date +\%F-\%T).sql.gz

This only seems a good way if I accept to do this for every single table and that the generated create table statement is always naming the table “results” and does the wrong type definitions (all is STRING). This is also boring and a bad solution.

I wonder why this was all so complicated :confused: Isn’t there an easy and user friendly way like with mysqldump or pg_dump?

I found my solution and post it here for the case someone else needs something similar:

I created a crontab job like this:

0 0,6,12,18 * * * rm -rf /home/cockroach/cockroach-data/extern/* && /usr/local/bin/cockroach sql -e "BACKUP DATABASE vaccinator INTO 'backup' IN 'nodelocal://1/';" --insecure --host= && zip -q -r /backups/crdb-$(date +\%F-\%H\%M).zip /home/cockroach/cockroach-data/extern/backup/
5 * * * * find /backups/*.zip -mtime +1 -delete 2> /dev/null

As you can see this is using my node ID 1 at Adapt to your needs.

This is what it is doing at 00:00, 06:00, 12:00 and 18:00:

  • Cleaning up previous backup data in /home/cockroach/cockroach-data/extern/ (Warning: Modify this if you need to use the extern folder for other purposes, too).
  • Making a full backup into a “backup” folder in your extern folder.
  • Zip this backup folder into a single file (with date/time) to a local /backups mount (or folder).
  • Remove all zip files from /backups that are older than 1 day (backup retention).

By this, the /backups mount always contains the last four backups.

To restore such, you can do like this:

First unzip:

rm -rf /home/cockroach/cockroach-data/extern/*
unzip /backups/<yourBackupFile>.zip -d / 
chown -R cockroach:cockroach /home/cockroach/cockroach-data/extern/

Then you can RESTORE the backup like this:

cockroach sql -e "RESTORE DATABASE <db_name> FROM 'backup' IN 'nodelocal://1/';" --insecure --host=

Please note that I did all with --insecure for my test environment. You may adapt to use certificates for production.

Please also note that you might need to drop the database <db_name> first, if it already exists. Otherwise you get an error because you can’t restore a database that is already there.

Full ack. It’s very pity that cockroach dump is deprecated.

@CRDB-team Is there any chance to revert this decision?

There are no plans to re-add dump functionality. We did build in some alternatives that, while syntactically different from what you’re used to, are effectively doing something similar under the hood.

cockroach sql -e "SHOW CREATE ALL TABLES;" > schema.sql would dump all database schema you’re connected to, and then looping through the tables to to cockroach sql -e "select *" > data.sql

Understanding this is not great, re-adding cockroach dump is not currently on the near term roadmap.

1 Like