Restore deleted tables

Hi,

So is there a way to restore deleted tables or rollback options to the last checkpoint? Not sure how to do that, if anyone has any idea how to do a database backup for Omniscidb, that would be great!!

Looking forward to your answers - thanks Dipanwita.

Hi @dipanwita2019,

You have various ways to do backups omniscidb

First, you physically backup all the files comprising the DB itself, and this could be useful in case of a hardware failure like a broken disk.
To do that, you have to stop the services and create an archive of $OMNISCI_STORAGE data-dir.

tar zxvf /some_path/omnisci_storage_backup.tar.gz /omnisci_storage/

Second, you can use the dump/restore table commands to backup single tables in the database.

DUMP TABLE table1 TO '/some_path/table1.dmp' WITH (COMPRESSION='gzip');

You can read about those commands here

Dump/restore can also be a fast way to duplicate a table. For example, you can dump table1 and use the dump to restore a table that you will call table2.

Third, use the copy to command to do logical dumps in text format; with a copy to command, you can specify a query so that you can filter, aggregate, etc. the data you are exporting

COPY (select * from table1 where col1 between 1 and 10000000) to '/somepath/table1.csv';

This is a link to the docs.

Then if you accidentally deleted or inserted, or updated records, you can revert the table to a previous epoch. Still, I suggest using this in case of an emergency, not as a normal way to roll back the data.

There are two hidden commands in omnisql called.

\gte and \ste

The first will return the actual epoch of the table, and the second will set the epoch you will specify

so as an example, we have a table with some records, and we delete some records accidentally

omnisql> select count(*) from fact_sales_16;
EXPR$0
123534963
1 rows returned.
Execution time: 9 ms, Total time: 10 ms
omnisql> \gte fact_sales_16
table epoch is 3
omnisql> delete from fact_sales_16 where extract(month from the_date) = 12;
omnisql> select count(*) from fact_sales_16;
EXPR$0
113205120
1 rows returned.
Execution time: 57 ms, Total time: 58 ms

to restore the records we deleted, we have to set the previous epoch

omnisql> \ste "fact_sales_16 3"
table epoch set
omnisql> select count(*) from fact_sales_16;
EXPR$0
123534963
1 rows returned.
Execution time: 48 ms, Total time: 48 ms

This would cover all the options, also the undocumented ones, you can use to save your data with omniscdb.

Regards,
Candido

1 Like

Just to add to @candido.dessanti’s answer, you can also run: (Warning: this is undocumented/not officially supported)

SHOW TABLE DETAILS <table_name> to get various metadata about the table, including the current epoch (max_epoch), and the lowest epoch you can roll back to (min_epoch).`

In addition to \ste, you can also roll back a table to a certain epoch via ALTER TABLE <table_name> SET epoch = <epoch_number>

For example:

omnisql> insert into epoch_test (a) values (1);
omnisql> insert into epoch_test (a) values (2);
omnisql> insert into epoch_test (a) values (3);
omnisql> insert into epoch_test (a) values (4);
omnisql> insert into epoch_test (a) values (5);
omnisql> show table details epoch_test;
table_id|table_name|column_count|is_sharded_table|shard_count|max_rows|fragment_size|max_rollback_epochs|min_epoch|max_epoch|min_epoch_floor|max_epoch_floor|metadata_file_count|total_metadata_file_size|total_metadata_page_count|total_free_metadata_page_count|data_file_count|total_data_file_size|total_data_page_count|total_free_data_page_count
430|epoch_test|3|false|0|4611686018427387904|32000000|3|5|5|2|2|1|16777216|4096|4088|1|536870912|256|254
omnisql> \gte epoch_test
table epoch is 5
omnisql> select * from epoch_test;
a
1
2
3
4
5
omnisql> alter table epoch_test set epoch = 1;
Cannot set epoch for table (1, 430) lower than the minimum rollback epoch (2).
omnisql> alter table epoch_test set epoch = 3;
omnisql> show table details epoch_test;
table_id|table_name|column_count|is_sharded_table|shard_count|max_rows|fragment_size|max_rollback_epochs|min_epoch|max_epoch|min_epoch_floor|max_epoch_floor|metadata_file_count|total_metadata_file_size|total_metadata_page_count|total_free_metadata_page_count|data_file_count|total_data_file_size|total_data_page_count|total_free_data_page_count
430|epoch_test|3|false|0|4611686018427387904|32000000|3|3|3|2|2|1|16777216|4096|4092|1|536870912|256|254
omnisql> \gte epoch_test
table epoch is 3
omnisql> select * from epoch_test;
a
1
2
3

Again note that as @candido.dessanti mentioned, only use this capability if you know what you’re doing, as its currently not an officially supported/documented feature. And as always, making regular backups is highly recommended.

1 Like

Hi,

I suggest using the commands suggested by @todd because they are available using any driver (JDBC, ODBC, PYTHON), so you aren’t restricted to omnisql’s use, but as Todd and Me mentioned, don’t use as a regular ROLLBACK.