Qadmin: osn-seo-utilities: db-analysis-iscsi-snapshot @ cf7d5275edd5 (approved in the portal)

2026-10-07T05:45:08Z

osn-seo-utilities: db-analysis-iscsi-snapshot @ cf7d5275edd5 (approved in the portal)

New page

''By Steve Umbehocker, CTO, OSNexus · Updated October 3, 2026''

Offline database analysis means running reports, audits and heavy queries against a point-in-time copy of production data on a separate server, so that the production database never sees the load. QuantaStor makes that copy with a snapshot schedule that snapshots every database volume in a pool at the same instant, presents those snapshots to an analysis server over iSCSI, and rotates old copies out on its own. This guide builds the whole nightly loop with the <code><nowiki>qs</nowiki></code> CLI and a short bash script that triggers the schedule, waits for the new snapshots, swaps the old mounts for new ones and verifies them.

== Why it matters ==

Nightly reporting, data-quality checks and ad-hoc analysis all compete with production I/O when they run against the live database. Copying databases to a reporting server every night is slow and doubles the capacity bill. A snapshot avoids both: it is created near-instantly, records metadata rather than copying blocks, and consumes pool space only for blocks that change afterwards.

A snapshot schedule adds two things a hand-rolled snapshot script lacks. All ZFS members of a schedule that live in the same [[Storage Pools|storage pool]] are snapshotted by a single ZFS command, so a database spread across data, log and index volumes is captured as one crash-consistent set rather than several copies taken seconds apart. The schedule also owns rotation, so you never write cleanup code that deletes the wrong snapshot.

== How it works ==

The moving parts on the appliance side are all documented reference features:

* '''[[Snapshot Schedules]]''' take the snapshots, name them <code><nowiki><volume>_GMT<YYYYMMDD>_<HHMMSS></nowiki></code> from the UTC start time of the run, and keep only '''Max Short Term Snapshots''' of each volume in the rotating window.
* '''Triggering''' runs a schedule immediately, in addition to its timed runs. From the CLI it is <code><nowiki>qs snap-schedule-trigger</nowiki></code>, and over the [[QuantaStor REST API Reference Guide|REST API]] it is the <code><nowiki>snapshotScheduleTrigger</nowiki></code> call. Triggering a disabled schedule does nothing, so the schedule must stay enabled.
* '''Lazy cloning.''' A schedule snapshot sits in the Offline state with no iSCSI target until it is needed. Assigning it to a host materializes it: the clone is created, the target is registered and the state moves to Normal. Each volume, snapshots included, gets its own target IQN and presents at LUN 0 (see [[Storage Volumes]]).
* '''Host assignment''' is the LUN masking that lets only the analysis server see the snapshot ([[Hosts and Host Groups]]).

On the analysis server, open-iscsi logs in to the snapshot's target and the filesystem is mounted like any local disk. Because the snapshot is a writable clone (Read/Write is the default access mode for snapshots), the database engine or the filesystem can replay its journal on mount as it would after a power loss. Those writes land in the clone and never touch the production volume.

== Design and sizing ==

Put every volume of one database, or every database you want captured at the same instant, in the '''same storage pool''' and the '''same schedule'''. Atomicity holds per pool, and '''Disable Atomicity''' on the schedule's Advanced Settings tab breaks it into batches, so leave it off for this use. A schedule run also snapshots only members owned by the appliance it runs on.

The snapshots are crash-consistent. If your database needs a cleaner point, the schedule runs <code><nowiki>/var/opt/osnexus/custom/schedule-prestart.sh</nowiki></code> on the appliance at the start of every run, before any snapshot is taken, with <code><nowiki>--name=<schedule name> --id=<schedule id></nowiki></code>. It blocks the run for up to five minutes, which is enough to ask the database to checkpoint.

Retention for an analysis schedule is short. You only need the snapshot being analyzed and the one about to replace it:

