MONETDB(1)

📦
version 11.55.7 · 2 versions
, in nixpkgs unstable

💖 monetdb(1) — control a MonetDB Database Server instance 💖

okay so this is the monetdb(1) page from the MonetDB Applications manual — yes, that manual name is folded up here instead of doing a whole separate header line, we're keeping it cute. This utility is the admin's main-character tool for bossing databases around inside a MonetDB Database Server instance. Important vibe check: it relies on monetdbd(1) running in the background for literally all operations. No background daemon, no slay. 💅

🌸 NAME 🌸

monetdb — control a MonetDB Database Server instance.

That's the whole assignment, bestie. 🫶

✨ SYNOPSIS ✨

monetdb [ monetdb_options ] command [ command_options ] [ command_args ]

🦋 DESCRIPTION 🦋

monetdb lets an administrator of the MonetDB Database Server perform various operations on the databases in the server. It relies on monetdbd(1) running in the background for all operations.

🎀 OPTIONS 🎀

monetdb_options affect all commands and control the general behavior of monetdb. Group-chat rules: these are the global settings, not per-command ones.

  • -q — suppresses all standard progress messages, only writing output to stderr if an error occurred. Quiet mode: no yapping unless something is actually wrong.
  • -h hostname — connect to hostname instead of attempting a connection over the local UNIX socket. This allows monetdb to connect to a remote monetdbd(1). The use of this option requires -P (see below). If hostname starts with a forward slash (/), hostname is assumed to be the directory where the UNIX sockets are stored. In that case, the -P option is not allowed. iykyk: slash at the front means socket directory, no -P allowed.
  • -p port — connects to the given port number instead of the default (50000). Requires -h to be given as option too. port 50000 is the default, don't forget it.
  • -P passphrase — specifies the passphrase necessary to login to a remote monetdbd(1). This option requires -h to be given as well. A bad passphrase causes monetdb to fail to login, and hence fail to perform any remote action. Bad passphrase = no login = no remote actions, periodt.
  • -v — show version, equal to monetdb version. Version check, iconic.

📜 COMMANDS 📜

The commands for the monetdb utility are create, destroy, lock, release, status, start, stop, kill, snapshot, set, get, inherit, discover, help, and version. They facilitate adding, removing, maintaining, starting and stopping a database inside the MonetDB Database Server.

For all commands, database arguments can be glob-like expressions. This allows wildcard matches. For details on the syntax, see EXPRESSIONS. Wildcard bestie behavior: yes, it's giving pattern matching.

🛠️ create [-m pattern] [-p password] database [database ...]

Initializes a new database in the MonetDB Database Server. A database created with this command makes it available under its database name, but not yet for use by clients, as the database is put into maintenance mode. This allows the database administrator to perform initialization steps before releasing it to users, unless the -p argument is supplied. See also monetdb lock.

The name of the database must match the expression [A-Za-z0-9_-]+.

  • -m pattern — with the -m flag, instead of creating a database, a multiplex-funnel is created. See section MULTIPLEX-FUNNEL in monetdbd(1). The pattern argument is not fully the same as a pattern for connecting or discovery. Each parallel target for the multiplex-funnel is given as username+password@pattern sequence, separated by commas. Here the pattern is an ordinary pattern as would be used for connecting to a database, and can hence also be just the name of a database. Multiplex-funnel time: comma-separated username+password@pattern targets, not the usual pattern situation.
  • -p password — the -p flag allows to create a database with the given password for the monetdb user. Since this protects the database from being accessed via well-known credentials, the created database is not locked after creation. This way, a new database can be created and used right away using the password supplied. Password supplied? No maintenance-mode lock after creation — use it right away.

💀 destroy [-f] database [database ...]

Removes the given database, including all its data and logfiles. Once destroy has completed, all data is lost. Be careful when using this command. Fr fr: this is the data-yeeting command. All data is lost after it completes. Be careful.

  • -f — by default, a confirmation question is asked, however the -f option, when provided, suppresses this question and removal is executed right away. Note that without this option you cannot destroy a running database, bring it down first using the stop command. No confirmation question with -f; removal happens right away. And without this option, bring a running database down first using stop.

🔒 lock [-a] database [database ...]

Puts the given database(s), or, when -a is supplied, all databases in maintenance mode. A database under maintenance can only be connected to by an administrator account (by default the monetdb account). A database which is under maintenance is not started automatically by monetdbd(1), the MonetDB Database Server, when clients request for it. Use the release command to bring the database back for normal usage. To start a database which is under maintenance for administrator access, the start command can be used. Maintenance mode = admin-only velvet rope, and monetdbd(1) won't auto-start it for clients.

🔓 release [-a] database [database ...]

Brings the given database(s), or, when -a is supplied, all databases back from maintenance mode. A released database is available again for normal use by any client, and is started on demand. Use the lock command to take a database under maintenance. Released database = back for normal use by any client, started on demand.

📊 status [-lc] [-s states] [database ...]

