Message-ID: <48862B57.2050402@Sheeky.Biz>
Date: Wed, 23 Jul 2008 04:17:51 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
CC: pgsql-admin@postgresql.org, Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Subject: Re: conditional logging based on client
References: <a97c77030807220517u2324a0cckb8400a359123426a@mail.gmail.com>
	<200807221524.45181.achill@matrix.gatewaynet.com>
In-Reply-To: <200807221524.45181.achill@matrix.gatewaynet.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Achilleas Mantzios wrote:
> Στις Tuesday 22 July 2008 15:17:42 ο/η Rajesh Kumar Mallah
> έγραψε:
>> Hi,
>> 
>> We want to log all SQLs that has been executed by using psql
>> client. we do not want to use .psql_history as it is distributed
>> and may be deleted by users .
>> 
>> The original objective is that we should be able to know what all
>> hand made SQLs have  been executed in past  (which can date back as
>> long as 1 year even)
> 
> You have to adjust log_statement in your postgresql.conf however you
> will have to bear in mind the performance implications of this. 
> Normal production sites dont do this. You can enable/disable this
> kind of logging any time by killing -HUP

I am thinking that is a bit more than what you want. You can log all sql
statements run on a server but the server can't differentiate between
your custom-app sent sql and hand made sql sent through psql.

I know you can add things like remote address, username, dbname,
sessionid to the start of each log line but I don't know of getting the
client app name.


My thought would be to enable the statement logging and to modify your
client apps to have a set comment at the beginning and end of all sql
statements sent. You could then filter these sql statements out of the
logs leaving you with hand crafted sql.


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz