Appendices
Appendix J. Database Administration Commands
The administration command file: Chapter 21's (and 14's, 20's, 21's) operational commands organized by task, both platforms side by side. See Appendix B and C for each platform's fuller references.
Server and service
| Task | PostgreSQL | MySQL |
|---|
| Status | systemctl status postgresql | systemctl status mysql / mysqld |
| Start / stop / restart | systemctl start/stop/restart postgresql | same, mysql |
| Reload config | SELECT pg_reload_conf(); or pg_ctl reload | SET PERSIST survives; some need restart |
| Version | SELECT version(); | SELECT VERSION(), @@version_comment; |
| Uptime / activity | SELECT * FROM pg_stat_activity; | SHOW PROCESSLIST; / sys.session_view |
Users, roles, privileges (see Chapter 20)
-- PostgreSQL
CREATE ROLE app LOGIN PASSWORD '...';
GRANT SELECT, INSERT ON enrollment TO app;
GRANT EXECUTE ON FUNCTION enroll_student TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO report;
\du -- inventory
-- MySQL
CREATE USER 'app'@'host' IDENTIFIED BY '...';
GRANT SELECT, INSERT ON university.enrollment TO 'app'@'host';
SHOW GRANTS FOR 'app'@'host';
ALTER USER 'app'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;
Backup and restore (see Chapter 21)
| Task | PostgreSQL | MySQL |
|---|
| Logical dump | pg_dump -Fc db > f.dump | mysqldump --single-transaction --routines --triggers db > f.sql |
| Parallel dump | pg_dump -Fd -j 4 -f dir/ | mysqlsh util.dumpInstance |
| Restore | pg_restore -d db -j 4 f.dump | mysql db < f.sql |
| Globals | pg_dumpall --globals-only | (roles are in mysql schema dumps) |
| Physical base | pg_basebackup -D dir/ | clone plugin / XtraBackup |
| PITR tape | WAL archive + recovery.signal | binary log + mysqlbinlog replay |
Maintenance
| Task | PostgreSQL | MySQL |
|---|
| Statistics | ANALYZE [table]; | ANALYZE TABLE t; |
| Reclaim / rebuild | VACUUM / VACUUM FULL [table] | purge (auto) / OPTIMIZE TABLE t |
| Rebuild indexes | REINDEX TABLE t; | (OPTIMIZE / force rebuild) |
| Autopilot | autovacuum (tune thresholds) | purge threads (automatic) |
| Table check | (no direct analog; use pgcheck-class tooling) | CHECK TABLE t; |
Monitoring (see Sections 21.9 and 19.11)
| What | PostgreSQL | MySQL |
|---|
| Running queries | pg_stat_activity | SHOW PROCESSLIST / sys.processlist |
| Top statements | pg_stat_statements | sys.statements_with_runtimes_in_95th_percentile |
| Cache hit ratio | pg_stat_database (blks_*) | SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%' |
| Locks / deadlocks | pg_locks, pg_stat_database.deadlocks | performance_schema.data_locks, SHOW ENGINE INNODB STATUS |
| Table bloat / churn | pg_stat_user_tables.n_dead_tup | information_schema.INNODB metrics |
| Sizes | pg_database_size(), pg_total_relation_size() | information_schema.TABLES sums |
| Replication | pg_stat_replication (lag) | SHOW REPLICA STATUS |
Configuration quick reference
| Setting | PostgreSQL | MySQL |
|---|
| Memory cache | shared_buffers (~25% RAM), work_mem | innodb_buffer_pool_size (~50–70% dedicated) |
| Connections | max_connections | max_connections |
| Slow query log | log_min_duration_statement | slow_query_log, long_query_time |
| Logging | logging_collector, log_destination | error / general / slow logs |
| Auth policy | pg_hba.conf methods | user@host + plugin |
| TLS required | hostssl lines | require_secure_transport |
The routine calendar (Chapter 21.11)
Daily: backup verified (restore-to-scratch + counts), log scan, disk/lag alerts. Weekly: bloat review, top-query review, capacity trend. Monthly: restore drill, security checklist (Chapter 20), patch review. Quarterly: PITR/restore rehearsal, configuration review. Each item is a script with a schedule — never a memory.