From system@ijs.co.id Thu Jul  17 09:15:21 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id C339B64FF8A
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 Jul 2008 06:15:21 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 04518-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 Jul 2008 06:15:10 -0300 (ADT)
X-Greylist: delayed 00:30:41.007283 by SQLgrey-1.7.6
Received: from phoenix.sby.dnet.net.id (zeus02.sby.dnet.net.id [202.148.7.49])
	by postgresql.org (Postfix) with ESMTP id 2693D64FCDA
	for <pgsql-performance@postgresql.org>;
	Thu, 17 Jul 2008 06:15:13 -0300 (ADT)
Received: from phoenix.sby.dnet.net.id (localhost [127.0.0.1])
	by phoenix.sby.dnet.net.id (Postfix) with ESMTP id B81112B873C
	for <pgsql-performance@postgresql.org>;
	Thu, 17 Jul 2008 16:03:40 +0700 (WIT)
Received: from neptunus.sby.dnet.net.id (neptunus.sby.dnet.net.id
	[202.148.11.37])
	by phoenix.sby.dnet.net.id (Postfix) with ESMTP id A89CA2B8726
	for <pgsql-performance@postgresql.org>;
	Thu, 17 Jul 2008 16:03:40 +0700 (WIT)
Received: from ijs.co.id (unknown [202.148.12.194])
	by neptunus.sby.dnet.net.id (Postfix) with ESMTP id DA38697EE4
	for <pgsql-performance@postgresql.org>;
	Thu, 17 Jul 2008 15:44:27 +0700 (WIT)
Received: from edp03 ([192.140.6.18]) by ijs.co.id (ijs.co.id [192.140.6.10])
	(MDaemon.PRO.v6.8.5.R) with ESMTP id 46-md50000000006.tmp
	for <pgsql-performance@postgresql.org>; Thu, 17 Jul 2008 15:45:32 +0700
Message-ID: <01a601c8e7e9$41ad45d0$12068cc0@edp03>
Reply-To: "System/IJS - Joko" <system@ijs.co.id>
From: "System/IJS - Joko" <system@ijs.co.id>
To: <pgsql-performance@postgresql.org>
Subject: log_statement at postgres.conf
Date: Thu, 17 Jul 2008 15:43:59 +0700
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_01A3_01C8E823.EDFDC5F0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-MDRemoteIP: 192.140.6.18
X-Return-Path: system@ijs.co.id
X-MDaemon-Deliver-To: pgsql-performance@postgresql.org
X-Spam-Processed: ijs.co.id, Thu, 17 Jul 2008 15:45:37 +0700
X-Virus-Scanned: ClamAV using ClamSMTP
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200807/138
X-Sequence-Number: 30738

This is a multi-part message in MIME format.

------=_NextPart_000_01A3_01C8E823.EDFDC5F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear all,

After setting log_statement=3D'all' at postgres.conf,
then i'm rebooting OS [freeBSD or CentOS],
i can't find where log file created from log_statement=3D'all' =
located...
FYI, location of postgres.conf at /var/lib/pgsql/data/postgres.conf

many thanks..

Regards,
Joko [SYSTEM]
PT. Indra Jaya Swastika
Phone: +62 31 7481388  Ext 201
http://www.ijs.co.id

--
If you have any problem with our services ,
please contact us at 70468146 or e-mail: helpdesk@ijs.co.id
PT Indra Jaya Swastika | Jl. Kalianak Barat 57A | +62-31-7481388

------=_NextPart_000_01A3_01C8E823.EDFDC5F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5730.13" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#fffbf0>
<DIV><FONT face=3DVerdana size=3D2>Dear all,</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DVerdana size=3D2>After setting log_statement=3D'all' =
at=20
postgres.conf,</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>then i'm rebooting OS [freeBSD or=20
CentOS],</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>i can't find where log file created =
from=20
log_statement=3D'all' located...</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>FYI, location of postgres.conf at=20
/var/lib/pgsql/data/postgres.conf</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DVerdana size=3D2>many thanks..</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DVerdana size=3D2>Regards,<BR>Joko [SYSTEM]<BR>PT. =
Indra Jaya=20
Swastika<BR>Phone: +62 31 7481388&nbsp; Ext 201<BR><A=20
href=3D"http://www.ijs.co.id/">http://www.ijs.co.id</A></FONT></DIV></BOD=
Y></HTML>


