Databases core

Use this checklist to verify database backups are configured, running, and restorable. Covers backup schedule, retention, restore test, and access control. Run periodically and before major changes or go-live.

Back up MySQL or MariaDB with mysqldump for logical backups or use filesystem snapshots with FLUSH TABLES WITH READ LOCK for consistent physical backup. Use this when setting up backup jobs or when you need to restore a database.

Create a MySQL user with CREATE USER and grant privileges with GRANT on databases, tables, or global. Restrict by host (e.g. 'app'@'10.0.0.%'). Use this when onboarding an application or implementing least privilege access to MySQL or MariaDB.

Install MySQL 8 or MariaDB on Debian, Ubuntu, or RHEL using the official or distro repository. Secure the installation with mysql_secure_installation; create a database and user. Use this when setting up a new database server or when you need MySQL-compatible storage.

Restore a MySQL or MariaDB database from a mysqldump file using the mysql client. Create the database first if needed. Use this when recovering from a failure or when cloning a database from a logical backup.

Back up PostgreSQL with pg_dump for logical backups (single DB or full cluster) or pg_basebackup for physical backups. Use WAL archiving for point-in-time recovery. Use this when setting up backup jobs or when you need to restore a database or cluster.

Configure PostgreSQL via postgresql.conf (memory, connections, WAL) and pg_hba.conf (who can connect and how). Reload or restart after changes. Use this when tuning performance, enabling remote access, or locking down authentication.

Create a PostgreSQL role (user) with CREATE USER or CREATE ROLE; grant privileges with GRANT on databases, schemas, and tables. Use LOGIN for interactive users and NOLOGIN for app roles. Use this when onboarding a new user or service account or when implementing least privilege.

Install PostgreSQL on Debian, Ubuntu, or RHEL using the official or distro package repository. Configure the data directory, init the cluster, and start the server so you can create databases and users. Use this when setting up a new database server or when you need a specific PostgreSQL version.

Restore from a pg_dump custom-format file with pg_restore, or from a plain SQL dump with psql. Drop or create the target database first if replacing; restore globals with psql if you have pg_dumpall output. Use this when recovering from a failure or when cloning a database.

Use this checklist before putting a database into production: backups, users, security, config, monitoring, and restore test. Ensures nothing is missed and the database is ready for production load and recovery.

Harden database access: least-privilege users, network restriction, encryption in transit and at rest, and audit logging. Use this when deploying a new database or when reviewing security for an existing PostgreSQL or MySQL instance.

Define how long to keep database backups based on RTO, RPO, and compliance. Use full plus incremental or differential; test restore regularly. Use this when setting or reviewing backup retention.

Store database connection strings in environment variables or a secrets manager; never commit them to source control. Use least-privilege users and SSL. Use this when deploying apps that connect to a database.

MySQL and MariaDB use my.cnf or my.ini for configuration. Key settings include datadir, port, bind-address, max_connections, and buffer pool. Use this when tuning or securing the server.

Configure PostgreSQL logging with log_destination, log_directory, and log_filename. Use log_min_duration_statement for slow queries and log_connections for audit. Use this when debugging or meeting audit requirements.

VACUUM reclaims dead tuple space and updates visibility for the planner. Run VACUUM ANALYZE after bulk changes. Rely on autovacuum; tune if needed. Use this when you see bloat or stale stats.

Use read replicas when read load exceeds primary capacity or you need geographic distribution. Replicas add lag and eventual consistency. Use this when deciding whether to add replication for scaling or HA.

Use a connection pool (PgBouncer, ProxySQL, or application-level) so many application threads share a smaller number of database connections. Reduces connection churn and stays under max_connections. Use this when you have many app instances or high concurrency and hit connection limits.

Define RPO and RTO for databases; use backups and optionally replication to meet them. Restore from backup or fail over to a replica; document and test the procedure. Use this when planning or executing database recovery after a failure or data loss.

Apply schema and data changes in versioned, reversible steps using migration scripts or tools (e.g. Flyway, Liquibase, or custom SQL). Test on staging first; backup before production migration. Use this when introducing or evolving schema in a way that is auditable and rollback-safe.

Set up a streaming replica for high availability or read scaling. Configure the primary for replication (wal_level, pg_hba); use pg_basebackup to clone; add primary_conninfo and recovery target on the replica. Use this when you need a standby or read replica.

Find and fix slow PostgreSQL queries using log_min_duration_statement and pg_stat_statements. Use EXPLAIN ANALYZE to see the plan and add indexes or rewrite queries. Use this when the database is slow or when optimizing after enabling query logging.

Tune PostgreSQL for your workload by setting shared_buffers, work_mem, effective_cache_size, and checkpoint-related parameters. Use EXPLAIN ANALYZE to find slow queries; add indexes as needed. Use this when the database is slow or when sizing a new server.

Enable slow query log and use EXPLAIN to fix slow MySQL queries. Add indexes on WHERE and JOIN columns. Use this when the database is slow.

Enable SSL for MySQL or MariaDB with ssl_cert, ssl_key, and ssl_ca in my.cnf. Require SSL for users with REQUIRE SSL. Use this for encrypted connections and compliance.

Tune MySQL or MariaDB with innodb_buffer_pool_size, query cache (if available), connection limits, and slow query log. Use this when improving performance or after measuring bottlenecks.

Enable SSL for PostgreSQL with ssl = on and server cert and key in postgresql.conf. Clients use sslmode=require or verify-full. Use this for encrypted connections and compliance.

Set up MySQL replication with binary logging on the primary and CHANGE REPLICATION SOURCE on the replica. Use for read scaling or HA. Use this when you need a replica for reads or failover.

Upgrade PostgreSQL by using pg_dump and pg_restore (logical) or pg_upgrade (in-place). Logical is safer and works across major versions; pg_upgrade is faster but same major. Use this when moving to a new major version.