I was surprised recently to find that many of the engineers I worked with weren’t aware of the Server Status tool in pgAdmin. This seems to be true of many people who work with PostgreSQL by the paucity of mentions in blog entries. Server Status allows you to monitor your host in near real-time. I was able to tell when one of the other engineers starting hitting my development database with his production queries (not a great idea) and was blocking my database loads. It’s also possible to see connections that have been inadvertently left open (conn.close anyone?) and close them manually.
Here’s how you use it:
By far the most useful for the work I’ve done have been the activity and locks panes. You can close the ones you don’t want to use and expand the ones you want to use. You can configure how often the server is queried at the top of the window:
The red and green arrow buttons refreshes the view. The next icon copies the current selection to clipboard. The third from the left (that looks confusingly like the one next to it) opens the Query Tool with the selected query. The orange box will cancel the query. The red box will terminate the backend. For a discussion on these, see the documentation on Server Status. Suffice to say, you probably just want to cancel a query rather than terminate the backend and both should be used with caution.
The disk icon allows you to commit transaction while the recycle bin next to it rolls back the transaction. The two dropdown menus relate to the logs, the first allowing you to select either historic logs or the current one, and the second telling the server to rotate its server log files. The dropdown on the right controls how often the display is refreshed.
Also important is the color coding:
While this data can be easily queried from the system tables, the visual interface makes the process easier, especially when you’re trying to monitoring running processes. I think it’s a great tool for any database administrator or developer to quickly diagnose performance issues.
It’s important to have the proper user permissions if you want to use this effectively. A read-only user won’t see the actual transactions. Instead of the query being displayed, they will see “<insufficient privileges>.” The first time I saw that, I thought the query running had insufficient privileges to run, which was a little frightening. A Google query didn’t provide the information I needed to understand what was going on. I just happened to notice that I was logged in under the read-only account. Once I changed accounts, I had full visibility on the queries being run on the server.
For more information on the Server Status Tool, see the documentation and enjoy one of the best features of pgAdmin.