Is there any way to run a user-defined function at DB startup (Enterprise mode)?

JoshLitt

I've searched the docs and didn't find anything, but thought I'd check here for ideas.

As the subject line says, I'm looking for a way to run a function/scripting at DB startup to make config changes to the DB.

We have a hands-off embedded Vertica use case where we'd like to ensure some settings are updated each time Vertica starts. For the most part we control the Vertica startup via scripts, but there are times it might be started manually via admintools. Ideally we would trigger a script to run at startup to check the filesystem for a config file and update some settings (this could be TLS ciphers, resource pools, etc.)

I was looking for a something like the Stored Procedure Scheduler which would be event-based, rather than just time-based. Interested in any ideas.

mosheg

Vertica does not provide a built-in event-driven startup hook to execute scripts automatically when the database starts.
However, the provided Bash script can be used to detect when Vertica starts by monitoring the database logs and triggering a function.

cat vertica_startup_trigger.sh

#!/bin/bash

# Path to Vertica startup log file, found via: /opt/vertica/bin/admintools -t list_db -d YourDatabaseName
STARTUP_LOG="/CATALOG/YourDatabaseName/v_YourDatabaseName_node0001_catalog/startup.log"

# Function to trigger when Vertica starts
on_vertica_start() {
    echo "Vertica started at: $(date)"
    vsql -c "select 'CALL your_post_startup_procedure()' Vertica_DB_Startup_trigger from dual;"  # Replace with the actual procedure
}

# Initial check in case Vertica is already running
if grep -q '"stage" : "Startup Complete",' "$STARTUP_LOG"; then
    on_vertica_start
fi

# Watch for modifications on the startup log and trigger the script only when needed
inotifywait -m -e MODIFY "$STARTUP_LOG" --format "%w%f" |
while read FILE; do
    if grep -q '"stage" : "Startup Complete",' "$FILE"; then
        on_vertica_start
    fi
done

Replace the "STARTUP_LOG" with your real Vertica startup log file path.
Vertica startup log file path is shown with: admintools -t list_db -d YourDatabaseName

One prerequisite is to ensure that the inotify-tools package is installed:

sudo apt-get install inotify-tools   # Debian/Ubuntu
sudo yum install inotify-tools         # RHEL/CentOS