PostgreSQL DBA Daily Checklist

The daily checklist of a PostgreSQL DBA


We often get this question, What are the most important things a PostgreSQL DBA should do to guarantee optimal performance and reliability, Do we have checklist for PostgreSQL DBAs to follow daily ? Since we are getting this question too often, Thought let’s note it as blog post and share with community of PostgreSQL ecosystem. The only objective this post is to share the information, Please don’t consider this as a run-book or recommendation from MinervaDB PostgreSQL support. We at MinervaDB are not accountable of any negative performance in you PostgreSQL performance with running these scripts in production database infrastructure of your business, The following is a simple daily checklist for PostgreSQL DBA:

Task 1: Check that all the PostgreSQL instances are up and operational:

What if you have several instances of PostgreSQL are running:

Task 2: Monitoring PostgreSQL logsRecord PostgreSQL error logs: Open postgresql.conf configuration file, Under the ERROR REPORTING AND LOGGING section of the file, use following config parameters:

Save the postgresql.conf file and restart the PostgreSQL server:

Task 3: Confirm PostgreSQL backup completed successfully

Use backup logs (possible only with PostgreSQL logical backup) to audit backup quality:

Task 4: Monitoring PostgreSQL Database Size:

Task 5: Monitor all PostgreSQL queries running (please repeat this task every 90 minutes during business / peak hours):

Task 6: Inventory of indexes in PostgreSQL database:

Task 7: Finding the largest databases in your PostgreSQL cluster:

Task 8: when you are suspecting some serious performance bottleneck in PostgreSQL ? Especially when you suspecting transactions blocking each other:

Task 9:  Identify bloated Tables in PostgreSQL :

Task 10:  Identify bloated indexes in PostgreSQL :

Task 11:  Monitor blocked and blocking activities in PostgreSQL:

Task 12: Monitoring PostgreSQL Disk I/O performance:

References 

☛ MinervaDB is trusted by top companies worldwide

Customer Logo

About Shiv Iyer 72 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.