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.
The resulting tar file may become very large depending on the size of your database on the file system. Check your database size (in the dbfarm directory) and assure your file system has enough free disk space to store a copy of it as tar file.
Always include the .tar
part in the file name as done in the examples above as some tools (on Windows)
use it to associate the proper program to open it. The file name is also used to determine which
compression algorithm to apply.
Procedure sys.hot_snapshot()
is introduced in release Jun2020 (11.37.7) and not available in older releases.
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;
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.