Database Backup and Diagnostics Manual

Identify engine, instance, database and backup destination first. A backup file is not proof of recoverability. This guide connects consistency, coverage and isolated restore validation. Application-internal migrations/deletions belong to supported application tools.

Task	Start here
Back up SQLite	WAL and online backup
Dump/restore PostgreSQL	Database and globals
MySQL / MariaDB	Snapshot preconditions
Is automation recoverable?	Restore drills and retention

Do not copy only the live main database file

Identify the database and schema read-only

# Verify the intended SQLite file
sqlite3 -readonly /var/lib/app/app.db '.databases'

Verify the main path. -readonly prevents accidental creation at a wrong path; keep the same source throughout.

# Inspect journaling mode and backup boundaries
sqlite3 -readonly /var/lib/app/app.db 'PRAGMA journal_mode;'

In wal mode, committed data may still reside in the WAL. Do not copy only the main file or delete the WAL to tidy up.

# Discover real table names
sqlite3 -readonly /var/lib/app/app.db '.tables'

Inspect .schema TABLE_NAME separately using a discovered name. Dot commands belong to the CLI, not SQL drivers.

Replace the path. -readonly avoids accidentally creating an empty database on a typo. Dot commands belong to the CLI, not SQL. Committed WAL data may not be in the main file; copying only it can omit data or be inconsistent. Do not delete active -wal/-shm files.

Create a consistent copy in a new restricted directory

# Create an unused restricted directory for this backup
umask 077
mkdir -m 700 ./db-review

Run in a controlled local directory. If db-review exists, choose a new name and update later paths. umask affects subsequent new files in this shell.

# Back up the live database through SQLite’s backup API
sqlite3 -readonly /var/lib/app/app.db <<'SQLITE'
.bail on
.timeout 5000
.backup './db-review/app-backup.db'
SQLITE

The heredoc contains CLI commands. .bail exits on error; .timeout waits for locks in milliseconds. Check exit status and reject failed copies. A read-only source can still write the backup destination.

# Check the copy’s structural integrity
sqlite3 -readonly ./db-review/app-backup.db 'PRAGMA integrity_check;'

Expect ok. Preserve errors and investigate/retry rather than accepting mere file existence.

# Check violations of declared foreign keys
sqlite3 -readonly ./db-review/app-backup.db 'PRAGMA foreign_key_check;'

No rows means no declared-key violations, not all business invariants. Check representative records and run the application against the copy in isolation.

Use a new destination rather than overwriting the only verified copy. .backup uses the backup API but can encounter lock contention; check exit status. integrity_check expects ok and foreign_key_check no violations. Neither proves application-level completeness.

A database dump is not the whole cluster

PostgreSQL: verify connection profiles first

Configure service=app for the source database and protected credentials first; restore must point to a separate isolated instance. Write exports into a fresh restricted directory without overwriting prior backups.

# Verify source database, user, and server version
psql 'service=app' -X -c 'SELECT current_database(), current_user, version();'

-X skips psqlrc. Ensure pg_dump compatibility, especially not using an older major client against a newer server.

# Export one database as a selectable custom archive
pg_dump --dbname='service=app' --format=custom --file=app.dump

Check exit status and size. Use pg_restore for custom format, not psql redirection. Inspect lock/DDL errors.

# Export roles and tablespaces separately
pg_dumpall --database='service=app' --globals-only --file=globals.sql

Requires access and can include sensitive role data. Do not automatically replay globals into a test instance; review role conflicts, paths, and privileges.

# Inspect the archive’s object inventory
pg_restore --list app.dump

Verify expected schemas, tables, sequences, and functions. A readable inventory is not a successful restore.

Configure service=app in pg_service.conf and protected credentials such as pgpass (0600 on Unix). pg_dump provides a database-consistent view while normal DML continues, but DDL/locks can contend. It excludes cluster roles, tablespaces, server config and a continuous WAL recovery chain. Protect the globals export too.

Create a fresh database on the isolated instance

# Recheck restore-instance identity before writing
psql 'service=restore' -X -c 'SELECT inet_server_addr(), inet_server_port(), current_database(), current_user;'

