Stopping the database

There are many occasions when you must stop a database, for example, before an upgrade or performing various maintenance tasks.

There are many occasions when you must stop a database, for example, before an upgrade or performing various maintenance tasks. You can stop a running database through one of the following:

Management Console
Administration Tools interface
Vertica functions
Command line

You cannot stop a running database if any users are connected or Database Designer is building or deploying a database design.

Administration tools

To stop a running database with admintools:

Verify that all cluster nodes are up. If any nodes are down, identify and restart them.
Close all user sessions:
- Identify all users with active sessions by querying the SESSIONS system table. Notify users of the impending shutdown and request them to shut down their sessions.
- Prevent users from starting new sessions by temporarily resetting configuration parameter MaxClientSessions to 0:
```
=> ALTER DATABASE DEFAULT SET MaxClientSessions = 0;
```
- Close all remaining user sessions with Vertica functions CLOSE_SESSION and CLOSE_ALL_SESSIONS.
Note
You can also force a database shutdown and block new sessions with the function SHUTDOWN.
Open Vertica Administration Tools.
From the Main Menu:
- Select Stop Database
- Click OK
Select the database to stop and click OK.
Enter the password (if asked) and click OK.
When prompted that database shutdown is complete, click OK.

Vertica functions

You can stop a database with the SHUTDOWN function. By default, the shutdown fails if any users are connected. To force a shutdown regardless of active user connections, call SHUTDOWN with an argument of true:

=> SELECT SHUTDOWN('true');
      SHUTDOWN
-------------------------
Shutdown: sync complete
(1 row)

In Eon Mode databases, you can stop subclusters with the SHUTDOWN_SUBCLUSTER and SHUTDOWN_WITH_DRAIN functions. SHUTDOWN_SUBCLUSTER shuts down subclusters immediately, whereas SHUTDOWN_WITH_DRAIN performs a graceful shutdown that drains client connections from subclusters before shutting them down. For more information, see Starting and stopping subclusters.

The following example demonstrates how you can shut down all subclusters in an Eon Mode database using SHUTDOWN_WITH_DRAIN:

=> SELECT SHUTDOWN_WITH_DRAIN('', 0);
NOTICE 0:  Begin shutdown of subcluster (default_subcluster, analytics)
                SHUTDOWN_WITH_DRAIN
-----------------------------------------------------------------------
Shutdown message sent to subcluster (default_subcluster, analytics)

(1 row)

Command line

You can stop a database with the admintools command stop_db:

$ $ admintools -t stop_db --help
Usage: stop_db [options]

Options:
  -h, --help            Show this help message and exit.
  -d DB, --database=DB  Name of database to be stopped.
  -p DBPASSWORD, --password=DBPASSWORD
                        Database password in single quotes.
  -F, --force           Force the database to shutdown, even if users are
                        connected.
  -z, --if-no-users     Only shutdown if no users are connected. If any users
                        are connected, exit with an error.
  -n DRAIN_SECONDS, --drain-seconds=DRAIN_SECONDS
                        Eon db only: seconds to wait for user connections to
                        close. Default value is 60 seconds. When the time
                        expires, connections will be forcibly closed and the
                        db will shut down.
  --timeout=NONINTERACTIVE_TIMEOUT
                        Set a timeout (in seconds) to wait for actions to
                        complete ('never') will wait forever (implicitly sets
                        -i).
  -i, --noprompts       Do not stop and wait for user input(default false).
                        Setting this implies a timeout of 20 min.

Note

You cannot use both the -z (--if-no-users) and -F (or --force) options in the same stop_db call.

stop_db behavior depends on whether it stops an Eon Mode or Enterprise Mode database.

Stopping an Eon Mode database

In Eon Mode databases, the default behavior of stop_db is to call SHUTDOWN_WITH_DRAIN to gracefully shut down all subclusters in the database. This graceful shutdown process drains client connections from subclusters before shutting them down.

The stop_db option -n (--drain-seconds) lets you specify the number of seconds to wait—by default, 60—before forcefully closing client connections and shutting down all subclusters. If you set a negative -n value, the subclusters are marked as draining but do not shut down until all active user sessions disconnect.

In the following example, the database initially has an active client session, but the session closes before the timeout limit is reached and the database shuts down:

$ admintools -t stop_db -d verticadb --password password --drain-seconds 200
Shutdown will use connection draining.
Shutdown will wait for all client sessions to complete, up to 200 seconds
Then it will force a shutdown.
Poller has been running for 0:00:00.000025 seconds since 2022-07-27 17:10:08.292919


------------------------------------------------------------
client_sessions     |node_count          |node_names
--------------------------------------------------------------
0                   |5                   |v_verticadb_node0005,v_verticadb_node0006,v_verticadb_node0003,v_verticadb_node0...
1                   |1                   |v_verticadb_node0001
STATUS: vertica.engine.api.db_client.module is still running on 1 host: nodeIP as of 2022-07-27 17:10:18. See /opt/vertica/log/adminTools.log for full details.
Poller has been running for 0:00:11.371296 seconds since 2022-07-27 17:10:08.292919

...

------------------------------------------------------------
client_sessions     |node_count          |node_names
--------------------------------------------------------------
0                   |5                   |v_verticadb_node0002,v_verticadb_node0004,v_verticadb_node0003,v_verticadb_node0...
1                   |1                   |v_verticadb_node0001
Stopping poller drain_status because it was canceled
Shutdown metafunction complete. Polling until database processes have stopped.
Database verticadb stopped successfully

If you use the -z (--if-no-users) option, the database shuts down immediately if there are no active user sessions. Otherwise, the stop_db command returns an error:

$ admintools -t stop_db -d verticadb --password password --if-no-users
Running shutdown metafunction. Not using connection draining

             Active session details
| Session id                        | Host Ip        | Connected User |
| ------- --                        | ---- --        | --------- ---- |
| v_verticadb_node0001-107720:0x257 | 192.168.111.31 | analyst        |
Database verticadb not stopped successfully for the following reason:
Shutdown did not complete. Message: Shutdown: aborting shutdown
Active sessions prevented shutdown.
Omit the option --if-no-users to close sessions. See stop_db --help.

You can use the -F (or --force) option to shut down all subclusters immediately, without checking for active user sessions or draining the subclusters.

Stopping an Enterprise Mode database

In Enterprise Mode databases, the default behavior of stop_db is to shut down the database only if there are no active sessions. If users are connected to the database, the command aborts with an error message and lists all active sessions. For example:

$ /opt/vertica/bin/admintools -t stop_db -d VMart
Info: no password specified, using none

        Active session details
| Session id                     | Host Ip       | Connected User |
| ------- --                     | ---- --       | --------- ---- |
| v_vmart_node0001-91901:0x162   | 10.20.100.247 | analyst        |
Database VMart not stopped successfully for the following reason:
Unexpected output from shutdown: Shutdown: aborting shutdown
NOTICE: Cannot shut down while users are connected

You can use the -F (or --force) option to override user connections and force a shutdown.