Creating a full database backup online
Use system procedure:
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:
or on MS Windows (note the R string value prefix to interpret the string characters as Raw):
This will create a tar file to the given location and name on the server.
Warning: 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.
Warning: 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.
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.
or on MS Windows:
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: for releases Jun2020 and Jun2020-SP1 on MS Windows, only .gz and .bz2 compression formats were supported. With Oct2020 release now also .xz and .lz4 compression formats are supported on MS Windows. 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;
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
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
$ 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.