Verify host/socket configuration and ensure it is not production. inet_server_addr may be NULL for Unix sockets.

# Create a new empty database in isolation
createdb --maintenance-db='service=restore' app_restore

If it exists, inspect rather than dropping it. Keep the same name in restore and verification.

# Restore into the new database, stopping on error
pg_restore --dbname='service=restore dbname=app_restore' \
  --exit-on-error --no-owner --no-privileges app.dump

These flags deliberately omit original ownership/ACL. Plan those for real recovery. Failure may leave partial objects; preserve logs and retry in a fresh test database.

# Confirm that verification queries use the restored database
psql 'service=restore dbname=app_restore' -X -c 'SELECT current_database();'

Follow with project read-only data/constraint checks, extensions, sequences, and application compatibility; record elapsed restore time. Database identity alone is not acceptance.

Inspect waits before canceling connections

# Identify long transactions and wait types read-only
psql 'service=app' -X -c "SELECT pid, state, wait_event_type, wait_event, xact_start, query_start FROM pg_stat_activity WHERE datname = current_database() ORDER BY xact_start NULLS LAST;"

Interpret state, transaction age, and waits together. Query cancellation and session termination have different effects; choose only after identifying the PID and intent.

restore must be an isolated test instance. This example assigns restored objects to the restoring user and omits privileges; a full recovery must plan roles, ownership, ACLs, extensions and version compatibility. Avoid --clean over a live database. Diagnose waits and long transactions in pg_stat_activity before cancelling queries or terminating sessions.

single-transaction depends on the storage engine

MySQL: prepare source and restore login paths

This uses Oracle MySQL clients. Configure app and restore for different instances with mysql_config_editor and interactive passwords. For MariaDB use matching clients and protected option files, not MySQL-only flags.

# Verify the source database, identity, and version
mysql --login-path=app --database=app -e 'SELECT DATABASE(), CURRENT_USER(), VERSION();'

--database fixes the source instead of leaving DATABASE() NULL.

# Check table engines for transactional snapshot support
mysql --login-path=app -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='app' AND TABLE_TYPE='BASE TABLE';"

Nontransactional tables such as MyISAM need another consistency strategy, such as planned locking or physical backup.

# Export transactional data and program objects for standalone restore
mysqldump --login-path=app --single-transaction \
  --routines --events --triggers --set-gtid-purged=OFF app > app.sql

Use a restricted directory and require success. GTID OFF suits this standalone test, not replication recovery. Review DDL, DEFINER, privileges, and character sets.

Verify another instance before restoring

# Verify the isolated restore connection
mysql --login-path=restore -e 'SELECT @@hostname, @@port, CURRENT_USER(), VERSION();'

Check login-path host/socket too; hostname alone does not prove isolation.

# Create a fresh restore database without overwriting an existing one
mysql --login-path=restore -e 'CREATE DATABASE app_restore;'

Adapt charset/collation to the source. Stop if the name exists.

# Import reviewed SQL only into the isolated database
mysql --login-path=restore app_restore < app.sql

Inspect exit status/logs and validate tables, data, programs, events, and the application. Review SQL for USE/CREATE/DEFINER statements that affect scope/privileges; do not use --force to hide errors.

Configure login-path first. On MariaDB use the installed client/dump and protected option-file mechanism. single-transaction is consistent for transactional engines such as InnoDB, not MyISAM; avoid relevant DDL during dumping. Check routines, events, triggers, charset, privileges, GTID and version compatibility. Logical dumps are not binlog point-in-time recovery.

Validate by restoring into an independent environment

Automation evidence	Why it matters
Start/end, exit status, size, checksum	Detect failures/empty files/transfer corruption, not a substitute for restore
Instance, database, version, coverage	Avoid wrong database or omitted roles/config
Restore duration and business checks	Assess recovery time and recoverable point

A systemd timer job should use a unique temporary file, verify success, rename on the same filesystem and retain an independent copy. Never replace the last valid backup on failure; apply retention after verification. Grafana also needs configuration, plugins and its encryption secret; do not edit internal tables.

SQLite backup · SQLite CLI · PostgreSQL dumps · MySQL mysqldump