Restoring the result of 'cockroach dump'

General
#1

Hello everyone,
I have some tables in my database that use foreign keys, if I use cockroach dump to dump the entire database and then try to restore it locally (on to a cockroach DB instance) I get errors along the lines of:

Error: pq: referenced table "auth_rule" not found
Failed running "sql"

If I look at the dumped SQL the auth_rule table above is dumped AFTER the table that references it? Am I missing something? How can I restore these dumps without manually changing the ordering of the CREATE TABLE statements?

1 Like
#2

Are you using v1.0, by chance? I believe this is a known bug with
cockroach dump that will be fixed in v1.1. In the meantime you can use
the latest v1.1 beta (provided you’re not running in production).

If switching to v1.1 doesn’t fix it—or you need this for a production v1.0
cluster—I can do some more digging. Someone else from the team might be
along shortly with more information, too.

#3

Hi Nikhil, thanks for your time!

Yes we are using 1.0.5 in production (starting to regret switching to Cockroach!).

I will ensure I can actually restore by manually reorganising the dumps and then anxiously await the 1.1 release!

#4

Before you go too far down the manual road, you might have some luck by
using a v1.1 binary to run cockroach dump against your v1.0 production
cluster. In general, we discourage using different versions of Cockroach on
the client and server, but running dump is perfectly safe.

#5

Genius! I will try it now and report back with the results.

#6

Ah, no dice. The PR to fix this issue (#17581) depends on an internal table that isn’t present in v1.0.

#7

By the way, have you seen the BACKUP feature? It’s enterprise only, but is much faster than cockroach dump on larger clusters and doesn’t suffer from this bug. We offer evaluation licenses if you’d like to try it out.

Also, I’m sorry to hear you’re starting to regret switching to Cockroach! Would you have any time to walk our product team through your experience so far and what we can improve? (cc @dianasaur323)

#8

Ah yes I don’t have the crdb_internal.backward_dependencies table in my cluster. Damn. Any idea when 1.1 will be ready for production use?

Yes we have spoken with your team with regards to purchasing a license, the fees are way out of our reach. Hopefully one day you may offer a license to use the enterprise backup features only at a cost that a small business can afford. Until then I’m stuck with cockroach dump or the pain of migration back to Mysql master / slave replication.

Happy to talk to your team about our experience so far, feel free to message me on these forums or I can message you my email address if you prefer.

1 Like
#9

Hi Joe! I’m so sorry to hear about your experience thus far. Sticking with cockroach dump or migrating back to MySQL sound like sub-optimal solutions for you in your case. Re pricing, we would love to find something that would work out for you, but I’d like to understand what your constraints are. Let’s find a time to chat so that we can discuss your situation, and we can work together to find a solution! My email is diana at cockroachlabs.com.

#10

Hello again @Joe. I wanted to follow up to make sure that we fully addressed your issues. Let us know if you still have time to connect!