Re: How to log query's from servers ?

On Mon, Jun 6, 2011 at 7:26 AM, Condor <condor(at)stz-bg(dot)com> wrote:
> On Mon, 06 Jun 2011 18:53:57 +0800, Craig Ringer wrote:
>>
>> On 6/06/2011 4:14 PM, Condor wrote:
>>>
>>> Hello ppl,
>>> any one can tell me how i make my postgresql server to log commands
>>> coming from another postgres server ? I have one postgresql and give to
>>> 2 other sql server to access my db. I want to log all query's coming
>>> only from one of the servers. Is that possibly ?
>>
>> If the different servers log in with different user accounts or log
>> into different databases you could use database-level or user-level
>> SET commands to set log_statement for only one of them.
>>
>> Otherwise: just grep the logs.
>
> Can you explain little more how i can use database-level or user-level
> SET commands to set log_statement for only one of them ?
> From server that i want to log his query i log in to db on another server
> with: psql -U user -h 192.168.1.1 master_db. Here maybe I should say that I
> wanna
> monitor users that I gave them access to DB.
> Every server use different username to connect. I can't use grep on logs
> because
> other two servers generate around 500 MB to 1 GB log file per day.
>
> Probably, a little filter or patch on postgresql source code file which
> manage logs to write log file
> only if ip is the ip that i want will save me.

What you are looking for are alter statements. On pretty new psql,
they'll be show with \h commands. you'll want something like this:

alter database mydb set log_statement='all';
alter user bubba set log_statement='all';