<html>
<br>
--<br>
If you have any problem with our services ,<br>
please contact us at 70468146 or e-mail: helpdesk@ijs.co.id<br>
PT Indra Jaya Swastika | Jl. Kalianak Barat 57A | +62-31-7481388</html>

------=_NextPart_000_01A3_01C8E823.EDFDC5F0--




From kometen@gmail.com Thu Jul  17 09:29:32 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 3085D64FF7A
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 Jul 2008 06:29:32 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 19472-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 Jul 2008 06:29:22 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.157])
	by postgresql.org (Postfix) with ESMTP id 8D3C064FF4F
	for <pgsql-performance@postgresql.org>;
	Thu, 17 Jul 2008 06:29:25 -0300 (ADT)
Received: by fg-out-1718.google.com with SMTP id 16so3221669fgg.23
	for <pgsql-performance@postgresql.org>;
	Thu, 17 Jul 2008 02:29:23 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type
	:content-transfer-encoding:content-disposition:references;
	bh=nATtLev0ZjmhlaiIyCxCkWy4Mn3kh0g+dKmvUW+pknA=;
	b=rN5nUYtupQoM+E9769/akDH8h8TRnbW3oOg6rGm7tVq0ygEHAl4+gnYnLgQFunYZEX
	eMorWDVcs0bazGXkRyhfidGWmGUhfZrwGmvCCjEEQh9m1ZUiTBktwsTUAbLZGigDFX/w
	BVjuxLkXgTqVy8Rm77nkqB5bbAf825EZVvtp4=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=btaIOxmxFpLkcai+JXqe4JQvPDG19/upSWt/eJJR8EiBtr8BmjzeWL5J2fOtLVB13I
	2PPLobcyzLwCgxijRs70F4sVIhzQOMeGVoe6Tet7W6xf/jyr+fuAIWhXjiITPdq7nx2p
	XKdJWII9tNJZ1LgzkibfhFe9dpUM4gwiHfKjQ=
Received: by 10.86.72.3 with SMTP id u3mr3628504fga.37.1216286963398;
	Thu, 17 Jul 2008 02:29:23 -0700 (PDT)
Received: by 10.86.81.6 with HTTP; Thu, 17 Jul 2008 02:29:23 -0700 (PDT)
Message-ID: <b41c75520807170229x7768f6d3u68fb6369bafea270@mail.gmail.com>
Date: Thu, 17 Jul 2008 11:29:23 +0200
From: "Claus Guttesen" <kometen@gmail.com>
To: "System/IJS - Joko" <system@ijs.co.id>
Subject: Re: log_statement at postgres.conf
Cc: pgsql-performance@postgresql.org
In-Reply-To: <01a601c8e7e9$41ad45d0$12068cc0@edp03>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <01a601c8e7e9$41ad45d0$12068cc0@edp03>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200807/139
X-Sequence-Number: 30739

> After setting log_statement='all' at postgres.conf,
> then i'm rebooting OS [freeBSD or CentOS],
> i can't find where log file created from log_statement='all' located...
> FYI, location of postgres.conf at /var/lib/pgsql/data/postgres.conf
>
> many thanks..

I added the following to FreeBSD:

/etc/newsyslog.conf:
/var/log/postgresql                     600  7     *    @T00  JC

/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.

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 found this recipe somewhere, but don't remember where so I can't
give credit to the that person.

-- 
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare



From npomarede@corp.free.fr Thu Jul  17 09:40:21 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 98B6E64FF4F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 Jul 2008 06:40:21 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 27584-05 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 17 Jul 2008 06:40:13 -0300 (ADT)
X-Greylist: whitelisted by SQLgrey-1.7.6
Received: from smtp8-g19.free.fr (smtp8-g19.free.fr [212.27.42.65])
	by postgresql.org (Postfix) with ESMTP id 6EB1765049F
	for <pgsql-performance@postgresql.org>;
	Thu, 17 Jul 2008 06:40:16 -0300 (ADT)
Received: from smtp8-g19.free.fr (localhost [127.0.0.1])
	by smtp8-g19.free.fr (Postfix) with ESMTP id AB1B917F55B;
	Thu, 17 Jul 2008 11:40:14 +0200 (CEST)
