Date: Fri, 18 Jul 2008 10:16:00 +0200 (CEST)
From: Pomarede Nicolas <npomarede@corp.free.fr>
To: System/IJS - Joko <system@ijs.co.id>
cc: pgsql-performance@postgresql.org
Subject: Re: log_statement at postgres.conf
In-Reply-To: <014d01c8e899$dc597670$12068cc0@edp03>
Message-ID: <Pine.LNX.4.64.0807181013150.14417@localhost>
References: <01a601c8e7e9$41ad45d0$12068cc0@edp03>
	<b41c75520807170229x7768f6d3u68fb6369bafea270@mail.gmail.com>
	<Pine.LNX.4.64.0807171134490.17374@localhost>
	<014d01c8e899$dc597670$12068cc0@edp03>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Fri, 18 Jul 2008, System/IJS - Joko wrote:

>>> I added the following to FreeBSD:
>>>
>>> /etc/newsyslog.conf:
>>> /var/log/postgresql                     600  7     *    @T00  JC
> make new file?
>
>>> /etc/syslog.conf:
>>> local0.*                                        /var/log/postgresql
>>>
>>> /usr/local/pgsql/data/postgresql.conf:
>>> log_destination = 'syslog'
>>> syslog_facility = 'LOCAL0'
>>> syslog_ident = 'postgres'
>>> log_min_duration_statement = 100        # -1 is disabled, 0 logs all
>>> statements, in ms.
> I already do this, but i can't find my log file
> FYI, i just wanna to log every SQL statement.
>
>>> Remember to touch /var/log/postgresql before restarting syslogd (kill
>>> -HUP syslog-pid). Chmod 0700 so only root can read the log-file.
>>> Adjust log_min_duration_statement to your needs.
> i don't understand "to touch /var/log/postgresql"
>
>> Hello,
>>
>> another possibility is to have logs stored in a file by just changing
>> 'redirect_stderr' to 'on' and 'log_destination' to 'stderr'.
>>
>> This way, with the default config, all logs sent to stderr will be written
>> to 'log_directory' under the name 'log_filename', without having to change
>> syslog.conf (you just need to change postgresql.conf).
>>
>> Additionaly, I added 'log_rotation_size = 0' to have on log file per day.
>>
>> Note that in that case, the log files won't be rotated, you'll need to
>> check you don't store too many log file after a few months (as the number
>> of files will increase every day).
> setting 'log_destination' to 'stderr' could also log every sql statement happen on my server?
>
> My mission is to activate 'log_statement' to 'all', so that i can log all sql activity on my database.

There're 2 points in your question :

  - what to log
  - where to log

To choose 'what' to log in your case, you can change 'log_statement' to 
'all'.

Then, to choose 'where' to log, you can either use the proposal in the 
first answer, or change 'log_destination' to 'stderr' and 
'redirect_stderr' to 'on'.

Nicolas