Database Administrator

This roadmap teaches you to run databases reliably and securely, with PostgreSQL as the main example and MySQL alongside. You'll need Linux basics and a lab VM or Docker.

• Install PostgreSQL in a lab
• Connect with psql
• Plan your study time

• CyberA4U Academy — courses

SQL is how you create, read, update and delete data. Solid SQL is the base of every database task, from troubleshooting to security.

• Write SELECT queries with joins, filters and aggregates
• Use INSERT, UPDATE and DELETE safely
• Use transactions

Tools & tech: Database

• PostgreSQL — Tutorial

A good schema keeps data consistent and queries fast: the right data types, keys, constraints and normalization.

• Choose data types and primary keys
• Use foreign keys and constraints
• Balance normalization and performance

Tools & tech: Database

• PostgreSQL — Data definition

Indexes make queries fast; EXPLAIN shows how the database actually runs a query. Together they solve most performance problems.

• Explain B-tree and other index types
• Read EXPLAIN and EXPLAIN ANALYZE output
• Add indexes that match real queries

Tools & tech: Database

• PostgreSQL — Using EXPLAIN
• PostgreSQL — Indexes

Databases need least-privilege access: separate roles for applications, administrators and read-only users, with strong authentication.

• Create roles and grant minimal privileges
• Configure client authentication (pg_hba.conf)
• Avoid using superuser for applications

Tools & tech: Database

• PostgreSQL — Database roles
• PostgreSQL — Client authentication

Encrypt connections with TLS so credentials and data aren't sent in clear text, and protect data at rest with disk or column encryption.

• Enable and require TLS connections
• Choose disk vs column-level encryption
• Protect backups too

Tools & tech: Database

• PostgreSQL — Secure TCP/IP connections with SSL
• PostgreSQL — Encryption options

Backups are only real if you can restore them. Combine logical dumps and physical backups and test restores regularly.

• Compare pg_dump and physical backups
• Schedule and store backups safely
• Test restore time

Tools & tech: Database

• PostgreSQL — Backup and restore

Point-in-time recovery replays the write-ahead log to restore a database to any moment — for example, just before a bad DELETE.

• Explain WAL archiving
• Configure a base backup and WAL archive
• Restore to a chosen timestamp

Tools & tech: Database

• PostgreSQL — Continuous archiving and PITR

Replication copies data to other servers for high availability and read scaling. Streaming replication in PostgreSQL and binary log replication in MySQL are the common options.

• Explain primary/replica replication
• Compare synchronous and asynchronous modes
• Monitor replication lag

Tools & tech: Database

• PostgreSQL — High availability, load balancing, and replication
• MySQL 8.4 — Replication

Every database connection costs memory. A pooler such as PgBouncer lets many application connections share a few database connections.

• Explain why pooling matters
• Configure PgBouncer modes
• Size pools correctly

Tools & tech: Database

• PgBouncer

Auditing records who accessed or changed data, which is essential for investigations and compliance.

• Enable logging of connections and DDL
• Use pgAudit for detailed auditing
• Protect and review audit logs

Tools & tech: Database

• pgAudit

SQL injection lets attackers change your queries through user input. Parameterized queries, least-privilege roles and input validation prevent it.

• Use parameterized queries in application code
• Limit database privileges
• Recognize injection-prone code

Tools & tech: Database

Learn it on CyberA4U:Module 17: Database Query Injection Attacks

• OWASP SQL Injection Prevention Cheat Sheet

Routine operations keep PostgreSQL healthy: vacuum, statistics, monitoring, upgrades and configuration tuning.

• Understand VACUUM and autovacuum
• Monitor key metrics
• Plan minor and major upgrades

Tools & tech: Database

• PostgreSQL — Routine database maintenance

MySQL is widely used alongside PostgreSQL. Learn its administration basics: users, storage engines, backups and monitoring.

• Manage users and privileges
• Understand InnoDB
• Back up and restore MySQL

Tools & tech: Database

• MySQL 8.4 — MySQL server administration

When a database is slow, measure first: slow query logs, wait events, locks and resource use point to the cause.

• Find slow queries
• Diagnose locking and contention
• Tune configuration based on evidence

Tools & tech: Database

• PostgreSQL — Performance tips

High availability combines replication, automatic failover and tested procedures so the database survives failures.

• Compare HA approaches
• Understand failover and split-brain risks
• Test failover

Tools & tech: Database

• PostgreSQL — High availability, load balancing, and replication

Check that you can run a secure, backed-up, monitored database and recover it when something goes wrong.

• Secure access and connections
• Back up and restore, including point-in-time
• Diagnose performance problems

• CyberA4U CTF Arena — practice challenges

You can now administer databases professionally. Continue with Linux Administrator, Secrets Management, or a PostgreSQL or MySQL certification.

• Keep a restore-tested lab
• Choose your next roadmap
• Share a runbook you wrote

• CyberA4U CTF Arena — practice challenges