{| class="wikitable"
! Setting !! Suggested value !! Reason
|-
| Max Short Term Snapshots (<code><nowiki>--max-snaps</nowiki></code>) || 3 || The mounted snapshot is never the oldest one in the window
|-
| Hourly to quarterly retention (<code><nowiki>--rc-*</nowiki></code>) || 0 || Long-term tags exempt snapshots from rotation, holding space you don't need
|-
| Schedule type || Calendar, one hour per day || The schedule must be enabled for triggers to work, so give it a harmless timed run
|-
| Offset || Different from any replication schedule on the same volumes || A member snapshotted by another schedule within the last 10 seconds is skipped
|}

Capacity cost tracks write volume, not database size: each snapshot holds the blocks overwritten since it was taken, and the mounted clone adds whatever the analysis server writes. Check '''Max Total Source Snapshots''' on the Snapshot Settings tab against pool free space before you commit.

[[File:Guide-db-analysis-iscsi-snapshot-db-snap-schedule-retention.png|frame|center|The Snapshot Settings tab: clear the long-term counts and keep Max Short Term Snapshots small, and watch the Max Total Source Snapshots figure]]

== Setting it up ==

'''1. Create the schedule.''' In the web interface, go to '''Storage Management → Schedules → Snapshot Schedule → Create''', or use the CLI. This schedule runs at 1 AM every day, keeps three snapshots per volume and no long-term copies:

<pre>qs snap-schedule-create --name=db-nightly \
--days=mon,tue,wed,thu,fri,sat,sun --hours=1am \
--volume-list=db-sales,db-finance,db-hr \
--max-snaps=3 --rc-hourly=0 --rc-dailies=0 --rc-weeklies=0 \
--rc-monthlies=0 --rc-quarterlies=0</pre>

[[File:Guide-db-analysis-iscsi-snapshot-db-snap-schedule-interval.png|frame|center|The Schedule Interval tab: tick the days and the single hour the schedule should fire on its own]]

To add another database later, use <code><nowiki>qs snap-schedule-add --schedule=db-nightly --volume-list=db-ops</nowiki></code> or the '''Update Selections''' dialog ([[Snapshot Schedule Add/Remove Volumes]]). <code><nowiki>qs snap-schedule-assoc-list --schedule=db-nightly</nowiki></code> confirms the members.

'''2. Register the analysis server.''' Read its IQN from <code><nowiki>/etc/iscsi/initiatorname.iscsi</nowiki></code> (install <code><nowiki>open-iscsi</nowiki></code> or <code><nowiki>iscsi-initiator-utils</nowiki></code> if it is missing) and add a Host with that IQN, as described in [[ISCSI Initiator Setup]]:

<pre>qs host-add --hostname=analytics1 --host-type=linux \
--iqn=iqn.1994-05.com.redhat:5a66857c49dc</pre>

