13

I need an alternative for running the following command:

C:\xxx\pgsql\bin\pg_ctl" -D "C:\xxx\pgsql\data" -l "C:\yyy\log\pgsql.log" start.

This way the server starts and logs to C:\yyy\log\pgsql.log. When I try to register the server as a service though - no logging options are available and the server logs to Event Viewer. From pg_ctl documentation:

pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-S a[uto] | d[emand] ] [-w] [-t seconds] [-s] [-o options]

How can I force the server to log into a file? Note: I don't want to use one-click installer, I just want to work with unzipped binaries.

0

3 Answers 3

15

Locate your postgresql.conf in the datadir

Find the section that looks like this

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, syslog and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off                # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'pg_log'               # directory where log files are written,
                                        # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_truncate_on_rotation = off         # If on, an existing log file of the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 7d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

simply uncomment and use these parameters. Then, restart the postgres service,

Since you are running postgresql in Windows, it is possible that you may not be allowed to edit postgresql.conf while the service is up. If that is the case:

  • shutdown the postgres service
  • edit postgresql.conf
  • start the postgres service

Give it a Try !!!

2
  • Thanks for pointing me to the right direction, Rolando, but your answer just isn't right... As you quote the configuration file, you can only have stderr, csvlog, syslog, eventlog there. I had to change the following properties instead: logging_collector = on, log_directory = ..., log_filename=.... If you edit your answer I'll accept it.
    – Vic
    Commented Nov 28, 2012 at 13:03
  • 1
    log_directory ='C:/Users/Peter/Documents/jonbloe/system/logs' worked for me. But don't use the Windows'\' as it works as an escape and the logs end up in a folder PGDATA/UsersPeterDocumentsjonbloesystemlogs
    – peter2108
    Commented Sep 23, 2014 at 12:30
0

Make sure you enabled the log_statement to log_statement = 'all'

0

This is a bug in PG. I have spoted it recently.

fd = fileno(stderr); returns -2 in window service mode

Probably PG developers should use fd = STDERR_FILENO.

I'll report this bug to community after more precise investigation.

The function should be fixed is the following

elog.c:

void
write_pipe_chunks(char *data, int len, int dest)
{
    ...
    int         fd = STDERR_FILENO;//fileno(stderr);
    ...

Accoring to Microsoft documentation https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/fileno?view=msvc-170

Quote: "If stdout or stderr is not associated with an output stream (for example, in a Windows application without a console window), the file descriptor returned is -2. ..."

1
  • Is this intended as an answer to the question? It's not at all clear that it is. Your post has been flagged for possible deletion. To avoid that, please edit your answer to clarify it.
    – Paul White
    Commented May 6 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.