Received: from pulsar.staff.proxad.net (pulsar.staff.proxad.net
	[213.228.1.109])
	by smtp8-g19.free.fr (Postfix) with ESMTP id F3D6D17F532;
	Thu, 17 Jul 2008 11:40:13 +0200 (CEST)
Date: Thu, 17 Jul 2008 11:40:13 +0200 (CEST)
From: Pomarede Nicolas <npomarede@corp.free.fr>
X-X-Sender: npomarede@localhost
To: Claus Guttesen <kometen@gmail.com>
cc: System/IJS - Joko <system@ijs.co.id>, pgsql-performance@postgresql.org
Subject: Re: log_statement at postgres.conf
In-Reply-To: <b41c75520807170229x7768f6d3u68fb6369bafea270@mail.gmail.com>
Message-ID: <Pine.LNX.4.64.0807171134490.17374@localhost>
References: <01a601c8e7e9$41ad45d0$12068cc0@edp03>
	<b41c75520807170229x7768f6d3u68fb6369bafea270@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200807/140
X-Sequence-Number: 30740

On Thu, 17 Jul 2008, Claus Guttesen wrote:

>> After setting log_statement='all' at postgres.conf,
>> then i'm rebooting OS [freeBSD or CentOS],
>> i can't find where log file created from log_statement='all' located...
>> FYI, location of postgres.conf at /var/lib/pgsql/data/postgres.conf
>>
>> many thanks..
>
> I added the following to FreeBSD:
>
> /etc/newsyslog.conf:
> /var/log/postgresql                     600  7     *    @T00  JC
>
> /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.
>
> 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 found this recipe somewhere, but don't remember where so I can't
> give credit to the that person.
>

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).


Nicolas



From system@ijs.co.id Fri Jul  18 06:15:03 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E1D7264FD0C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 18 Jul 2008 03:15:03 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 81617-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 18 Jul 2008 03:14:55 -0300 (ADT)
X-Greylist: delayed 00:26:45.241567 by SQLgrey-1.7.6
Received: from unicorn.sby.dnet.net.id (unicorn.sby.dnet.net.id
	[202.148.11.52])
	by postgresql.org (Postfix) with ESMTP id 935E464FD09
	for <pgsql-performance@postgresql.org>;
	Fri, 18 Jul 2008 03:14:54 -0300 (ADT)
Received: from unicorn.sby.dnet.net.id (localhost [127.0.0.1])
	by unicorn.sby.dnet.net.id (Postfix) with ESMTP id CAF9223A063A
	for <pgsql-performance@postgresql.org>;
	Fri, 18 Jul 2008 12:48:03 +0700 (WIT)
Received: from neptunus.sby.dnet.net.id (neptunus.sby.dnet.net.id
	[202.148.11.37])
	by unicorn.sby.dnet.net.id (Postfix) with ESMTP id BB0F523A05FD
	for <pgsql-performance@postgresql.org>;
	Fri, 18 Jul 2008 12:48:03 +0700 (WIT)
Received: from ijs.co.id (unknown [202.148.12.194])
	by neptunus.sby.dnet.net.id (Postfix) with ESMTP id B425A97EC9
	for <pgsql-performance@postgresql.org>;
	Fri, 18 Jul 2008 12:48:02 +0700 (WIT)
Received: from edp03 ([192.140.6.18]) by ijs.co.id (ijs.co.id [192.140.6.10])
	(MDaemon.PRO.v6.8.5.R) with ESMTP id 12-md50000000012.tmp
	for <pgsql-performance@postgresql.org>; Fri, 18 Jul 2008 12:47:01 +0700
Message-ID: <014d01c8e899$dc597670$12068cc0@edp03>
Reply-To: "System/IJS - Joko" <system@ijs.co.id>
From: "System/IJS - Joko" <system@ijs.co.id>
To: <pgsql-performance@postgresql.org>
References: <01a601c8e7e9$41ad45d0$12068cc0@edp03>
	<b41c75520807170229x7768f6d3u68fb6369bafea270@mail.gmail.com>
	<Pine.LNX.4.64.0807171134490.17374@localhost>
Subject: Re: log_statement at postgres.conf
Date: Fri, 18 Jul 2008 12:48:10 +0700
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_014A_01C8E8D4.88AC6790"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-MDRemoteIP: 192.140.6.18
X-Return-Path: system@ijs.co.id
X-MDaemon-Deliver-To: pgsql-performance@postgresql.org
X-Spam-Processed: ijs.co.id, Fri, 18 Jul 2008 12:47:06 +0700
X-Virus-Scanned: ClamAV using ClamSMTP
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200807/145
X-Sequence-Number: 30745

This is a multi-part message in MIME format.

------=_NextPart_000_014A_01C8E8D4.88AC6790
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

>> 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 =3D 'syslog'
>> syslog_facility =3D 'LOCAL0'
>> syslog_ident =3D 'postgres'
>> log_min_duration_statement =3D 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,
>=20
> another possibility is to have logs stored in a file by just changing=20
> 'redirect_stderr' to 'on' and 'log_destination' to 'stderr'.
>=20
> This way, with the default config, all logs sent to stderr will be =
written=20
> to 'log_directory' under the name 'log_filename', without having to =
change=20
> syslog.conf (you just need to change postgresql.conf).
>=20
> Additionaly, I added 'log_rotation_size =3D 0' to have on log file per =
day.
>=20
> 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=20
> 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.

Regards,
Joko [SYSTEM]
PT. Indra Jaya Swastika
Phone: +62 31 7481388  Ext 201
http://www.ijs.co.id

--
If you have any problem with our services ,
please contact us at 70468146 or e-mail: helpdesk@ijs.co.id
PT Indra Jaya Swastika | Jl. Kalianak Barat 57A | +62-31-7481388

------=_NextPart_000_014A_01C8E8D4.88AC6790
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5730.13" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DVerdana size=3D2>&gt;&gt; I added the following to=20
FreeBSD:<BR>&gt;&gt;<BR>&gt;&gt; /etc/newsyslog.conf:<BR>&gt;&gt;=20
/var/log/postgresql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
600&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp; *&nbsp;&nbsp;&nbsp; @T00&nbsp;=20
JC</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>make new file?<BR></FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>&gt;&gt; =
/etc/syslog.conf:<BR>&gt;&gt;=20
local0.*&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;=20
/var/log/postgresql<BR>&gt;&gt;<BR>&gt;&gt;=20
/usr/local/pgsql/data/postgresql.conf:<BR>&gt;&gt; log_destination =3D=20
'syslog'<BR>&gt;&gt; syslog_facility =3D 'LOCAL0'<BR>&gt;&gt; =
syslog_ident =3D=20
'postgres'<BR>&gt;&gt; log_min_duration_statement =3D=20
100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # -1 is disabled, 0 logs=20
all<BR>&gt;&gt; statements, in ms.</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>I already do this, but i can't find =
my log=20
file</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>FYI, i just wanna to log every SQL=20
statement.</FONT></DIV><FONT face=3DVerdana size=3D2>
<DIV><BR>&gt;&gt; Remember to touch /var/log/postgresql before =
restarting=20
syslogd (kill<BR>&gt;&gt; -HUP syslog-pid). Chmod 0700 so only root can =
read the=20
log-file.<BR>&gt;&gt; Adjust log_min_duration_statement to your =
needs.</DIV>
<DIV>i don't understand "to touch /var/log/postgresql"<BR></DIV>
<DIV>&gt; Hello,<BR>&gt; <BR>&gt; another possibility is to have logs =
stored in=20
a file by just changing <BR>&gt; 'redirect_stderr' to 'on' and =
'log_destination'=20
to 'stderr'.<BR>&gt; <BR>&gt; This way, with the default config, all =
logs sent=20
to stderr will be written <BR>&gt; to 'log_directory' under the name=20
'log_filename', without having to change <BR>&gt; syslog.conf (you just =
need to=20
change postgresql.conf).<BR>&gt; <BR>&gt; Additionaly, I added=20
'log_rotation_size =3D 0' to have on log file per day.<BR>&gt; <BR>&gt; =
Note that=20
in that case, the log files won't be rotated, you'll need to <BR>&gt; =
check you=20
don't store too many log file after a few months (as the number <BR>&gt; =
of=20
files will increase every day).<BR>setting 'log_destination' to 'stderr' =
could=20
also log every sql statement happen on my server?</DIV>
<DIV>&nbsp;</DIV>
<DIV>My mission is to activate 'log_statement' to 'all', so that i can =
log all=20
sql activity on my database.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Regards,<BR>Joko [SYSTEM]<BR>PT. Indra Jaya Swastika<BR>Phone: +62 =
31=20
7481388&nbsp; Ext 201<BR><A=20
href=3D"http://www.ijs.co.id">http://www.ijs.co.id</A></FONT></DIV></BODY=
></HTML>


