Hot Backup

Creating a full database backup online

Use system procedure sys.hot_snapshot(full_path_to_tar_file string[, on_server boolean]) to quickly create a binary copy of all persisted data files of the current connected database into one tar file. To activate it, simply execute the SQL statement:

call sys.hot_snapshot('/tmp/db_backup_2020_07_27.tar');

or on MS Windows (note the R string value prefix to interpret the string characters as Raw):

call sys.hot_snapshot(R'D:\Temp\db_backup_2020_07_27.tar');

By default, the file will be written by the MonetDB server, to a file on the server host with the user id the server's is running as. The optional second parameter on_server can be set to false to let the file be written by the client, usually mclient, on the client host, with the user id and permissions of the mclient process. This is especially useful if the client runs on a different system than the server.

Optionally the tar file can be compressed by adding an extra file name suffix: .lz4 or .gz or .bz2 or .xz.

call sys.hot_snapshot('/tmp/db_backup_2020_07_27.tar.lz4');

or on MS Windows:

call sys.hot_snapshot(R'D:\Temp\db_backup_2020_07_27.tar.gz');

It is recommended to use no compression or the fast lz4 compression so the tar file is written as fast as possible. This is desirable as it keeps the time the database lock is held as short as possible. Compressions gz and bz2 take much more time. Compression xz compresses best but also takes most time to complete and thus keeps the database locked the longest.

By default only user monetdb (the admin) may execute this system procedure. If needed the monetdb user may grant execute privilege to other users of the database, for instance to user mr_backup via SQL:

grant execute on procedure sys.hot_snapshot to mr_backup;

Restoring a database from the (compressed) tar file

The tar file expands to a single directory with the same name as the database that was snapshotted. This directory can be passed directly as the --dbpath startup argument of mserver5 or it can be copied into an existing dbfarm and started from monetdbd. For example, on the machine with the database:

$ monetdb status
name  state   health               remarks
tpch  R  8m  100%  0s  mapi:monetdb://hank:50000/tpch
$ mclient -d tpch -s "call sys.hot_snapshot('/tmp/tpch.tar.gz')"
$ ls -l /tmp/tpch.tar.gz
-rw------- 1 jvr jvr 13337600 Dec  4 11:16 /tmp/tpch.tar.gz

On the other machine:

$ monetdbd create $HOME/myfarm
$ monetdbd start $HOME/myfarm
$ tar -C $HOME/myfarm -axf /tmp/tpch.tar.gz
$ monetdb status
name  state   health               remarks
tpch  S                 mapi:monetdb://hank:50000/tpch
$ mclient -d tpch -s "select count(*) from lineitem"
+-------+
| %1    |
+=======+
| 60175 |
+-------+
1 tuple

To expand the tar file into a directory with a different name, for example because there already is a database with the original name, use the --strip-components option of GNU tar:

$ mkdir $HOME/myfarm/tpch2
$ tar -C $HOME/myfarm/tpch2 --strip-components=1 -axf /tmp/tpch.tar.gz
$ monetdb status
name   state   health               remarks
tpch   R 11m  100%  0s  mapi:monetdb://hank:50000/tpch
tpch2  S                mapi:monetdb://hank:50000/tpch2

In this example, we manually create the directory tpch2 in the dbfarm. The -C option makes tar unpack in this directory. The --strip-components=1 option removes the original directory name tpch/ from the extract files. Alternatively, we can avoid the mkdir using the even more nonstandard --one-top-level option.

pre$ tar -C $HOME/myfarm --one-top-level=tpch3 --strip-components=1 -axf /tmp/tpch.tar.gz

On MS Windows we recommend users to install 7-zip utlity to open the (compressed) tar file and extract the database files.