PostgreSQL DBA

Position Summary

The Aurora PostgreSQL Database Administrator is responsible for secure, reliable, and high-performing database services and migrations from other database engines to Amazon Aurora PostgreSQL-Compatible Edition. This role combines end-to-end migration delivery, advanced query and workload tuning, and replication and high-availability engineering. The DBA partners with application, Dev Ops, cloud, infrastructure, security, and service-management teams and supports production.

• Assess and plan:
  Inventory source schemas, code, dependencies, data volumes, service-level requirements, and compatibility gaps. Define migration sizing, effort, risks, and strategy.
• Design the approach:
  Select AWS DMS, DMS Schema Conversion and/or AWS SCT, native PostgreSQL utilities, logical replication, and custom SQL or scripts based on the source platform and downtime target.
• Convert and remediate:
  Transform schemas, data types, indexes, constraints, views, functions, procedures, triggers, jobs, extensions, and application SQL for Aurora PostgreSQL.
• Build migration pipelines:
  Configure and tune full-load and change-data-capture tasks, endpoints, replication resources, task settings, and monitoring for large tables, LOBs, throughput, and latency.
• Validate and cut over:
  Conduct test migrations, data reconciliation, performance and load testing, cutover rehearsals, rollback planning, production cutover, and post-migration stabilization.
• Document and coordinate:
  Maintain mapping decisions, issue logs, acceptance criteria, runbooks, and handoff documentation while partnering with application owners and technical stakeholders.

• Baseline workloads:
  Measure database load, latency, throughput, waits, resource use, and growth with Cloud Watch Database Insights, metrics, logs, Enhanced Monitoring, RDS events, and workload testing.
• Diagnose bottlenecks:
  Use EXPLAIN (ANALYZE, BUFFERS), _activity, _statements, _statements, relevant  views, lock and wait analysis, and database logs.
• Tune end to end:
  Optimize SQL, indexes, statistics, execution plans, parameter groups, memory, connections, temporary work, storage and I/O, and writer or reader sizing.
• Maintain PostgreSQL health:
  Manage VACUUM and ANALYZE, autovacuum, bloat, long-running transactions, transaction , WAL generation, and table or index growth.
• Prevent regressions:
  Use controlled testing, plan comparison, workload replay or load testing, and Aurora query plan management with _mgmt where appropriate.
• Prove outcomes:
  Document measurable before-and-after results and recommend scalable, cost-conscious improvements to database configuration, SQL, and application design.

• Aurora Replicas:
  Configure reader instances, reader endpoints, promotion tiers, failover behavior, read scaling, and replica health or lag monitoring.
• Logical replication:
  Implement and troubleshoot publications, subscriptions, replication slots, replica identity, WAL settings and retention, initial synchronization, conflicts, and lag.
• Migration replication:
  Support AWS DMS change data capture and monitor source and target latency, task health, recoverable errors, throughput, and data consistency through cutover.
• Cross-Region resilience:
  Support cross-Region replicas or Aurora Global Database where used. Test switchovers and failovers and validate application connectivity, RTO, and RPO.
• Recovery readiness:
  Own backup retention, snapshots, point-in-time recovery, restore testing, disaster-recovery runbooks, and recovery exercises.

• Administer Aurora PostgreSQL clusters, writer and reader instances, endpoints, parameter groups, extensions, backups, snapshots, maintenance, upgrades, capacity, and cost.
• Apply PostgreSQL roles and privileges, AWS IAM, TLS, AWS KMS, AWS Secrets Manager or Cyber Ark, VPC security groups, audit logging, and access reviews.
• Automate health checks, maintenance, deployments, reporting, and recovery tasks using Python, Bash or shell, SQL, AWS CLI/SDK, and infrastructure-as-code tools.
• Provide incident response, root-cause analysis, controlled production changes, operational documentation, monitoring, and rotational 24x7 on-call coverage as need it.

• Three to five years of database administration or database engineering experience in a medium-to-large production environment.
• Strong hands-on administration of Amazon Aurora PostgreSQL-Compatible Edition, including cluster configuration, parameter groups, monitoring, backup and recovery, upgrades, security, and production support.
• Successful delivery of at least one end-to-end migration from a different database engine, such as Oracle, Sybase, SQL Server, MySQL/MariaDB, Db2, or a comparable platform, to Aurora PostgreSQL.
• Practical experience with AWS DMS full load and change data capture, schema and code conversion, migration troubleshooting, data validation, cutover, and rollback planning.
• Advanced PostgreSQL…