<html>
<br>
--<br>
If you have any problem with our services ,<br>
please contact us at 70468146 or e-mail: helpdesk@ijs.co.id<br>
PT Indra Jaya Swastika | Jl. Kalianak Barat 57A | +62-31-7481388</html>

------=_NextPart_000_014A_01C8E8D4.88AC6790--




From npomarede@corp.free.fr Fri Jul  18 08:16:14 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id ED98964FFE0
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 18 Jul 2008 05:16:14 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 17553-05 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 18 Jul 2008 05:16:00 -0300 (ADT)
X-Greylist: whitelisted by SQLgrey-1.7.6
Received: from smtp8-g19.free.fr (smtp8-g19.free.fr [212.27.42.65])
	by postgresql.org (Postfix) with ESMTP id A53F864FCDD
	for <pgsql-performance@postgresql.org>;
	Fri, 18 Jul 2008 05:16:02 -0300 (ADT)
Received: from smtp8-g19.free.fr (localhost [127.0.0.1])
	by smtp8-g19.free.fr (Postfix) with ESMTP id 2237417F540;
	Fri, 18 Jul 2008 10:16:01 +0200 (CEST)
Received: from pulsar.staff.proxad.net (pulsar.staff.proxad.net
	[213.228.1.109])
	by smtp8-g19.free.fr (Postfix) with ESMTP id E7DF617F51A;
	Fri, 18 Jul 2008 10:16:00 +0200 (CEST)
Date: Fri, 18 Jul 2008 10:16:00 +0200 (CEST)
From: Pomarede Nicolas <npomarede@corp.free.fr>
X-X-Sender: npomarede@localhost
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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200807/146
X-Sequence-Number: 30746

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




From system@ijs.co.id Mon Jul  21 06:35:41 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id C1C9464FD13
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 21 Jul 2008 03:35:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 12160-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 21 Jul 2008 03:35:36 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from unicorn.sby.dnet.net.id (unicorn.sby.dnet.net.id
	[202.148.11.52])
	by postgresql.org (Postfix) with ESMTP id B4F5E64FCE7
	for <pgsql-performance@postgresql.org>;
	Mon, 21 Jul 2008 03:35:37 -0300 (ADT)
Received: from unicorn.sby.dnet.net.id (localhost [127.0.0.1])
	by unicorn.sby.dnet.net.id (Postfix) with ESMTP id 76AE123A02AD
	for <pgsql-performance@postgresql.org>;
	Mon, 21 Jul 2008 13:35:32 +0700 (WIT)
Received: from neptunus.sby.dnet.net.id (neptunus.sby.dnet.net.id
	[202.148.11.37])
	by unicorn.sby.dnet.net.id (Postfix) with ESMTP id 0EBCD23A01F1
	for <pgsql-performance@postgresql.org>;
	Mon, 21 Jul 2008 13:35:32 +0700 (WIT)
Received: from ijs.co.id (unknown [202.148.12.194])
	by neptunus.sby.dnet.net.id (Postfix) with ESMTP id 2758797EEB
	for <pgsql-performance@postgresql.org>;
	Mon, 21 Jul 2008 13:35:31 +0700 (WIT)
Received: from edp03 ([192.140.6.18]) by ijs.co.id (ijs.co.id [192.140.6.10])
	(MDaemon.PRO.v6.8.5.R) with ESMTP id 63-md50000000010.tmp
	for <pgsql-performance@postgresql.org>; Mon, 21 Jul 2008 13:34:47 +0700
Message-ID: <009b01c8eafb$f29fa9e0$12068cc0@edp03>
Reply-To: "System/IJS - Joko" <system@ijs.co.id>
From: "System/IJS - Joko" <system@ijs.co.id>
To: <pgsql-performance@postgresql.org>
References: <01a601c8e7e9$41ad45d0$12068cc0@edp03>
	<b41c75520807170229x7768f6d3u68fb6369bafea270@mail.gmail.com>
	<Pine.LNX.4.64.0807171134490.17374@localhost>
	<014d01c8e899$dc597670$12068cc0@edp03>
	<Pine.LNX.4.64.0807181013150.14417@localhost>
