PostgreSQL DBA Foundations and Safe Operations

1. Define the operating baseline

Before changing a server, record its PostgreSQL version, environment, owner, maintenance window, data classification, recovery objectives, and escalation contact. Use a named account with only the permissions required for the task; do not use a superuser for routine reporting.

SELECT version();
SHOW server_version;
SELECT current_database(), current_user, pg_is_in_recovery();
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS database_size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;

2. Use a safe change procedure

1. Write down the symptom, target, expected result, and rollback plan.
2. Check whether the change is read-only, transactional, lock-taking, or destructive.
3. Test the exact command on a representative non-production database.
4. Capture a before-state and schedule production work in an approved window.
5. Run one change at a time, verify the result, and record the evidence and owner.

3. Core responsibilities and hand-offs

A DBA maintains availability, recoverability, access control, capacity, and predictable performance. Application owners remain responsible for query intent and business data lifecycle. Agree on who receives alerts and who can approve a destructive action.

• Availability: connections, locks, replication, and planned maintenance.
• Recoverability: tested backups and point-in-time recovery objectives.
• Data safety: least privilege, change review, and controlled retention.
• Performance: measure with plans and workload evidence before tuning.