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 

About Shiv Iyer 18 Articles
Open Source Database Systems Geek in MySQL, MariaDB, PostgreSQL and ClickHouse with core expertise in performance, scalability, high availability and database reliability engineering, Shiv currently is the founder and principal of MinervaDB, An boutique private-label consulting, support, remote DBA and education services provider for MySQL, MariaDB, Percona Server, MyRocks, PostgreSQL and ClickHouse with over 300 customers worldwide. Shiv in the past worked for companies like MySQL AB, SUN Microsystems, AOL, eBay, PayPal, PalominoDB and Percona. Shiv also is a frequent speaker in open source conferences worldwide.

Be the first to comment

Leave a Reply

Your email address will not be published.


*