Hi,
The docs state that if there are no %-escapes in log_filename, then
it will add the epoch onto the end of the log filename. Is there any
way to turn off this behavior and just use the filename specified in
log_filename? I'd like have all the log data written to a file like
postgresql.log and let logrotate do the rotation.

Thanks for any help!

--
David Lowry
Systems Administrator
Bob Jones University
dlowry@bju.edu
(864)370-1800 ext. 4149

> The docs state that if there are no %-escapes in log_filename, then it will
> add the epoch onto the end of the log filename. Is there any way to turn off
> this behavior and just use the filename specified in log_filename? I'd like
> have all the log data written to a file like postgresql.log and let
> logrotate do the rotation.

Correct me if I'm wrong, but I think you're trying to redirect stderr.
Why not just use syslog? That's what I use and it works very nicely.

Here are the relevant bits from postgresql.conf (that aren't related
to log formatting and changed from the default).
log_destination = 'syslog'
syslog_facility = 'LOCAL1'

Just make sure you pipe the syslog facility to postgresql.log, and
then you can use logrotate.

Peter

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If logrotate would work, we'd never have bothered writing our own
>> substitute. But there's no way for logrotate to repoint postmaster's
>> stderr into other files.

> Uh... can't we just use copytruncate with logroate?

How's that going to reset the offsets in the stderr file descriptors of
the various PG processes? There might be some platforms and
configurations where it will work, but I wouldn't tout it as a portable
approach. I'd also suppose that there are race conditions involved:
you'll either lose or duplicate messages that are emitted while
copytruncate works.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

I'm trying to drop a database in a postgres 8.1.4 instance (x86 Solaris
10 OS), but someone is connected to
the database. What is the best way to find out exactly who is connected?

Thanks,
Naomi

--
------------------------------------------------------------------------
Naomi Walker Chief Information Officer
Mphasis Healthcare Solutions nwalker@mhs.mphasis.com
---An EDS Company 602-604-3100
------------------------------------------------------------------------
"Intellectuals solve problems; geniuses prevent them."
-- Albert Einstein
------------------------------------------------------------------------

-- CONFIDENTIALITY NOTICE --

Information transmitted by thisÂ*e-mail is proprietary to MphasiS and/or its Customers and is intended for use only by the individual or entity towhich it is addressed, and may contain information that isÂ*privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has been forwardedto you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this mail from your records.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Naomi Walker íàïèñà:
> I'm trying to drop a database in a postgres 8.1.4 instance (x86 Solaris
> 10 OS), but someone is connected to
> the database. What is the best way to find out exactly who is connected?
>

select * from pg_stat_activity where datname = <the_db_about_to_be_dropped>;

More here -
http://www.postgresql.org/docs/8.1/s...NG-STATS-VIEWS.


--
Milen A. Radev


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, 26 Oct 2007 17:45:00 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> If logrotate would work, we'd never have bothered writing our own
> >> substitute. But there's no way for logrotate to repoint
> >> postmaster's stderr into other files.
>
> > Uh... can't we just use copytruncate with logroate?
>
> How's that going to reset the offsets in the stderr file descriptors
> of the various PG processes? There might be some platforms and
> configurations where it will work, but I wouldn't tout it as a
> portable approach. I'd also suppose that there are race conditions
> involved: you'll either lose or duplicate messages that are emitted
> while copytruncate works.

Yeah, we have seen that, where the log will be missing a few
statements. Good point.

Joshua D. Drake

>
> regards, tom lane
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHIm07ATb/zqfZUUQRAlu2AJ0VmF7cnzPBDcrR4wmxm37cl89nngCgrtzs
LTmA42K6q0wsm2GkLAd89v4=
=gT7r
-----END PGP SIGNATURE-----

"Milen A. Radev" <milen@radev.net> writes:
> Naomi Walker íàïèñà:
>> I'm trying to drop a database in a postgres 8.1.4 instance (x86 Solaris
>> 10 OS), but someone is connected to
>> the database. What is the best way to find out exactly who is connected?

> select * from pg_stat_activity where datname = <the_db_about_to_be_dropped>;

If that doesn't work because you've got stats collection turned off,
"ps" will help too:

http://www.postgresql.org/docs/8.1/s...onitoring.html

On Solaris, note the Tip at the bottom (not sure if it applies to recent
Solaris versions but a few years ago it was critical).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thanks for jogging my memory! Works like a charm.

Naomi
>> select * from pg_stat_activity where datname = <the_db_about_to_be_dropped>;
>>
>
> If that doesn't work because you've got stats collection turned off,
> "ps" will help too:
>
> http://www.postgresql.org/docs/8.1/s...onitoring.html
>
> On Solaris, note the Tip at the bottom (not sure if it applies to recent
> Solaris versions but a few years ago it was critical).
>
> regards, tom lane
>


--
------------------------------------------------------------------------
Naomi Walker Chief Information Officer
Mphasis Healthcare Solutions nwalker@mhs.mphasis.com
---An EDS Company 602-604-3100
------------------------------------------------------------------------
"Intellectuals solve problems; geniuses prevent them."
-- Albert Einstein
------------------------------------------------------------------------


-- CONFIDENTIALITY NOTICE --

Information transmitted by thisÂ*e-mail is proprietary to MphasiS and/orits Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that isÂ*privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this mail from your records.

On Oct 26, 2007, at 6:42 PM, Joshua D. Drake wrote:
>>>> If logrotate would work, we'd never have bothered writing our own
>>>> substitute. But there's no way for logrotate to repoint
>>>> postmaster's stderr into other files.
>>
>>> Uh... can't we just use copytruncate with logroate?
>>
>> How's that going to reset the offsets in the stderr file descriptors
>> of the various PG processes? There might be some platforms and
>> configurations where it will work, but I wouldn't tout it as a
>> portable approach. I'd also suppose that there are race conditions
>> involved: you'll either lose or duplicate messages that are emitted
>> while copytruncate works.
>
> Yeah, we have seen that, where the log will be missing a few
> statements. Good point.

If I'm not mistaken, it would be more of a move combined with
creating a new file. My impression was that the sequence worked
something like this:

1. Logrotate moves the old log file to a new name, equivalent to
something like this:
mv postgresql.log postgresql.log.1
In the mean time, PG keeps writing to the same file.
2. Logrotate sends a HUP (as configured in the logrotate conf) to the
postmaster process, which causes PG to close the old log file
(postgresql.log.1) and open a new one (postgresql.log).

If I understand correctly, you're saying that this process either
won't work or isn't portable. Is that correct?

BTW, my rational is that we use logrotate for most of our other logs,
so I would like to put PG in the same system. If what I suggested
above won't work, I'll just set up PG to write to something like
postgresql.log_20070101 and write a script to gzip and clean up old
logs.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

David Lowry <dlowry@bju.edu> writes:
> 1. Logrotate moves the old log file to a new name, equivalent to
> something like this:
> mv postgresql.log postgresql.log.1
> In the mean time, PG keeps writing to the same file.
> 2. Logrotate sends a HUP (as configured in the logrotate conf) to the
> postmaster process, which causes PG to close the old log file
> (postgresql.log.1) and open a new one (postgresql.log).

> If I understand correctly, you're saying that this process either
> won't work or isn't portable. Is that correct?

Specifically, PG does not respond to SIGHUP in the way you are imagining
above. The log-file-switch capability is either nonexistent (if no log
collector process) or built in (if log collector is active) and there
doesn't seem any value in driving the latter from outside PG.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org