Shows the state of the given database, or, when none given, all known databases. Three modes control the level of detail in the displayed output. By default a condensed one-line output per database format is used. This output resembles pretty much the output of various xxxstat programs, and is ideal for quickly gaining an overview of the system state. The output is divided into four columns, name, state, health, and remarks. The state column contains two characters that identify the state of the database, based on Booting (starting up), Running, Stopped, Crashed and Locked (under maintenance). This is followed by the uptime when running. The health column contains the percentage of successful starts and stops, followed by the average uptime. The remarks column can contain arbitrary information about the database state, but usually contains the URI the database can be connected to. Default status is the quick one-line overview: name, state, health, remarks — very demure, very useful.

  • -c — the -c flag shows the most used properties of a database. This includes the state of the database (running, crashed, stopped), whether it is under maintenance or not, the crash averages and uptime statistics. The crash average is the number of times the database has crashed over the last 1, 15 or 30 starts. The lower the average, the healthier the database is. Crash averages over last 1, 15 or 30 starts: lower = healthier.
  • -l — triggered by the -l flag, a long listing is used. This listing spans many rows with on each row one property and its value separated by a colon (:). The long listing includes all information that is available. Long listing: one property per row, property: value, all the available info.
  • -s — the -s flag controls which databases are being shown, matching their state. The required argument to this flag can be a combination of any of the following characters. Note that the order in which they are put also controls the order in which the databases are printed. b, r, s, c, and l are used to print a starting up (booting), started (running), stopped, crashed and locked database respectively. The default order which is used when the -s flag is absent, is rbscl. State letters: b booting, r running, s stopped, c crashed, l locked. Put them in the order you want them printed. Default order: rbscl.

⚡ start [-a] database [database ...] / stop [-a] database [database ...] / kill [-a] database [database ...]

Starts, stops or kills the given database, or, when -a is supplied, all known databases. The kill command immediately terminates the database by sending the SIGKILL signal. Any data that hasn't been committed will be lost. This command should only be used as last resort for a database that doesn't respond any more. It is more common to use the stop command to stop a database. This will first attempt to stop the database, waiting for exittimeout seconds and if that fails, kill the database. When using the start command, monetdb(1) will output diagnostic messages if the requested action failed. When encountering an error, one should always consult the logfile of monetdbd(1) for more details. For the kill command a diagnostic message indicating the database has crashed is always emitted, due to the nature of that command. Note that in combination with -a the return code of monetdb(1) indicates failure if one of the databases had a failure, even though the operation on other databases was successful. kill sends SIGKILL immediately. Uncommitted data will be lost. Last resort only, bestie. stop tries to stop first, waits exittimeout seconds, then kills if it has to.

📸 monetdb snapshot write dbname

Takes a snapshot of the given database and writes it to stdout. Snapshot goes straight to stdout. Iconic.

📸 monetdb snapshot create [-t targetfile] dbname [dbname ...]

Takes a snapshot of the given databases. Here, dbname can be either the name of a single database or a pattern such as staging* indicating multiple databases to snapshot. Unless -t is given, the snapshots are written to files named <snapshotdir>/<dbname>_<YYYY><MM><DD>T<HH><MM>UTC<snapshotcompression> where snapshotdir is a monetdbd setting that has to be configured explicitly using monetdbd set and snapshotcompression is another monetdbd setting which defaults to .tar.lz4 or .tar. If -t is given, only a single database can be snapshotted and the snapshot is written to targetfile, a file on the server which must be somewhere under snapshotdir but which does not have to follow any particular naming convention. No -t? You get the whole <snapshotdir>/<dbname>_<YYYY><MM><DD>T<HH><MM>UTC<snapshotcompression> filename situation. snapshotdir must be configured explicitly using monetdbd set, and snapshotcompression defaults to .tar.lz4 or .tar. With -t, only one database can be snapshotted, and targetfile must be somewhere under snapshotdir — no particular naming convention required.

📸 monetdb snapshot list [dbname ...]

Lists the snapshots for the given databases, or all databases if none is given, showing the snapshot id, the time the snapshot was taken and the (compressed) size of the snapshot file. Only snapshots following the naming convention described under monetdb snapshot create are listed. The snapshot id is of the form dbname@tag where the tags are numbers starting at 1 for the most recent snapshot of a database, 2 for the next most recent, etc. For clarity, the first snapshot for each database shows the full snapshot id (dbname@1) and older snapshots for the same database are listed just as @2, @3, etc. Snapshot id tea: dbname@tag, tags start at 1 for most recent, then 2, 3, etc. First one shows full dbname@1; older ones show just @2, @3.

📸 monetdb snapshot restore [-f] snapshotid [dbname]

