Saturday, January 8, 2011

Client activity information in PostgreSQL

In most of my projects I use PostgreSQL as database, I'm sure that if you see what you get for free, most of you will turn to it, anyhu' this post is about getting information from database, such as:
- databases to which has connections to it;
- current queries ran on X database and the timestamp when the query was started;
- ID's of processes;
- user name of connected clients;
- port on which each client is connected;
- client IP address;
- based upon above information we can get more special info regarding active connections;

In order to see all of the above, run this query on the database:

SELECT * FROM PG_STAT_ACTIVITY;

The reason I was interested in this kind of information is that from time to time the database structure changes, therefore I need to run queries on previous database structures in order to fulfill latest needs, sooo... in order to upgrade the database I require that NO one else besides my "upgrade" application is connected to the database, therefore I run the following query in order to see to how many connections I have to X database, if the number of connections is greater than 1(if I'm connected to the database, I will be counted as well) then the application will wait until the number of connections to X database reaches 1 and then run the update queries, the query that I'm using is:

SELECT DATNAME AS "Database", COUNT(*) AS "ConnectionCount" FROM PG_STAT_ACTIVITY GROUP BY "Database";

and this will result in showing:

Database

ConnectionCount

X db

2

Y db

70

etc.

Well that's about all that I wanted to point out for now, do you have any special queries you run on a PostgreSQL database and want to share? comment bellow and I will put above this final thought like:
Name:
SQL QUERY