[[File:Guide-db-analysis-iscsi-snapshot-db-snap-add-host.png|frame|center|The Add Host dialog: set Operating System Type to Linux and paste the server's IQN into iSCSI Initiator]]

The appliance port at 192.0.2.10 in the examples below must have iSCSI enabled, or discovery returns no portals.

'''3. Install the refresh script''' on the analysis server. It needs the <code><nowiki>qs</nowiki></code> CLI pointed at the appliance (the <code><nowiki>QS_SERVER</nowiki></code> variable or <code><nowiki>~/.qs.cnf</nowiki></code>, per the [[QuantaStor CLI Command Reference|CLI reference]]), plus <code><nowiki>jq</nowiki></code> and <code><nowiki>iscsiadm</nowiki></code>:

<pre>#!/bin/bash
set -euo pipefail # usage: refresh-db-snaps.sh [--use-existing-snaps]

SCHEDULE=db-nightly
HOST=analytics1 # Host object holding this server's IQN
PORTAL=192.0.2.10 # iSCSI-enabled port on the appliance
VOLUMES="db-sales db-finance db-hr" # source volumes; each mounts at $MNT/<volume>
MNT=/mnt/dbsnap
PART=-part1 # set to "" if the filesystem uses the whole disk
STATE=/var/lib/dbsnap # which snapshot is mounted where
WAIT=600 # seconds to wait for new snapshots

USE_EXISTING=0
[ "${1:-}" = "--use-existing-snaps" ] && USE_EXISTING=1
mkdir -p "$STATE"

latest() { # names are <volume>_GMTYYYYMMDD_HHMMSS (UTC): last sorted is newest
qs volume-list --json | jq -r '.. | objects | .name? // empty' \
| grep -E "^${1}_GMT[0-9]{8}_[0-9]{6}\$" | sort -u | tail -1 || true
}

declare -A NEW OLD
if [ "$USE_EXISTING" = 1 ]; then
for v in $VOLUMES; do NEW[$v]=$(latest "$v"); done
else
for v in $VOLUMES; do OLD[$v]=$(latest "$v"); done
qs snap-schedule-trigger --schedule="$SCHEDULE"
deadline=$((SECONDS + WAIT))
for v in $VOLUMES; do
while NEW[$v]=$(latest "$v"); [ "${NEW[$v]}" = "${OLD[$v]}" ]; do
[ "$SECONDS" -lt "$deadline" ] || { echo "no new snapshot of $v" >&2; exit 1; }
sleep 10
done
done
fi

changed=0 # remount only if some volume has a newer snapshot than the one mounted
for v in $VOLUMES; do
[ -n "${NEW[$v]}" ] || { echo "no snapshot of $v yet" >&2; exit 1; }
[ "${NEW[$v]}" = "$(cat "$STATE/$v" 2>/dev/null)" ] || changed=1
done
[ "$changed" = 1 ] || { echo "snapshots unchanged, mounts left in place"; exit 0; }

for v in $VOLUMES; do
# Detach the previous snapshot: unmount, log out, remove the assignment.
if [ -f "$STATE/$v" ]; then
old=$(cat "$STATE/$v"); oldiqn=$(cat "$STATE/$v.iqn")
if mountpoint -q "$MNT/$v"; then umount "$MNT/$v"; fi
iscsiadm -m node -T "$oldiqn" -p "$PORTAL" --logout || true
iscsiadm -m node -T "$oldiqn" -p "$PORTAL" -o delete || true
qs volume-unassign --volume="$old" --host-list="$HOST" || true
rm -f "$STATE/$v" "$STATE/$v.iqn"
fi

# Attach the new one. Assigning it materializes the lazy clone and its target.
snap=${NEW[$v]}
qs volume-assign --volume="$snap" --host-list="$HOST"
iqn=$(qs volume-get --volume="$snap" --json \
| jq -r '.. | objects | .iqn? // empty' | head -1)
iscsiadm -m discovery -t sendtargets -p "$PORTAL" >/dev/null
iscsiadm -m node -T "$iqn" -p "$PORTAL" --login
dev=/dev/disk/by-path/ip-$PORTAL:3260-iscsi-$iqn-lun-0$PART
for _ in $(seq 30); do [ -b "$dev" ] && break; sleep 1; done
mkdir -p "$MNT/$v"
mount "$dev" "$MNT/$v"

# Verify: mounted and not empty, then record it for the next run.
mountpoint -q "$MNT/$v" && [ -n "$(ls -A "$MNT/$v")" ] \
|| { echo "mount check failed for $snap" >&2; exit 1; }
echo "$snap" > "$STATE/$v"; echo "$iqn" > "$STATE/$v.iqn"
echo "$v -> $snap mounted at $MNT/$v"
done</pre>

<code><nowiki>set -e</nowiki></code> stops the script if an unmount fails, which is what you want when a report still has files open: the old snapshot stays mounted and nothing is half-swapped.

== Operating and testing ==

Choose one of two timing models:

* '''Script-driven.''' Cron runs <code><nowiki>refresh-db-snaps.sh</nowiki></code> when you want the copy, for example after the nightly ETL load finishes. The trigger makes the snapshots, and the script waits up to <code><nowiki>WAIT</nowiki></code> seconds for a new name to appear on every volume.
* '''Schedule-driven.''' The schedule's own 1 AM run makes the snapshots and cron runs <code><nowiki>refresh-db-snaps.sh --use-existing-snaps</nowiki></code> at 1:30 AM. The script compares the newest snapshot names with what it last mounted and exits without touching the mounts if nothing is new. This is also the safe way to re-run the script by hand during the day.

[[File:Guide-db-analysis-iscsi-snapshot-db-snap-host-assignments.png|frame|center|The Hosts view: the analysis server's initiator IQN, and in Storage Volume Assignments, the volumes currently assigned to it]]

To test, run the script by hand once and check four things:

# <code><nowiki>qs volume-list</nowiki></code> shows a new <code><nowiki>db-sales_GMT...</nowiki></code> snapshot for each volume, all with the same timestamp.
# <code><nowiki>qs volume-assign-list --host=analytics1</nowiki></code> lists exactly one snapshot per source volume.
# <code><nowiki>iscsiadm -m session</nowiki></code> shows one session per snapshot target.
# The database engine starts against <code><nowiki>/mnt/dbsnap</nowiki></code> and your reports return yesterday's numbers.

Run it a second time with <code><nowiki>--use-existing-snaps</nowiki></code> and confirm it reports the snapshots as unchanged. If no new snapshot ever appears, <code><nowiki>qs snap-schedule-get --schedule=db-nightly</nowiki></code> shows whether the schedule is enabled. A schedule left with no members disables itself.

Old snapshots need no cleanup code. Once the script has unassigned a snapshot ([[Remove Storage Assignment]] is the equivalent in the web interface), the schedule rotates it out when it falls outside the window. To keep one copy for an audit, put a hold on it with <code><nowiki>qs volume-hold-add --volume=<snapshot> --hold-tag=keep-for-audit</nowiki></code>. A held snapshot is excluded from schedule cleanup until the hold is released.

== FAQ ==

=== Is a snapshot taken by the schedule consistent across several database volumes? ===

Yes, for volumes in the same storage pool. The schedule snapshots all of its ZFS members in a pool with a single ZFS command, so they share one point in time and are crash-consistent with each other. Volumes in different pools, or a schedule with Disable Atomicity ticked, don't get that guarantee.

=== Does running analysis on the snapshot slow down the production database? ===

The analysis reads come from the snapshot's clone, which shares unchanged blocks with the production volume in the same pool. The analysis server's queries therefore still use the pool's disks, but none of the work runs inside the production database engine, and writes to the clone never reach the production volume. If pool I/O is a concern, schedule the refresh and heavy reports outside peak hours.

=== What does --use-existing-snaps do? ===

It skips the <code><nowiki>snap-schedule-trigger</nowiki></code> call and works with snapshots the schedule has already made. If the newest snapshot of every volume is the one already mounted, the script exits without unmounting anything. Otherwise it swaps in the newer snapshots exactly as a normal run would.

=== Can I trigger the schedule over the REST API instead of the CLI? ===

Yes. The <code><nowiki>snapshotScheduleTrigger</nowiki></code> call takes the schedule name or ID and signals the schedule manager to run it immediately, the same as <code><nowiki>qs snap-schedule-trigger</nowiki></code>. The CLI is the simpler choice for a bash script, and the API suits tools that already speak JSON-RPC.

=== How much pool space do the snapshots use? ===

Each snapshot holds only the blocks overwritten in production since it was taken, plus anything the analysis server writes to its clone. With Max Short Term Snapshots at 3 and no long-term retention, you hold roughly three days of change per volume.

----
''Part of the [[Guides:Index|QuantaStor Guides]] series. For reference documentation, see the [[Main Page|QuantaStor documentation]].''

[[Category:Guides]]

Guides:Nightly Database Analysis from iSCSI Snapshots - Revision history

Qadmin: osn-seo-utilities: db-analysis-iscsi-snapshot @ cf7d5275edd5 (approved in the portal)