Re: [HACKERS] serverlog function (log_destination file)
Magnus Hagander wrote: Specifically about the logs, I still think there is a lot of value to being able to read the logs remotely even if you can't restart postmaster. Since I believe that retrieving the logs easily without server file access is a feature that's welcomed by many users, here's my proposal. The attached diff will - add a guc-variable log_filename - extend log_destination to accept the keyword 'file' - elog to that file if configured - provide two functions pg_logfile_length() and pg_logfile to obtain the contents. int4 pg_logfile_length() cstring pg_logfile(int4 size, int4 position) size (may be null meaning max) is the chunk size (max: currently 5) position (may be null meaning -size) is the start position; positive counting from log file start, negative from end. Regards, Andreas Index: backend/postmaster/postmaster.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v retrieving revision 1.402 diff -u -r1.402 postmaster.c --- backend/postmaster/postmaster.c 3 Jun 2004 02:08:03 - 1.402 +++ backend/postmaster/postmaster.c 8 Jun 2004 18:07:30 - @@ -532,6 +532,9 @@ /* If timezone is not set, determine what the OS uses */ pg_timezone_initialize(); +/* open alternate logfile, if any */ + LogFileOpen(); + #ifdef EXEC_BACKEND write_nondefault_variables(PGC_POSTMASTER); #endif Index: backend/storage/ipc/ipc.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/storage/ipc/ipc.c,v retrieving revision 1.87 diff -u -r1.87 ipc.c --- backend/storage/ipc/ipc.c 12 Dec 2003 18:45:09 - 1.87 +++ backend/storage/ipc/ipc.c 8 Jun 2004 18:07:31 - @@ -111,6 +111,8 @@ on_proc_exit_list[on_proc_exit_index].arg); elog(DEBUG3, exit(%d), code); + + LogFileClose(); exit(code); } Index: backend/utils/adt/misc.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/misc.c,v retrieving revision 1.34 diff -u -r1.34 misc.c --- backend/utils/adt/misc.c2 Jun 2004 21:29:29 - 1.34 +++ backend/utils/adt/misc.c8 Jun 2004 18:07:36 - @@ -103,3 +103,64 @@ { PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0),SIGINT)); } + +Datum pg_logfile_length(PG_FUNCTION_ARGS) +{ + extern FILE *logfile; // in elog.c + + if (logfile) + PG_RETURN_INT32(ftell(logfile)); + PG_RETURN_INT32(0); +} + + +#define MAXLOGFILECHUNK 5 +Datum pg_logfile(PG_FUNCTION_ARGS) +{ + size_t size=MAXLOGFILECHUNK; + char *buf=0; + size_t nbytes; + + char *filename = LogFileName(); + if (filename) + { + if (!PG_ARGISNULL(0)) + size = PG_GETARG_INT32(0); + if (size MAXLOGFILECHUNK) + { + size = MAXLOGFILECHUNK; + ereport(WARNING, + (errcode(ERRCODE_OUT_OF_MEMORY), +errmsg(Maximum size is %d., MAXLOGFILECHUNK))); + } + + FILE *f=fopen(filename, r); + if (f) + { + if (PG_ARGISNULL(1)) + fseek(f, -size, SEEK_END); + else + { + long pos = PG_GETARG_INT32(1); + if (pos = 0) + fseek(f, pos, SEEK_SET); + else + fseek(f, pos, SEEK_END); + } + buf = palloc(size+1); + nbytes = fread(buf, 1, size, f); + buf[nbytes] = 0; + + fclose(f); + } + else + { + ereport(WARNING, + (errcode(ERRCODE_NO_DATA), +errmsg(Could not open log file %s., filename))); + } + free(filename); + } + + PG_RETURN_CSTRING(buf); +} Index: backend/utils/error/elog.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/error/elog.c,v retrieving revision 1.140 diff -u -r1.140 elog.c --- backend/utils/error/elog.c 3 Jun 2004 02:08:04 - 1.140 +++ backend/utils/error/elog.c 8 Jun 2004 18:07:39 - @@ -71,8 +71,10 @@ PGErrorVerbosity Log_error_verbosity = PGERROR_VERBOSE; char *Log_line_prefix = NULL; /* format for extra log line info */ unsigned int Log_destination = LOG_DESTINATION_STDERR; +char *Log_filename = NULL; bool
Re: [HACKERS] serverlog function
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: For adminstrator's convenience, I'd like to see a function that returns the serverlog. What do you mean by returns the serverlog? Are you going to magically recover data that has gone to stderr or the syslogd daemon? If so, how? And why wouldn't you just go and look at the log file, instead? I'd like to see the serverlog even if I can't go and look at the log file, because I don't have file access to the server. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] serverlog function (log_destination file)
Andreas Pflug wrote: Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: For adminstrator's convenience, I'd like to see a function that returns the serverlog. What do you mean by returns the serverlog? Are you going to magically recover data that has gone to stderr or the syslogd daemon? Hm, what I missed is that pg_ctl's -l parameter converts to a simple stderr redirection, and it's hardly possible to find out where it's going. This could be solved by a file log_destination option or a freopen(...,stderr) from a guc variable. Regards, Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] serverlog function (log_destination file)
Andreas Pflug [EMAIL PROTECTED] writes: Hm, what I missed is that pg_ctl's -l parameter converts to a simple stderr redirection, and it's hardly possible to find out where it's going. This could be solved by a file log_destination option or a freopen(...,stderr) from a guc variable. Any such patch would be rejected, because it would break the ability to pipe stderr into another program (such as logrotate). And what of the syslog case? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] serverlog function
Andreas Pflug wrote: Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: For adminstrator's convenience, I'd like to see a function that returns the serverlog. What do you mean by returns the serverlog? Are you going to magically recover data that has gone to stderr or the syslogd daemon? If so, how? And why wouldn't you just go and look at the log file, instead? I'd like to see the serverlog even if I can't go and look at the log file, because I don't have file access to the server. Understand. Unfortunately, we don't allow such functionality. The only solution I can think of is to use syslog and send the logs to a machine where you do have access. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] serverlog function (log_destination file)
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: 07 June 2004 14:30 To: Andreas Pflug Cc: PostgreSQL Development Subject: Re: [HACKERS] serverlog function (log_destination file) Andreas Pflug [EMAIL PROTECTED] writes: Hm, what I missed is that pg_ctl's -l parameter converts to a simple stderr redirection, and it's hardly possible to find out where it's going. This could be solved by a file log_destination option or a freopen(...,stderr) from a guc variable. Any such patch would be rejected, because it would break the ability to pipe stderr into another program (such as logrotate). And what of the syslog case? I see the problems with the existing mechanisms, but just to float an idea - what about adding a GUC variable that can be used to specify an amount of shared memory to use as a fifo area in which a copy of the log output is stored for return to clients that might want it (accessing it via internal functions)? Regards, Dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] serverlog function (log_destination file)
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Hm, what I missed is that pg_ctl's -l parameter converts to a simple stderr redirection, and it's hardly possible to find out where it's going. This could be solved by a file log_destination option or a freopen(...,stderr) from a guc variable. Any such patch would be rejected, because it would break the ability to pipe stderr into another program (such as logrotate). And what of the syslog case? Might it be sensible to have pg_ctl write its log destination (if any) out to a file in the data dir? That plus the log_destination setting might provide enough info at least for some common cases. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] serverlog function
Bruce Momjian wrote: Andreas Pflug wrote: I'd like to see the serverlog even if I can't go and look at the log file, because I don't have file access to the server. Understand. Unfortunately, we don't allow such functionality. The only solution I can think of is to use syslog and send the logs to a machine where you do have access. What I mean is not how to set up a log solution, but how to offer a convenient way for the windows spoiled admins who like to have a gui for all kind of stuff, and just wants to hit a button show server log, regardless of server location and platform type. This is a request that was recommended for pgadmin3. AFAICS, we have some alternatives: - try to grab the currently created files/syslog/eventlog. Seems hard to do, because we'd depend on additional external tools. - redirect stderr to a postgresql.conf known file. Disadvantage: breaks piping. - maintain a sharedMem for the latest messages. Disadvantage: limited space, no access to older entries after postmaster restart. - additional log_destination file. Disadvantage: Yet Another File besides the redirected stderr, but this seems a minor problem. Regards, Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] serverlog function (log_destination file)
Dave Page [EMAIL PROTECTED] writes: ... what about adding a GUC variable that can be used to specify an amount of shared memory to use as a fifo area in which a copy of the log output is stored for return to clients that might want it (accessing it via internal functions)? No, that's a nonstarter, because having the postmaster log into shared memory means that the postmaster probably goes down too anytime a backend crashes. The shared area would have to have a mutual-exclusion lock, and we definitely do not want the postmaster participating in any lock protocols. If I were trying to solve Andreas' problem, I'd pipe stderr to some program that stores recent log output in a file that I know the location of and can read from the hypothetical log-grabber function. Actually I don't see that there's any need to involve Postgres itself in this issue at all --- seems like the only agreement needed is between the GUI and the postmaster launching script about where the log file is. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] serverlog function
On Mon, Jun 07, 2004 at 16:06:53 +0200, Andreas Pflug [EMAIL PROTECTED] wrote: AFAICS, we have some alternatives: You could also pipe the logs to a program that writes them to a table in the database. As long as the logging level wasn't set so high that inserting the log entries was logged. This should be a fairly simple program to write and could be provided to people that wanted to provide this feature for their server. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] serverlog function (log_destination file)
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 07 June 2004 15:32 To: Dave Page Cc: Andreas Pflug; PostgreSQL Development Subject: Re: [HACKERS] serverlog function (log_destination file) If I were trying to solve Andreas' problem, I'd pipe stderr to some program that stores recent log output in a file that I know the location of and can read from the hypothetical log-grabber function. Actually I don't see that there's any need to involve Postgres itself in this issue at all --- seems like the only agreement needed is between the GUI and the postmaster launching script about where the log file is. Thanks Tom. I wonder if we (the pgAdmin team) finally need to bite the proverbial bullet and write a helper daemon that can allow access to logs as well as config files and pg_ctl etc. as an optional extra component. Regards, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] serverlog function (log_destination file)
Dave Page [EMAIL PROTECTED] writes: Thanks Tom. I wonder if we (the pgAdmin team) finally need to bite the proverbial bullet and write a helper daemon that can allow access to logs as well as config files and pg_ctl etc. as an optional extra component. Red Hat's RHDB group already did a fair amount of work on such a tool: see Control Center available from http://sources.redhat.com/rhdb/ IIRC it's all in Java and thus at least theoretically portable to Windows. At the moment Red Hat is devoting no resources to it (and so I think it's stuck in the 7.3 time frame) but I'd be very happy to see someone else pick it up and work on it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] serverlog function (log_destination file)
Tom Lane wrote: If I were trying to solve Andreas' problem, I'd pipe stderr to some program that stores recent log output in a file that I know the location of and can read from the hypothetical log-grabber function. Actually I don't see that there's any need to involve Postgres itself in this issue at all --- seems like the only agreement needed is between the GUI and the postmaster launching script about where the log file is. What if there's no file access (e.g. only db admin, not sys admin, no file sharing, firewalled with only 5432 port access or similar)? I'd like a solution that needs just enabling in postgresql.conf and a button in pgadmin3 (probably phppgadmin would follow soon), on any platform. Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] serverlog function (log_destination file)
Andreas Pflug [EMAIL PROTECTED] writes: What if there's no file access If you don't have any access to the machine then you are not really a DBA, you only play one on TV. You can't for example start and stop the postmaster remotely. So I don't have a lot of sympathy for the notion that the logs have to be externally accessible, and none whatever for the notion that this has to be possible in every configuration. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] serverlog function (log_destination file)
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: What if there's no file access If you don't have any access to the machine then you are not really a DBA, you only play one on TV. However you may call me, I can think of many cases where I'd like to look at the server log, without wanting to change the configuration or start/stop the postmaster. The error message sent to the application might be lost, or an interference with another app might have caused troubles, so a collected log is fine for such cases, preferrably displayed by the same tool I'm tuning my queries with. What you're telling me is that people should have a telnet session to the linux box and tail /usr/data/pgsql/serverlog, while they're working with comfortable gui tools otherwise. Sound dissatisfying. The rhdb control center seems fine for a sysadmin-dbadmin who's going to install and reconfigure a machine, but I certainly don't want this for SQL centric work. And a Java GUI is a bad idea on win32 either... Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] serverlog function
Andreas wrote: AFAICS, we have some alternatives: - try to grab the currently created files/syslog/eventlog. Seems hard to do, because we'd depend on additional external tools. - redirect stderr to a postgresql.conf known file. Disadvantage: breaks piping. - maintain a sharedMem for the latest messages. Disadvantage: limited space, no access to older entries after postmaster restart. - additional log_destination file. Disadvantage: Yet Another File besides the redirected stderr, but this seems a minor problem. Another alternative would be to add code to the admin tool to get the log via scp or a similar method. IMHO PostgreSQL is doing the right thing here by using the OS logging, and breaking that isn't a good idea when there are other ways to solve the problem. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] serverlog function (log_destination file)
If I were trying to solve Andreas' problem, I'd pipe stderr to some program that stores recent log output in a file that I know the location of and can read from the hypothetical log-grabber function. Actually I don't see that there's any need to involve Postgres itself in this issue at all --- seems like the only agreement needed is between the GUI and the postmaster launching script about where the log file is. Thanks Tom. I wonder if we (the pgAdmin team) finally need to bite the proverbial bullet and write a helper daemon that can allow access to logs as well as config files and pg_ctl etc. as an optional extra component. It would certainly make things a lot easier for the box admin if you did this as extension functions in pg instead of a separate daemon (even if these are not installed in pg by default, but have to be installed manually. Better if they are included by default, of course, from the easy-to-admin perspective). Then it's just one set of firewall rules, one process to check if it's running, etc. Specifically about the logs, I still think there is a lot of value to being able to read the logs remotely even if you can't restart postmaster. Looking at the server logs can help you debug the database client (for example when the app won't give you the full error msg, but it goes in the server log). It won't help you if it's a postmaster problem (since you won't get to it), but there are a lot of other situations where it will. (This is, btw, how MSSQL does it at least - you can view the server log remotely if the server starts. If not, you'll have to go to the log viewer on the server) //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] serverlog function
Andreas Pflug [EMAIL PROTECTED] writes: For adminstrator's convenience, I'd like to see a function that returns the serverlog. What do you mean by returns the serverlog? Are you going to magically recover data that has gone to stderr or the syslogd daemon? If so, how? And why wouldn't you just go and look at the log file, instead? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])