pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlog_statement at postgres.conf
7+ messages / 3 participants
[nested] [flat]
* log_statement at postgres.conf
@ 2008-07-17 08:43 System/IJS - Joko <system@ijs.co.id>
0 siblings, 1 reply; 7+ messages in thread
From: System/IJS - Joko @ 2008-07-17 08:43 UTC (permalink / raw)
To: pgsql-performance
Dear all,
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..
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: log_statement at postgres.conf
@ 2008-07-17 09:29 Claus Guttesen <kometen@gmail.com>
parent: System/IJS - Joko <system@ijs.co.id>
0 siblings, 1 reply; 7+ messages in thread
From: Claus Guttesen @ 2008-07-17 09:29 UTC (permalink / raw)
To: System/IJS - Joko <system@ijs.co.id>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: log_statement at postgres.conf
@ 2008-07-17 09:40 Pomarede Nicolas <npomarede@corp.free.fr>
parent: Claus Guttesen <kometen@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Pomarede Nicolas @ 2008-07-17 09:40 UTC (permalink / raw)
To: Claus Guttesen <kometen@gmail.com>; +Cc: System/IJS - Joko <system@ijs.co.id>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: log_statement at postgres.conf
@ 2008-07-18 05:48 System/IJS - Joko <system@ijs.co.id>
parent: Pomarede Nicolas <npomarede@corp.free.fr>
0 siblings, 1 reply; 7+ messages in thread
From: System/IJS - Joko @ 2008-07-18 05:48 UTC (permalink / raw)
To: pgsql-performance
>> 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.
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: log_statement at postgres.conf
@ 2008-07-18 08:16 Pomarede Nicolas <npomarede@corp.free.fr>
parent: System/IJS - Joko <system@ijs.co.id>
0 siblings, 1 reply; 7+ messages in thread
From: Pomarede Nicolas @ 2008-07-18 08:16 UTC (permalink / raw)
To: System/IJS - Joko <system@ijs.co.id>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: log_statement at postgres.conf
@ 2008-07-21 06:35 System/IJS - Joko <system@ijs.co.id>
parent: Pomarede Nicolas <npomarede@corp.free.fr>
0 siblings, 1 reply; 7+ messages in thread
From: System/IJS - Joko @ 2008-07-21 06:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: log_statement at postgres.conf
@ 2008-07-21 08:27 Pomarede Nicolas <npomarede@corp.free.fr>
parent: System/IJS - Joko <system@ijs.co.id>
0 siblings, 0 replies; 7+ messages in thread
From: Pomarede Nicolas @ 2008-07-21 08:27 UTC (permalink / raw)
To: System/IJS - Joko <system@ijs.co.id>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-07-21 08:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-17 08:43 log_statement at postgres.conf System/IJS - Joko <system@ijs.co.id>
2008-07-17 09:29 ` Claus Guttesen <kometen@gmail.com>
2008-07-17 09:40 ` Pomarede Nicolas <npomarede@corp.free.fr>
2008-07-18 05:48 ` System/IJS - Joko <system@ijs.co.id>
2008-07-18 08:16 ` Pomarede Nicolas <npomarede@corp.free.fr>
2008-07-21 06:35 ` System/IJS - Joko <system@ijs.co.id>
2008-07-21 08:27 ` Pomarede Nicolas <npomarede@corp.free.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox