Database Services, Users & Backups

Real-world analogy

What is it?

Database admin-lite is the survival kit for supporting DB-backed apps: services, users, backups, simple queries, and recognizing HA/encryption layers without managing them from scratch.

Real-world relevance

App goes down at 7 AM. You check DB service status: OK. Connections: open. Query recent logs: many deadlocks overnight. You note this in the ticket, escalate with evidence to the DBA team. Without your triage, the DBA team would have started from zero.

Key points

• Service status and restarts — PostgreSQL: systemctl status postgresql. SQL Server: services.msc / SQL Server Configuration Manager. Never kill -9 a DB — use the graceful restart. Document every restart.
• Connections and port checks — Know the default ports (PostgreSQL 5432, SQL Server 1433, MySQL 3306, Oracle 1521). Test reachability with Test-NetConnection / telnet / psql from the app host — not only from the DBA laptop.
• Users, logins, roles, grants — Create logins/users with least privilege. GRANT only the required permissions. App users usually don’t need db_owner. Separate admin identity from app identity; rotate and vault credentials.
• Backups and restores — Know how the DB is backed up (full/diff/log) and where. Practice restore to a test server. ‘It’s backed up’ is a claim; ‘I restored last Tuesday successfully’ is evidence.
• Point-in-time recovery — Transaction-log backups enable recovery to a specific minute/second. Critical for financial data where losing the last 4 hours is unacceptable.
• Maintenance jobs — Index rebuilds/reorgs, statistics updates, backup jobs, integrity checks. In many shops, the DBA team owns them; you must recognize failure alerts.
• Replication and HA awareness — Always-On Availability Groups (SQL Server), streaming replication (PostgreSQL), Multi-AZ RDS. You may not configure it, but you should know whether your app’s DB is HA and what failover means.
• Security and encryption — TDE (Transparent Data Encryption), SSL/TLS on connections, strong passwords, no sample data in prod, DB audit logging. Table stakes in regulated industries.

Code example

-- some operator DB snippets

-- PostgreSQL: show long locks
SELECT pid, usename, application_name, state,
       now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC NULLS LAST
LIMIT 20;

-- PostgreSQL: size of largest tables
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- SQL Server: recent error log (T-SQL)
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, 'DESC';

-- SQL Server: who’s connected right now
SELECT session_id, login_name, host_name, program_name, status, last_request_end_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY last_request_end_time DESC;

Line-by-line walkthrough

1. 1. Operator DB snippets
2. 2. Postgres long locks
3. 3. Blank separator
4. 4. Postgres biggest tables
5. 5. Blank separator
6. 6. SQL Server recent errorlog
7. 7. Blank separator
8. 8. SQL Server current sessions

Spot the bug

App outage. Junior restarts the DB service every time. Alerts are silenced to reduce noise. Nobody reads the logs.

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• PostgreSQL docs (PostgreSQL)
• SQL Server Express (Microsoft)
• MySQL documentation (MySQL)