Restores a database from the given snapshot, where snapshotid is either a path on the server or name@tag as listed by monetdb snapshot list. The optional dbname argument sets the name of the newly created database. It can be omitted unless snapshotid is a full path. When -f is given, no confirmation is asked when overwriting an existing database. Restore from a server path or name@tag. Optional dbname names the new database, and it can be omitted unless snapshotid is a full path. -f = no confirmation when overwriting an existing database.

  • monetdb snapshot destroy [-f] name@tag [name@tag ...] — okay bestie, this one deletes the listed snapshots from the snapshotdir directory. When -f is given, no confirmation is asked. so it's giving “commit to the bit” energy 💅
  • monetdb snapshot destroy [-f] -r N dbname [dbname ...] — deletes all but the N latest snapshots for the given databases. Again, dbname can be a pattern such as staging* or even * to work on all snapshotted databases. When -f is given, no confirmation is asked. bulk cleanup icon behavior ✨
  • get <all | property[,property[,..]]> [database ...] — prints the requested properties, or all known properties, for the given database. For each property its source and value are printed. Source indicates where the current value comes from, e.g. the configuration file, or a local override. receipts: shown 🧾
  • set property=value [database ...] — sets property to value for the given database(s), or all. For a list of properties, run monetdb get all. Most properties require the database to be stopped when set. yes, stopped — do not try to be slick 😭
    • shared=<yes|no|tag> — defines if and how the database is being announced to other monetdbds or not. If not set to yes or no the database is simply announced or not. Using a string, called tag, the database is shared using that tag, allowing for more sophisticated usage. For information about the tag format and use, see section REMOTE DATABASES in the monetdbd(1) manpage. Note that this property can be set for a running database, and that a change takes immediate effect in the network. network main character energy 🌐
    • nthreads=number — defines how many worker threads the server should use to perform main processing. Normally, this number equals the number of available CPU cores in the system. Reducing this number forces the server to use less parallelism when executing queries, or none at all if set to 1. parallelism vibe check 🔥
    • ncopyintothreads=number — defines the maximum number of worker threads the server should use to perform COPY INTO from a CSV file. The actual number of threads used is never higher than the number of columns, and is 1 if the number of rows is small. Normally, this number is equal to the value of the nthreads property. Using this number forces the server to use more or less parallelism when executing COPY INTO. Note, COPY INTO threads are created in addition to normal worker threads for each COPY INTO query that is being executed and therefore contend for the CPU with other queries. ngl, CPU drama is real 💀
    • optpipe=string — each server operates with a given optimizer pipeline. While the default usually is the best setting, for some experimental uses the pipeline can be changed. See the mserver5(1) manpage for available pipelines. Changing this setting is discouraged at all times. at all times, bestie — leave it alone unless you know exactly why 🙈
    • readonly=<yes|no> — defines if the database has to be started in readonly mode. Updates are rejected in this mode, and the server employs some read-only optimizations that can lead to improved performance. read-only era 🧊
    • nclients=number — sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged. A multiplex-funnel may be more performant, see MULTIPLEX-FUNNEL below. don't let the whole group chat in at once 👀
    • raw_strings=<yes|no> — defines how the server interprets literal strings. See the mserver5(1) manpage for more details. string lore 🧵
    • idletimeout=seconds — set the idle timeout of the server. If a connection is idle for longer than the specified timeout, and it has a transaction open, the connection will be closed. See the mserver5(1) manpage for more details. nap time has consequences 😴
    • loadmodules=module-list — enable the modules in module-list for the given database. The module-list is a comma or space separated list of module names and translates to a --loadmodule=module option to mserver5(1) for each of the modules in the list. modules: loaded, slay 🎀
  • inherit property [database ...] — like set, but unsets the database-local value, and reverts to inherit from the default again for the given database(s), or all. back to factory settings energy 🫶
  • discover [expression] — returns a list of remote monetdbds and database URIs that were discovered by monetdbd(1). All databases listed can be connected to via the local MonetDB Database Server as if they were local databases using their database name. The connection is redirected or proxied based on configuration settings. If expression is given, only those discovered databases are returned for which their URI matches the expression. The expression syntax is described in the section EXPRESSIONS. Next to database URIs the hostnames and ports for monetdbds that allow to be controlled remotely can be found in the discover list masked with an asterisk. These entries can easily be filtered out using an expression (e.g. mapi:monetdb:*) if desired. The control entries come in handy when one wants to get an overview of available monetdbds in e.g. a local cluster. Note that for monetdbd to announce its control port, the mero_controlport setting for that monetdbd must be enabled in the configuration file. discovery mode: unlocked 🦋
  • -h, help [command] — shows general help, or short help for a given command. when in doubt, ask for help 🌸
  • -v, version — shows the version of the monetdb utility. version check, periodt ⭐

✨ EXPRESSIONS ✨

For various options, typically database names, expressions can be used. These expressions are limited shell-globbing like, where the * in any position is expanded to an arbitrary string. The * can occur multiple times in the expression, allowing for more advanced matches. Note that the empty string also matches the *, hence de*mo can return demo as match. To match the literal * character, one has to escape it using a backslash, e.g. \*. globbing but make it fashion 💫

💖 RETURN VALUE 💖

The monetdb utility returns exit code 0 if it successfully performed the requested command. An error caused by user input or database state is indicated by exit code 1. If an internal error in the utility occurs, exit code 2 is returned. vibe check: 0 slayed, 1 user/database oops, 2 internal oops 🔍

🌈 SEE ALSO 🌈

go forth and be iconic 💅✨

Signed off with love: source/version MonetDB, date 2025-12-09 — MONETDB(1) 🫶🎀

📦 where this page comes from