Checklists for database administrators #

This page is for database administrators who want the steps of each routine on one page. Copy a list into your ticket or runbook; each item links to the page that explains it.

Contents #

1. Before an install
2. Go-live
3. Daily
4. Weekly
5. Monthly and quarterly
6. Before and after an upgrade
7. Restore
8. Engine move
9. Security review

Before an install #

• To do: Engine chosen (Choosing an engine); version at or above the practical minimum (Versions).
• To do: PHP extension loaded in the web PHP, the command line PHP and Velocity's PHP (PHP extensions).
• To do: Database created in UTF-8 (Character sets).
• To do: Account created with the least privileges that work (Accounts).
• To do: PostgreSQL: pgcrypto available. MongoDB: library installed. Oracle: oci8 and the client found.
• To do: Network access only from the web servers (Securing).

Go-live #

• To do: Setup > System information shows the expected engine, version and UTF-8.
• To do: No root or superuser in settings/override/site.ini.append.php; the file is mode 640 and not in version control.
• To do: SQLite: ownership set up, the file not downloadable (SQLite permissions).
• To do: MongoDB: create_indexes.js run (Setting up MongoDB).
• To do: Backups scheduled and one restore tested (Backups, Restoring).
• To do: Cronjobs scheduled; session_gc.php added with database sessions (Housekeeping).
• To do: Monitoring and alerts in place (Monitoring).
• To do: Debug output off for the public.

Daily #

• To do: Backup ran; its size is plausible; SQLite backup passes integrity_check.
• To do: var/log/error.log has no new database errors.
• To do: ezpending_actions is not growing.
• To do: MongoDB: no new MONGO TODO.

Weekly #

• To do: SQLite: PRAGMA optimize, quick_check, -wal size (SQLite maintenance).
• To do: Engine statistics current (PostgreSQL autovacuum, Oracle gather-stats).
• To do: Disk space for database, logs and backups.
• To do: Setup > Cronjobs shows runs without issues.

Monthly and quarterly #

• To do: Table sizes reviewed (Tables); old versions pruned.
• To do: Engine and PHP extension still in vendor security support.
• To do: Restore tested on another machine (quarterly).
• To do: Accounts and network rules reviewed.

Before and after an upgrade #

• To do: Version row read (Upgrades).
• To do: Backup taken; maintenance on.
• To do: Update files of your engine and path applied in order, each once; no 6.12/, 7.2/, 7.3/, unstable/.
• To do: Cluster update files where the cluster is used.
• To do: 6.0.15: createaudittables.php, movetrashrecords.php (6.0.15 update).
• To do: exp:checkdbfiles prints nothing; upgrade check lists no missing table (Checking the schema).
• To do: Caches cleared after the PHP-FPM reload or Velocity restart; test publish done; maintenance off.

Restore #

• To do: Maintenance on; Velocity stopped.
• To do: Database and files from the same moment restored.
• To do: All caches cleared, query cache and Velocity's response cache included.
• To do: Sequences, search index, MongoDB indexes checked.
• To do: Version row, row counts, sign-in and test publish checked; maintenance off.

Engine move #

• To do: Rehearsal on a copy; window agreed (Moving engines).
• To do: Extensions reviewed for the target engine (Extension SQL).
• To do: Dump and insert completed; settings switched; caches cleared; workers restarted.
• To do: Row counts match; consistency check clean; search index rebuilt.
• To do: Backups and monitoring switched to the new engine.

Security review #

• To do: One account per site, no administrative accounts (Accounts).
• To do: Credentials only in settings/override/ (and the cluster files); not in history, crontabs or scripts.
• To do: Database port reachable from the web servers only; TLS where it crosses machines.
• To do: Backups encrypted and access-controlled.
• To do: Debug output limited to administrators' addresses.

Related pages #

• Database administrators: start here
• Monthly operations, Security review (for hosting companies)
• Database FAQ
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.