Use system procedure: sys.hot_snapshot(full_path_to_tar_file string)
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 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**');
This will create a tar file to the given location and name on the server.
The resulting tar file may become very big (TB's) 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.
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 or fast lz4 compression so the tar file is written as fast as possible. This is desired to keep the db-lock as short as possible during the copying of the data files. 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. It can be useful when you want the smallest backup file size and no other users are connected to the database server.
Note: on MS Windows, .gz, .bz2, .xz and .lz4 compression formats are supported. We recommend to use no or gz or xz compression on Windows.
By default only monetdb user (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.