Subject: Re: log_statement at postgres.conf
Date: Mon, 21 Jul 2008 13:35:20 +0700
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-MDRemoteIP: 192.140.6.18
X-Return-Path: system@ijs.co.id
X-MDaemon-Deliver-To: pgsql-performance@postgresql.org
X-Spam-Processed: ijs.co.id, Mon, 21 Jul 2008 13:34:47 +0700
X-Virus-Scanned: ClamAV using ClamSMTP
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200807/167
X-Sequence-Number: 30767

Thx a lot Nicolas,

I finaly success to log query statement because of your simple explanation.
I have other question:
1. Is there posibility to automatically logging that statement to table?
2. All of that statement is come from every database on my server,
  could I know from which database that statement come?
  or at least I can filter to log only from database X ?
3. If I need to log only changed made on my database, then the value of 
'log_statement' is 'mod' ?
CMIIW

Regards,
Joko [SYSTEM]
PT. Indra Jaya Swastika
Phone: +62 31 7481388  Ext 201
http://www.ijs.co.id

--sorry for my bad english

----- Original Message ----- 
From: "Pomarede Nicolas" <npomarede@corp.free.fr>
To: "System/IJS - Joko" <system@ijs.co.id>
Cc: <pgsql-performance@postgresql.org>
Sent: Friday, July 18, 2008 3:16 PM
Subject: Re: [PERFORM] log_statement at postgres.conf

> 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
> 

--
If you have any problem with our services ,
please contact us at 70468146 or e-mail: helpdesk@ijs.co.id
PT Indra Jaya Swastika | Jl. Kalianak Barat 57A | +62-31-7481388





From npomarede@corp.free.fr Mon Jul  21 08:27:41 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id F412B64FF27
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 21 Jul 2008 05:27:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 09556-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 21 Jul 2008 05:27:35 -0300 (ADT)
X-Greylist: whitelisted by SQLgrey-1.7.6
Received: from smtp8-g19.free.fr (smtp8-g19.free.fr [212.27.42.65])
	by postgresql.org (Postfix) with ESMTP id 1334C64FD13
	for <pgsql-performance@postgresql.org>;
	Mon, 21 Jul 2008 05:27:38 -0300 (ADT)
Received: from smtp8-g19.free.fr (localhost [127.0.0.1])
	by smtp8-g19.free.fr (Postfix) with ESMTP id 571BC17F564;
	Mon, 21 Jul 2008 10:27:36 +0200 (CEST)
Received: from pulsar.staff.proxad.net (pulsar.staff.proxad.net
	[213.228.1.109])
	by smtp8-g19.free.fr (Postfix) with ESMTP id 218AB17F561;
	Mon, 21 Jul 2008 10:27:35 +0200 (CEST)
Date: Mon, 21 Jul 2008 10:27:35 +0200 (CEST)
From: Pomarede Nicolas <npomarede@corp.free.fr>
X-X-Sender: npomarede@localhost
To: System/IJS - Joko <system@ijs.co.id>
cc: pgsql-performance@postgresql.org
Subject: Re: log_statement at postgres.conf
In-Reply-To: <009b01c8eafb$f29fa9e0$12068cc0@edp03>
Message-ID: <Pine.LNX.4.64.0807211023260.11758@localhost>
References: <01a601c8e7e9$41ad45d0$12068cc0@edp03>
	<b41c75520807170229x7768f6d3u68fb6369bafea270@mail.gmail.com>
	<Pine.LNX.4.64.0807171134490.17374@localhost>
	<014d01c8e899$dc597670$12068cc0@edp03>
	<Pine.LNX.4.64.0807181013150.14417@localhost>
	<009b01c8eafb$f29fa9e0$12068cc0@edp03>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200807/168
X-Sequence-Number: 30768

On Mon, 21 Jul 2008, System/IJS - Joko wrote:

> Thx a lot Nicolas,
>
> I finaly success to log query statement because of your simple explanation.
> I have other question:
> 1. Is there posibility to automatically logging that statement to table?

I don't know, never tried that.

> 2. All of that statement is come from every database on my server,
> could I know from which database that statement come?
> or at least I can filter to log only from database X ?

You can modify 'log_line_prefix' to add the database name :
use '%d %t %p %r ' instead of the default '%t %p %r ' for example.

> 3. If I need to log only changed made on my database, then the value of 
> 'log_statement' is 'mod' ?

yes


Nicolas