How to profile a PostgreSQL database?

Question

I want to profile (keep an eye) all the actions that occur in a database that is in PostgreSQL.

Is there such a utility that will help me do this?

Answer 1

To save my eyes, I use pgtop , a program that intentionally mimics the command on top of Unix.

Answer 2

Keep track of and profile are two different tasks, in my opinion.

For profiling (not viewing in real time what is happening right now, but to see which queries take the most time, etc.) check pgFouine:

http://pgfouine.projects.postgresql.org/

This will allow you to find out which queries are resource intensive and take appropriate actions: add missing indexes, rewrite queries using other methods, etc.

Answer 3

Since the ribs will respond to another pgbadger profiling pgbadger , it exited. You can find it here: http://dalibo.imtqy.com/pgbadger/

You can get a very detailed report and graphs.

pgfouine is no longer supported, the last update was in 2010.

Answer 4

Well, if you look at what happens regarding selection, updating, deleting, etc., there are several views in the pg_catalog scheme, I mainly use pg_stat_user_tables and pg_stat_user_indexes , but there are a lot more, everything is within pg_stat* .

There is also a pg_stat_activity that tells you what is running on your server right now.

I hacked four munin plugins that use user_tables and user_indexes, they are available there

Answer 5

Check out the Nagios-Plugin script or check_postgres.pl