Repository navigation
Description
Problem
On large PasarGuard installations, node_user_usages and node_usages dominate both backup size and restore time. On one production panel node_user_usages had ~247M rows; a normal backup is hundreds of MB and grows forever. Most operators only need accounts, groups, hosts, nodes and settings for disaster recovery, not the full per-node traffic history.
--ignore-table alone is not enough because it also drops the CREATE TABLE, so the restored panel is missing the usage tables and the usage jobs fail.
Proposal
Add a first-class light backup mode to pasarguard backup-service:
- Exclude only the rows of
node_user_usagesandnode_usages. - Keep their schema (
CREATE TABLE, indexes, foreign keys) so the panel works after restore. - Show it in the menu as an option (e.g.
7. Toggle light backup [ON/OFF]). - Enable it by default for new installs and keep the choice across script updates, with a clear opt-out to a full backup.
- Make it work for the standard MySQL/MariaDB paths (and custom DB mode if possible).
Implementation sketch
- Add
--ignore-table=${db_name}.node_user_usages --ignore-table=${db_name}.node_usagesto the MySQL/MariaDB dump invocations. - After the data dump, append a schema-only dump for those two tables:
mariadb-dump --no-data --skip-add-drop-table --skip-comments --compact --no-create-db "$db" node_user_usages node_usages
then wrapCREATE TABLEwithCREATE TABLE IF NOT EXISTS, remove the old-- Dump completed on ...footer and append a fresh one so the restore validator still passes. - Store the state in a persistent flag (e.g.
/etc/pasarguard/backup-light), create it by default, and have bothbackup_serviceandbackup_commandread it. - Document that restoring over an existing database keeps old usage rows (the dump intentionally excludes them); restoring into a fresh/dropped database is the supported path.
This should compose with PR #40 (--single-transaction for non-locking MySQL dumps), which also touches the same 7 dump calls.
Testing / reference
I built and tested a standalone installer/patch for PasarGuard 5.5.1 on a test panel:
https://github.com/Aknuun/pasarguard-light-backup
- Light archive: 0
INSERTstatements for both usage tables,CREATE TABLEfor both, valid completion marker. DROP DATABASE pasarguard+ restore succeeded: panel 200, node connected, 2 users / 9 hosts restored, usage tables empty.- Existing test archive was ~63 KB instead of ~852 KB full, mostly because the two usage tables were excluded.
Happy to open a PR against lib/pasarguard-backup.sh if maintainers want to take this path. If the default-on choice is controversial, at minimum exposing the toggle would already help large installations.
Activity
Aknuun commentedon Oct 29, 2026
Implemented for all engines in #44: MySQL/MariaDB (ignore-table + schema append), PostgreSQL/TimescaleDB (
--exclude-table-data), and SQLite (snapshot + delete +VACUUM). Option 7 inpasarguard backup-service, state kept in/etc/pasarguard/backup-light.Tested on a real 2.28 GB SQLite panel DB (snapshot 116 MB / archive 40 MB, 0 usage rows) and MySQL 9.7.4 (64 KB archive, schema preserved). The companion installer enables it automatically: https://github.com/Aknuun/pasarguard-light-backup