Using Journal Hot Copy

A journal hot copy creates a transactionally-consistent copy of the changes in an SM since the previous journal hot copy. Journal hot copy enables point-in-time restore, allowing restore to a chosen transaction. To use journal hot copy, it must be enabled with a startup option.

Like full and incremental hot copy, journal hot copy includes every storage group the hot copied SM serves. Unlike full and incremental hot copy, journal hot copy does not copy atom files, it only copies journal files. Journal hot copy requires the use of backup sets.

When restoring from a backup set containing journal hot copies, the user may request a transaction to restore. NuoDB Archive will apply only those messages from the hot copied journal files necessary to restore this transaction (and its dependencies). If the backup set contains no incremental hot copies, then journal messages will be applied to the full hot copy. If the backup set contains incrementals, then journal messages will be applied to the latest incremental hot copy that was hot copied prior to the transaction to restore, which may decrease restore latency.

A journal hot copy of an SM is approximately the size of all journal files created since the last journal hot copy. This is proportional to the number of changes made in the SM’s archive.

If journal hot copy is enabled and an SM is restarted and needs to archive sync, the journal size will grow to the amount of data to be synchronized. Sufficient disk space must be provisioned to allow for this.

Enabling journal hot copy

To use journal hot copy with an SM, start that SM with the option --journal-hot-copy enable.

When started with the option --journal-hot-copy enable, an SM will retain each journal files on disk until it is journal hot copied into a backup set.

This means that unless journal hot copy is executed periodically, an SM running with --journal-hot-copy enable could exhaust free disk space on the journal device. To avoid this situation, run journal hot copy periodically. You may need to monitor the size of your journal directory on each SM to understand the necessary frequency for journal hot copy based on your application workload.

How to use journal hot copy and point-in-time restore

To request a journal hot copy specify type journal when executing the hot copy and specify an existing backupSetDirectory. The backupSetDirectory does not need to contain a completed full hot copy; i.e., the full hot copy may be in progress when the journal hot copy is executed.

You must use the most-recently created backup set when initiating journal hot copy.

Transactions from journal hot copies in a backup set must be restored into a new archive in order to use them to start an SM. Restore using journal hot copies is a two-step process. First, identify a transaction of interest in a backup set by its timestamp using the --report-timestamps option of NuoDB Archive. Then, restore that transaction by passing the transaction id to the --restore-snapshot option and the empty or non-existent directory where the archive should be restored to the --restore-dir option of NuoDB Check. This will extract the transaction specified by --restore-snapshot into the directory specified by --restore-dir. Only committed transactions can be restored.

In addition to finding points in time to restore post hoc via --report-timestamps, a database administrator or application may make note of transaction ids of interest and save them for later (e.g., in a database table). For example, during an important transaction like an ETL prior to commit, the transaction might be extracted via gettransactionid() and saved in case it is needed later for point-in-time restore.

Restoring a transaction will restore the effects of that transaction and the effects of all transactions visible to it at the time it executed on a TE (its dependencies).

There is no option to restore all transactions in a journal hot copy in a backup set. You must select a transaction to restore by its transaction id.

Journal hot copy and incremental hot copy

Applying journal files during restore can be expensive. When a backup set contains both journal hot copies and incremental hot copies, restore will only apply journal files that were hot copied after the latest incremental hot copy that was hot copied before the transaction being restored. This minimizes restore time.

It is important to take periodic incremental hot copies, or else to start new backup sets periodically with full hot copy. Otherwise the latency of point-in-time restore may become large.

Examples: running journal hot copy into a backup set

Before journal hot copy can be executed, a backup set must exist. However, the full hot copy invoked to create that backup set does not have to have finished.

As for incremental hot copy, we’ll create a backup set first using full hot copy.

nuocmd hotcopy database --db-name test --type full --default-backup-dir /tmp/hotcopy/2017-12-18 --timeout 60

To produce an interesting journal hot copy, we’ll perform three inserts using autocommit into an empty table with a single integer column that was already created prior to the creation of the full hot copy.

SQL> insert into foo values(1);
SQL> insert into foo values(2);
SQL> insert into foo values(3);

Perform a type journal hot copy into the latest backup set.

nuocmd hotcopy database --db-name test --type journal --default-backup-dir /tmp/hotcopy/2017-12-18 --timeout 60

Use NuoDB Archive to --report-timestamps available for restore. Three transactions are restorable.

nuoarchive restore --report-timestamps /tmp/hotcopy/2017-12-18
2017-12-19T20:58:15 6530
2017-12-19T20:58:16 6658
2017-12-19T20:58:18 6786

We will restore the second transaction. It corresponds to the next-to-last transaction we committed (“insert into foo values (2)”).

nuoarchive restore --restore-snapshot 6658 --restore-dir /tmp/restore /tmp/hotcopy/2017-12-18

If we start a new database on the restored SM (say, test2) we can see that we restored the requested transaction.

$ nuosql test2 --password … --user …
SQL> select * from foo;

As with full hot copy, journal hot copy can also be executed using the plan syntax.