pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedLog File?
26+ messages / 12 participants
[nested] [flat]
* Log File?
@ 2001-04-12 04:48 Newbie <ketedfordspam@mailcity.com>
2001-04-14 00:56 ` Re: Log File? Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: Newbie @ 2001-04-12 04:48 UTC (permalink / raw)
To: pgsql-general
Our company is deciding whether to choose Postgresql or not and
have a question. Say we back up our database every night and the
next day at 11am, the hard drive crashes. We then have data from
our back up from last night, but, is there a way to have a log file sitting
somewhere
other than the hard drive that will allow us to retrieve all data
from that morning and add that data to the back up? Therefore, we
will have only lost the trasaction that we would have been working
on because it has not been commited. Any answer would be extremely helpful.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Log File?
2001-04-12 04:48 Log File? Newbie <ketedfordspam@mailcity.com>
@ 2001-04-14 00:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 26+ messages in thread
From: Bruce Momjian @ 2001-04-14 00:56 UTC (permalink / raw)
To: Newbie <ketedfordspam@mailcity.com>; +Cc: pgsql-general
You are asking for point-in-time recovery. That should be in 7.2.
> Our company is deciding whether to choose Postgresql or not and
> have a question. Say we back up our database every night and the
> next day at 11am, the hard drive crashes. We then have data from
> our back up from last night, but, is there a way to have a log file sitting
> somewhere
> other than the hard drive that will allow us to retrieve all data
> from that morning and add that data to the back up? Therefore, we
> will have only lost the trasaction that we would have been working
> on because it has not been commited. Any answer would be extremely helpful.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 26+ messages in thread
* log file?
@ 2002-11-05 08:56 Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 10:47 ` Re: log file? Richard Huxton <dev@archonet.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
0 siblings, 2 replies; 26+ messages in thread
From: Florian Litot @ 2002-11-05 08:56 UTC (permalink / raw)
To: pgsql-general
hi
i have got a lot of file which is create
DEBUG: recycled transaction log file 0000000100000037
DEBUG: recycled transaction log file 0000000100000038
DEBUG: recycled transaction log file 0000000100000039
DEBUG: recycled transaction log file 000000010000003A
DEBUG: recycled transaction log file 000000010000003B
DEBUG: recycled transaction log file 000000010000003C
why is it create?
and what is this folder? pg_xlog
how i can reduce the number of this files because it takes a lot of place
thanks
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 10:47 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 26+ messages in thread
From: Richard Huxton @ 2002-11-05 10:47 UTC (permalink / raw)
To: Florian Litot <flitot@besancon.sema.slb.com>; pgsql-general
On Tuesday 05 Nov 2002 8:56 am, Florian Litot wrote:
> hi
> i have got a lot of file which is create
> DEBUG: recycled transaction log file 0000000100000037
> DEBUG: recycled transaction log file 0000000100000038
> DEBUG: recycled transaction log file 0000000100000039
> DEBUG: recycled transaction log file 000000010000003A
> DEBUG: recycled transaction log file 000000010000003B
> DEBUG: recycled transaction log file 000000010000003C
> why is it create?
> and what is this folder? pg_xlog
> how i can reduce the number of this files because it takes a lot of place
> thanks
PG writes all transactions to disk before actually making changes so that you
can recover in the event of a crash. It splits this transaction log into
chunks and recycles them when all the transactions it refers to are no longer
of interest.
Read up on WAL for more details and configuration options in the
postgresql.conf file.
--
Richard Huxton
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 11:14 ` Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:47 ` Re: log file? Richard Huxton <dev@archonet.com>
2002-11-05 12:48 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 16:11 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
1 sibling, 3 replies; 26+ messages in thread
From: Florian Litot @ 2002-11-05 11:14 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-general
At 11:47 05/11/02, Richard Huxton wrote:
>PG writes all transactions to disk before actually making changes so that you
>can recover in the event of a crash. It splits this transaction log into
>chunks and recycles them when all the transactions it refers to are no longer
>of interest.
but the problem is it never recycle this file so i have a got a px_log 2Go
how i can resolve this problem?
why it doesn't recycle?
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 11:47 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 26+ messages in thread
From: Richard Huxton @ 2002-11-05 11:47 UTC (permalink / raw)
To: Florian Litot <flitot@besancon.sema.slb.com>; pgsql-general
On Tuesday 05 Nov 2002 11:14 am, Florian Litot wrote:
> At 11:47 05/11/02, Richard Huxton wrote:
> >PG writes all transactions to disk before actually making changes so that
> > you can recover in the event of a crash. It splits this transaction log
> > into chunks and recycles them when all the transactions it refers to are
> > no longer of interest.
>
> but the problem is it never recycle this file so i have a got a px_log 2Go
> how i can resolve this problem?
> why it doesn't recycle?
According to the log you posted it was being recycled.
How many files are there in pg_xlog and what dates do they have?
What are the WAL settings in your postgresql.conf file?
Do you have any clients that have been running a long time?
--
Richard Huxton
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 12:48 ` Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 13:17 ` Re: log file? Richard Huxton <dev@archonet.com>
2 siblings, 1 reply; 26+ messages in thread
From: Florian Litot @ 2002-11-05 12:48 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-general
At 12:47 05/11/02, Richard Huxton wrote:
>On Tuesday 05 Nov 2002 11:14 am, Florian Litot wrote:
> > At 11:47 05/11/02, Richard Huxton wrote:
> > >PG writes all transactions to disk before actually making changes so that
> > > you can recover in the event of a crash. It splits this transaction log
> > > into chunks and recycles them when all the transactions it refers to are
> > > no longer of interest.
> >
> > but the problem is it never recycle this file so i have a got a px_log 2Go
> > how i can resolve this problem?
> > why it doesn't recycle?
>
>According to the log you posted it was being recycled.
>
>How many files are there in pg_xlog and what dates do they have?
>What are the WAL settings in your postgresql.conf file?
>Do you have any clients that have been running a long time?
>
>--
> Richard Huxton
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: 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
i have got 66 files
i have got a new file to 16 minutes
#
# Write-ahead log (WAL)
#
wal_files = 64 # range 0-64
#wal_sync_method = fsync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300 # in seconds, range 30-3600
#fsync = true
it's standard
i make some insertion i make 1 insertion every 2 seconds
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 12:48 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 13:17 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 26+ messages in thread
From: Richard Huxton @ 2002-11-05 13:17 UTC (permalink / raw)
To: Florian Litot <flitot@besancon.sema.slb.com>; pgsql-general
On Tuesday 05 Nov 2002 12:48 pm, Florian Litot wrote:
> At 12:47 05/11/02, Richard Huxton wrote:
> >How many files are there in pg_xlog and what dates do they have?
> >What are the WAL settings in your postgresql.conf file?
> >Do you have any clients that have been running a long time?
> i have got 66 files
> i have got a new file to 16 minutes
> wal_files = 64 # range 0-64
> #wal_sync_method = fsync # the default varies across platforms:
> # # fsync, fdatasync, open_sync, or open_datasync
> #wal_debug = 0 # range 0-16
> #commit_delay = 0 # range 0-100000
> #commit_siblings = 5 # range 1-1000
> #checkpoint_segments = 3 # in logfile segments (16MB each), min 1
> #checkpoint_timeout = 300 # in seconds, range 30-3600
> #fsync = true
>
> it's standard
> i make some insertion i make 1 insertion every 2 seconds
I don't think I can explain better than by quoting from the manual (for the
full details see the WAL implementation and configuration sections):
"The number of 16MB segment files will always be at least WAL_FILES + 1, and
will normally not exceed WAL_FILES + MAX(WAL_FILES, CHECKPOINT_SEGMENTS) + 1.
This may be used to estimate space requirements for WAL. Ordinarily, when an
old log segment files are no longer needed, they are recycled (renamed to
become the next sequential future segments). If, due to a short-term peak of
log output rate, there are more than WAL_FILES + MAX(WAL_FILES,
CHECKPOINT_SEGMENTS) + 1 segment files, then unneeded segment files will be
deleted instead of recycled until the system gets back under this limit. (If
this happens on a regular basis, WAL_FILES should be increased to avoid it.
Deleting log segments that will only have to be created again later is
expensive and pointless.) "
Given a wal_files setting of 64 you're always going to have 65 files. Keep an
eye on it and see if the number of files keeps increasing - if not, you don't
have a problem. If you want to reduce the number of files, reduce wal_files.
--
Richard Huxton
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 16:11 ` Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 16:57 ` Re: log file? Richard Huxton <dev@archonet.com>
2002-11-05 17:24 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2 siblings, 2 replies; 26+ messages in thread
From: Florian Litot @ 2002-11-05 16:11 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-general
At 12:47 05/11/02, Richard Huxton wrote:
>On Tuesday 05 Nov 2002 11:14 am, Florian Litot wrote:
> > At 11:47 05/11/02, Richard Huxton wrote:
> > >PG writes all transactions to disk before actually making changes so that
> > > you can recover in the event of a crash. It splits this transaction log
> > > into chunks and recycles them when all the transactions it refers to are
> > > no longer of interest.
> >
> > but the problem is it never recycle this file so i have a got a px_log 2Go
> > how i can resolve this problem?
> > why it doesn't recycle?
>
>According to the log you posted it was being recycled.
>
>How many files are there in pg_xlog and what dates do they have?
>What are the WAL settings in your postgresql.conf file?
>Do you have any clients that have been running a long time?
>
>--
> Richard Huxton
when this files is recycling?
because my database is always connecting
what can i do?
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 16:11 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 16:57 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 26+ messages in thread
From: Richard Huxton @ 2002-11-05 16:57 UTC (permalink / raw)
To: Florian Litot <flitot@besancon.sema.slb.com>; +Cc: pgsql-general
On Tuesday 05 Nov 2002 4:11 pm, you wrote:
> At 12:47 05/11/02, Richard Huxton wrote:
> > > but the problem is it never recycle this file so i have a got a px_log
> > > 2Go how i can resolve this problem?
> > > why it doesn't recycle?
> >
> >According to the log you posted it was being recycled.
> >
> >How many files are there in pg_xlog and what dates do they have?
> >What are the WAL settings in your postgresql.conf file?
> >Do you have any clients that have been running a long time?
> >
> >--
> > Richard Huxton
>
> when this files is recycling?
> because my database is always connecting
> what can i do?
As long as the number of files isn't increasing, all is well. The only reason
old files wouldn't get recycled is if a client might refer to a transaction
held in it. It doesn't matter if you have lots of clients running lots of
queries, the files will still get recycled.
See my previous reply for why you have 66 files.
--
Richard Huxton
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 16:11 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 17:24 ` Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 18:20 ` Re: log file? dev@archonet.com
1 sibling, 1 reply; 26+ messages in thread
From: Florian Litot @ 2002-11-05 17:24 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-general
the number of files increase
my database is always connected
i use a jdbc driver and a program is always connected to the database and
send sometimes insertion
hav you got an idea?
At 17:57 05/11/02, Richard Huxton wrote:
>On Tuesday 05 Nov 2002 4:11 pm, you wrote:
> > At 12:47 05/11/02, Richard Huxton wrote:
> > > > but the problem is it never recycle this file so i have a got a px_log
> > > > 2Go how i can resolve this problem?
> > > > why it doesn't recycle?
> > >
> > >According to the log you posted it was being recycled.
> > >
> > >How many files are there in pg_xlog and what dates do they have?
> > >What are the WAL settings in your postgresql.conf file?
> > >Do you have any clients that have been running a long time?
> > >
> > >--
> > > Richard Huxton
> >
> > when this files is recycling?
> > because my database is always connecting
> > what can i do?
>
>As long as the number of files isn't increasing, all is well. The only reason
>old files wouldn't get recycled is if a client might refer to a transaction
>held in it. It doesn't matter if you have lots of clients running lots of
>queries, the files will still get recycled.
>
>See my previous reply for why you have 66 files.
>
>--
> Richard Huxton
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: log file?
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 16:11 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 17:24 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
@ 2002-11-05 18:20 ` dev@archonet.com
0 siblings, 0 replies; 26+ messages in thread
From: dev@archonet.com @ 2002-11-05 18:20 UTC (permalink / raw)
To: flitot@besancon.sema.slb.com; +Cc: dev@archonet.com; pgsql-general
> the number of files increase
> my database is always connected
> i use a jdbc driver and a program is always connected to the database
> and send sometimes insertion
> hav you got an idea?
The only thing I know of that can cause this is an old transaction still
being required because a connected client might need it. Could this be
the case? I don't know what version of PG you are using, but recent ones
let you see what queries are in progress via the statistics views. See
pg_stat... in the manual and the mailing list archives.
It's evening here, so I'll post again tomorrow.
--
Richard Huxton
^ permalink raw reply [nested|flat] 26+ messages in thread
* Log file
@ 2018-10-29 04:03 Igor Korot <ikorot01@gmail.com>
2018-10-29 04:01 ` Re: Log file Michael Paquier <michael@paquier.xyz>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: Igor Korot @ 2018-10-29 04:03 UTC (permalink / raw)
To: PostgreSQL ODBC list <pgsql-odbc@postgresql.org>
Hi, ALL,
I'm trying to test the functionality of logging on my older Mac with
PostgreSQL 9.1.
I see that the logile is created with the owner of postgres and the
group of wheel.
Is there a way to make it open with "<current_user><current_user>"?
Or I will have to change the owner/group manuall every time I will
access the file?
Thank you.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
@ 2018-10-29 04:01 ` Michael Paquier <michael@paquier.xyz>
2018-10-29 16:56 ` Re: Log file Igor Korot <ikorot01@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Michael Paquier @ 2018-10-29 04:01 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; +Cc: PostgreSQL ODBC list <pgsql-odbc@postgresql.org>
On Mon, Oct 29, 2018 at 12:03:04AM -0400, Igor Korot wrote:
> I'm trying to test the functionality of logging on my older Mac with
> PostgreSQL 9.1.
>
> I see that the logile is created with the owner of postgres and the
> group of wheel.
>
> Is there a way to make it open with "<current_user><current_user>"?
> Or I will have to change the owner/group manuall every time I will
> access the file?
This question looks unrelated to the ODBC driver, I think that you
should contact the mailing list pgsql-novice instead.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20181029040127.GH14242@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 04:01 ` Re: Log file Michael Paquier <michael@paquier.xyz>
@ 2018-10-29 16:56 ` Igor Korot <ikorot01@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Igor Korot @ 2018-10-29 16:56 UTC (permalink / raw)
To: michael@paquier.xyz; +Cc: PostgreSQL ODBC list <pgsql-odbc@postgresql.org>
Michael,
On Sun, Oct 28, 2018 at 11:01 PM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, Oct 29, 2018 at 12:03:04AM -0400, Igor Korot wrote:
> > I'm trying to test the functionality of logging on my older Mac with
> > PostgreSQL 9.1.
> >
> > I see that the logile is created with the owner of postgres and the
> > group of wheel.
> >
> > Is there a way to make it open with "<current_user><current_user>"?
> > Or I will have to change the owner/group manuall every time I will
> > access the file?
>
> This question looks unrelated to the ODBC driver, I think that you
> should contact the mailing list pgsql-novice instead.
Yes, sorry.
Forwarded to the appropriate list.
Thank you.
> --
> Michael
^ permalink raw reply [nested|flat] 26+ messages in thread
* Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
@ 2018-10-29 16:56 ` Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Igor Korot @ 2018-10-29 16:56 UTC (permalink / raw)
To: pgsql-general
I initially sent this to the ODBC list.'
Thank you for any suggestions.
---------- Forwarded message ---------
From: Igor Korot <ikorot01@gmail.com>
Date: Sun, Oct 28, 2018 at 11:03 PM
Subject: Log file
To: PostgreSQL ODBC list <pgsql-odbc@postgresql.org>
Hi, ALL,
I'm trying to test the functionality of logging on my older Mac with
PostgreSQL 9.1.
I see that the logile is created with the owner of postgres and the
group of wheel.
Is there a way to make it open with "<current_user><current_user>"?
Or I will have to change the owner/group manuall every time I will
access the file?
Thank you.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
@ 2018-10-29 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-11-11 16:24 ` Re: Fwd: Log file Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 2 replies; 26+ messages in thread
From: Tom Lane @ 2018-10-29 18:56 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; +Cc: pgsql-general
Igor Korot <ikorot01@gmail.com> writes:
> I'm trying to test the functionality of logging on my older Mac with
> PostgreSQL 9.1.
> I see that the logile is created with the owner of postgres and the
> group of wheel.
Well, more specifically, it's created under the OS user & group that
the server is running under.
> Is there a way to make it open with "<current_user><current_user>"?
What current user? The SQL user name might not correspond to any
OS-level entity at all. Even if it did, it's quite unlikely that
the OS would permit the server process to create files owned by
some other OS user --- doing so would be a giant security risk.
> Or I will have to change the owner/group manuall every time I will
> access the file?
You can set up the log files as readable by the OS group of the server
(see log_file_mode), and then grant membership in that group to whichever
OS accounts you trust. You may also need to move the log directory
out from under $PGDATA to make that work, since PG doesn't like
world-readable data directories.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-10-29 20:48 ` Igor Korot <ikorot01@gmail.com>
2018-10-29 21:08 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Igor Korot @ 2018-10-29 20:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
Hi, Tom,
On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Igor Korot <ikorot01@gmail.com> writes:
> > I'm trying to test the functionality of logging on my older Mac with
> > PostgreSQL 9.1.
> > I see that the logile is created with the owner of postgres and the
> > group of wheel.
>
> Well, more specifically, it's created under the OS user & group that
> the server is running under.
OK, that clarifies it a little.
I thought I could just crate a user called "igor", give him all
"postgres" permissions
and login to the server as "igor" and not "postgres" every time I test.
But since the server will probably run from the "postgres" account
during the machine
start-up that won't work.
>
> > Is there a way to make it open with "<current_user><current_user>"?
>
> What current user? The SQL user name might not correspond to any
> OS-level entity at all. Even if it did, it's quite unlikely that
> the OS would permit the server process to create files owned by
> some other OS user --- doing so would be a giant security risk.
The current_user = user who logged in to the machine and open the
current session.
If I log in to the machine as "igor" and try to create a file in vi/nano/notepad
I will be the owner of this file and the group will be the group to which
I belong as a user.
And I'm talking about specifically to the result of the "ls -la"
output from the *nix/OSX
POV.
>
> > Or I will have to change the owner/group manuall every time I will
> > access the file?
>
> You can set up the log files as readable by the OS group of the server
> (see log_file_mode), and then grant membership in that group to whichever
> OS accounts you trust. You may also need to move the log directory
> out from under $PGDATA to make that work, since PG doesn't like
> world-readable data directories.
I'm trying to make the log file of PG readable of the user who logs in
to the current
OS session. I don't need a write permission, just read.
Because my program will not be started from the "postgres" account.
Thank you.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
@ 2018-10-29 21:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-10-31 04:32 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2018-10-29 21:08 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; +Cc: pgsql-general
Igor Korot <ikorot01@gmail.com> writes:
> On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You can set up the log files as readable by the OS group of the server
>> (see log_file_mode), and then grant membership in that group to whichever
>> OS accounts you trust. You may also need to move the log directory
>> out from under $PGDATA to make that work, since PG doesn't like
>> world-readable data directories.
> I'm trying to make the log file of PG readable of the user who logs in
> to the current
> OS session. I don't need a write permission, just read.
> Because my program will not be started from the "postgres" account.
Well, any such setup is a serious security hole in itself, because
there is likely to be sensitive data in the postmaster log, eg
passwords. (Remember that the log file is global to the whole cluster,
it will not contain just data relevant to the current session.)
You should only grant access to people who you trust at more or less
the level of trust you'd put in the installation DBA.
It may be that these concerns are all irrelevant to you because it's
a single-user installation anyway, but they're not irrelevant to
people running multi-user installations. So that's why you can't
get Postgres to do it. In a single-user installation, maybe you
should just launch the postmaster as that user.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 21:08 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-10-31 04:32 ` Igor Korot <ikorot01@gmail.com>
2018-10-30 16:20 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Igor Korot @ 2018-10-31 04:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
Hi, Tom,
On Mon, Oct 29, 2018 at 5:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Igor Korot <ikorot01@gmail.com> writes:
> > On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> You can set up the log files as readable by the OS group of the server
> >> (see log_file_mode), and then grant membership in that group to whichever
> >> OS accounts you trust. You may also need to move the log directory
> >> out from under $PGDATA to make that work, since PG doesn't like
> >> world-readable data directories.
>
> > I'm trying to make the log file of PG readable of the user who logs in
> > to the current
> > OS session. I don't need a write permission, just read.
> > Because my program will not be started from the "postgres" account.
>
> Well, any such setup is a serious security hole in itself, because
> there is likely to be sensitive data in the postmaster log, eg
> passwords. (Remember that the log file is global to the whole cluster,
> it will not contain just data relevant to the current session.)
> You should only grant access to people who you trust at more or less
> the level of trust you'd put in the installation DBA.
>
> It may be that these concerns are all irrelevant to you because it's
> a single-user installation anyway, but they're not irrelevant to
> people running multi-user installations. So that's why you can't
> get Postgres to do it. In a single-user installation, maybe you
> should just launch the postmaster as that user.
>
> regards, tom lane
OK, I understand.
Thank you.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 21:08 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-31 04:32 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
@ 2018-10-30 16:20 ` Igor Korot <ikorot01@gmail.com>
2018-10-31 07:00 ` Re: Fwd: Log file Laurenz Albe <laurenz.albe@cybertec.at>
2018-10-31 12:51 ` Re: Fwd: Log file Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 26+ messages in thread
From: Igor Korot @ 2018-10-30 16:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
Now is there a command to flush the log - delete the content of it?
All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.
On Wed, Oct 31, 2018 at 12:32 AM Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, Tom,
>
> On Mon, Oct 29, 2018 at 5:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Igor Korot <ikorot01@gmail.com> writes:
> > > On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > >> You can set up the log files as readable by the OS group of the server
> > >> (see log_file_mode), and then grant membership in that group to whichever
> > >> OS accounts you trust. You may also need to move the log directory
> > >> out from under $PGDATA to make that work, since PG doesn't like
> > >> world-readable data directories.
> >
> > > I'm trying to make the log file of PG readable of the user who logs in
> > > to the current
> > > OS session. I don't need a write permission, just read.
> > > Because my program will not be started from the "postgres" account.
> >
> > Well, any such setup is a serious security hole in itself, because
> > there is likely to be sensitive data in the postmaster log, eg
> > passwords. (Remember that the log file is global to the whole cluster,
> > it will not contain just data relevant to the current session.)
> > You should only grant access to people who you trust at more or less
> > the level of trust you'd put in the installation DBA.
> >
> > It may be that these concerns are all irrelevant to you because it's
> > a single-user installation anyway, but they're not irrelevant to
> > people running multi-user installations. So that's why you can't
> > get Postgres to do it. In a single-user installation, maybe you
> > should just launch the postmaster as that user.
> >
> > regards, tom lane
>
> OK, I understand.
>
> Thank you.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 21:08 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-31 04:32 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-30 16:20 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
@ 2018-10-31 07:00 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-06 18:06 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Laurenz Albe @ 2018-10-31 07:00 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
Igor Korot wrote:
> Now is there a command to flush the log - delete the content of it?
No, managing the logs is outside of PostgreSQL's responsibility.
But it shouldn't be a problem to do this outside the database.
Of course you could write a funtion in PostgreSQL that uses one
of the "untrusted" procedural languages to do it for you.
> All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.
You mean DROP, right?
You can set "log_statement = 'ddl'" for that.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 21:08 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-31 04:32 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-30 16:20 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-31 07:00 ` Re: Fwd: Log file Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-11-06 18:06 ` Igor Korot <ikorot01@gmail.com>
2018-11-06 20:35 ` Re: Fwd: Log file Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Igor Korot @ 2018-11-06 18:06 UTC (permalink / raw)
To: laurenz.albe@cybertec.at; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general
Hi, guys,
For some reason this reply was in my "Spam" folder (gmail service).
I guess moving to the new mailer service is not completely safe for "Spam".
So replying now - apologies to be late.
On Wed, Oct 31, 2018 at 2:00 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> Igor Korot wrote:
> > Now is there a command to flush the log - delete the content of it?
>
> No, managing the logs is outside of PostgreSQL's responsibility.
>
> But it shouldn't be a problem to do this outside the database.
> Of course you could write a funtion in PostgreSQL that uses one
> of the "untrusted" procedural languages to do it for you.
Ok.
I guess I will have to write such function.
>
> > All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.
>
> You mean DROP, right?
Yes, sorry.
>
> You can set "log_statement = 'ddl'" for that.
That's what I did.
But now I need to find a way to read the log file by the regular user
and not the "postgres" one.
Thank you.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 21:08 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-31 04:32 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-30 16:20 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-31 07:00 ` Re: Fwd: Log file Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-06 18:06 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
@ 2018-11-06 20:35 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Ron @ 2018-11-06 20:35 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 11/06/2018 12:06 PM, Igor Korot wrote:
[snip]
> Ok.
> I guess I will have to write such function.
Cron and the relevant log_* config variables should solve your problems.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 21:08 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-31 04:32 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-30 16:20 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
@ 2018-10-31 12:51 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 26+ messages in thread
From: Adrian Klaver @ 2018-10-31 12:51 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
On 10/30/18 9:20 AM, Igor Korot wrote:
> Now is there a command to flush the log - delete the content of it?
The only thing I know of is:
https://www.postgresql.org/docs/10/static/functions-admin.html
pg_rotate_logfile() boolean Rotate server's log file
>
> All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.
>
>
> On Wed, Oct 31, 2018 at 12:32 AM Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, Tom,
>>
>> On Mon, Oct 29, 2018 at 5:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>
>>> Igor Korot <ikorot01@gmail.com> writes:
>>>> On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>>> You can set up the log files as readable by the OS group of the server
>>>>> (see log_file_mode), and then grant membership in that group to whichever
>>>>> OS accounts you trust. You may also need to move the log directory
>>>>> out from under $PGDATA to make that work, since PG doesn't like
>>>>> world-readable data directories.
>>>
>>>> I'm trying to make the log file of PG readable of the user who logs in
>>>> to the current
>>>> OS session. I don't need a write permission, just read.
>>>> Because my program will not be started from the "postgres" account.
>>>
>>> Well, any such setup is a serious security hole in itself, because
>>> there is likely to be sensitive data in the postmaster log, eg
>>> passwords. (Remember that the log file is global to the whole cluster,
>>> it will not contain just data relevant to the current session.)
>>> You should only grant access to people who you trust at more or less
>>> the level of trust you'd put in the installation DBA.
>>>
>>> It may be that these concerns are all irrelevant to you because it's
>>> a single-user installation anyway, but they're not irrelevant to
>>> people running multi-user installations. So that's why you can't
>>> get Postgres to do it. In a single-user installation, maybe you
>>> should just launch the postmaster as that user.
>>>
>>> regards, tom lane
>>
>> OK, I understand.
>>
>> Thank you.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Fwd: Log file
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-11-11 16:24 ` Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 0 replies; 26+ messages in thread
From: Peter J. Holzer @ 2018-11-11 16:24 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2018-10-29 14:56:06 -0400, Tom Lane wrote:
> Igor Korot <ikorot01@gmail.com> writes:
> > Or I will have to change the owner/group manuall every time I will
> > access the file?
>
> You can set up the log files as readable by the OS group of the server
> (see log_file_mode), and then grant membership in that group to whichever
> OS accounts you trust.
Another way is to use ACLs. Set a default ACL on the log directory which
enforces read permission for some users or groups on all newly created
files.
This is a bit more fine-grained and may be better suited for situations
where different people should be able to read the logs of different
servers. I also find that ACLs are more likely to survive "corrective"
actions by update or init scripts.
Another way would be to log via syslog and configure syslogd to split
the log files according to the username embedded in the log message.
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/;
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20181111162411.qph4hoo4jqwobboe@hjp.at/2-signature.asc)
download
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2018-11-11 16:24 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-12 04:48 Log File? Newbie <ketedfordspam@mailcity.com>
2001-04-14 00:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-05 08:56 log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 10:47 ` Re: log file? Richard Huxton <dev@archonet.com>
2002-11-05 11:14 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 11:47 ` Re: log file? Richard Huxton <dev@archonet.com>
2002-11-05 12:48 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 13:17 ` Re: log file? Richard Huxton <dev@archonet.com>
2002-11-05 16:11 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 16:57 ` Re: log file? Richard Huxton <dev@archonet.com>
2002-11-05 17:24 ` Re: log file? Florian Litot <flitot@besancon.sema.slb.com>
2002-11-05 18:20 ` Re: log file? dev@archonet.com
2018-10-29 04:03 Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 04:01 ` Re: Log file Michael Paquier <michael@paquier.xyz>
2018-10-29 16:56 ` Re: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 16:56 ` Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 18:56 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-29 20:48 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-29 21:08 ` Re: Fwd: Log file Tom Lane <tgl@sss.pgh.pa.us>
2018-10-31 04:32 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-30 16:20 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-10-31 07:00 ` Re: Fwd: Log file Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-06 18:06 ` Re: Fwd: Log file Igor Korot <ikorot01@gmail.com>
2018-11-06 20:35 ` Re: Fwd: Log file Ron <ronljohnsonjr@gmail.com>
2018-10-31 12:51 ` Re: Fwd: Log file Adrian Klaver <adrian.klaver@aklaver.com>
2018-11-11 16:24 ` Re: Fwd: Log file Peter J. Holzer <hjp-pgsql@hjp.at>
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