CockroachDB how to restore a dropped column?-CodePudding

I accidentally dropped a column. I have no backup set up for this single node setup. Does cockroach have any auto backup mechanism or am I screwed?

CodePudding user response：

CockroachDB stores old versions of data at least through its configured gc.ttlseconds window (default one day). There's no simple way that I know of to instantly restore, but you can do

SELECT * FROM <tablename> AS OF SYSTEM TIME <timestamp before dropping the column>

And then manually reinsert the data from there.

CodePudding user response：

We could use time-travel queries to restored deleted data within a garbage collection window before the data is deleted forever.

The garbage collection window is determined by the gc.ttlseconds field in the replication zone configuration.

Examples are:

SELECT name, balance
  FROM accounts
    AS OF SYSTEM TIME '2016-10-03 12:45:00'
 WHERE name = 'Edna Barath`;

SELECT * FROM accounts AS OF SYSTEM TIME '-4h';

SELECT * FROM accounts AS OF SYSTEM TIME '-20m';

I noticed that managed CockroachDB run database backup (incremental or full) hourly up to 30 days. You may be able to restore the whole database from it.

Please note that the restoration will cause your cluster to be unavailable for the duration of the restored. All current data is deleted.

We can manage our own backup, including incremental, database and table level backup. We need to configure a userfile location or a cloud storage location. This require billing information.