Postgres SQL

Key Responsibilities:

• Database Administration:

  • Install, configure, and upgrade PostgreSQL database software on Linux/Unix and Windows platforms.

  • Manage and maintain large-scale production databases, ensuring high availability, reliability, and performance.

  • Monitor database performance, handle capacity planning, and address issues proactively.

  • Implement and enforce database standards and best practices.

• Performance Tuning & Optimization:

  • Analyze query performance and optimize SQL queries, indexing strategies, and database design for optimal performance.

  • Use tools like EXPLAIN, pg_stat_statements, and other diagnostic utilities to troubleshoot and resolve database bottlenecks.

  • Conduct routine performance tuning, including query optimization and adjusting PostgreSQL configuration parameters.

• Backup and Recovery:

  • Implement and manage comprehensive backup and recovery strategies using PostgreSQL tools (pg_dump, pg_basebackup, etc.) and third-party backup solutions.

  • Design and automate disaster recovery plans to minimize downtime.

  • Ensure high availability by managing replication (Streaming Replication, Logical Replication) and failover systems.

• Database Security:

  • Administer database user access, roles, and permissions to ensure the security of sensitive data.

  • Implement security protocols and encryption (SSL/TLS) for both data at rest and in transit.

  • Conduct regular database audits and vulnerability assessments.

• Replication and High Availability:

  • Set up and maintain PostgreSQL replication (synchronous and asynchronous), clustering, and failover configurations.

  • Monitor and troubleshoot replication lag, data consistency, and synchronization issues.

  • Ensure database uptime and resilience using solutions like pgPool, Patroni, and PgBouncer for load balancing and connection pooling.

• Upgrades and Patches:

  • Apply critical patches and updates in a timely manner to ensure systems remain secure.

  • Plan and execute major PostgreSQL version upgrades while ensuring minimal downtime and data integrity.