Sunday, March 13, 2011

Setting up PostgreSQL for Windows - (1) Log config

1. Location of PostgreSQL files.

C:\Program Files\PostgreSQL\9.0>dir
 Volume in drive C is HD01-1TB
 Volume Serial Number is 5ED7-0C7F

 Directory of C:\Program Files\PostgreSQL\9.0

03/13/2011  12:32 AM    <DIR>          .
03/13/2011  12:32 AM    <DIR>          ..
03/13/2011  12:31 AM    <DIR>          bin
03/13/2011  11:39 AM    <DIR>          data
03/13/2011  12:31 AM    <DIR>          doc
03/13/2011  12:31 AM    <DIR>          include
03/13/2011  12:31 AM    <DIR>          installer
03/13/2011  12:31 AM    <DIR>          lib
03/13/2011  12:31 AM    <DIR>          pgAdmin III
03/13/2011  12:32 AM               342 pg_env.bat
03/13/2011  12:31 AM    <DIR>          scripts
03/13/2011  12:31 AM    <DIR>          share
03/13/2011  12:31 AM    <DIR>          StackBuilder
03/13/2011  12:32 AM    <DIR>          symbols
03/13/2011  12:32 AM         5,768,777 uninstall-postgresql.exe
               2 File(s)      5,769,119 bytes
              13 Dir(s)  611,877,490,688 bytes free

2. Location of conf files.
C:\Program Files\PostgreSQL\9.0\data>dir *.conf
 Volume in drive C is HD01-1TB
 Volume Serial Number is 5ED7-0C7F

 Directory of C:\Program Files\PostgreSQL\9.0\data

02/27/2011  01:30 AM             3,648 pg_hba.conf
02/27/2011  01:30 AM             1,678 pg_ident.conf
02/27/2011  01:30 AM            18,554 postgresql.conf
               3 File(s)         23,880 bytes
               0 Dir(s)  611,877,838,848 bytes free

3. Changing log configuration.

(1) Location of log files:
By default, it creates log files in the directory:
C:\Program Files\PostgreSQL\9.0\data\pg_log

(2) First, make a backup copy of postgresql.conf file.
C:\Program Files\PostgreSQL\9.0\data>copy postgresql.conf postgresql.conf.bak.original
        1 file(s) copied.

C:\Program Files\PostgreSQL\9.0\data>dir *.conf*
 Volume in drive C is HD01-1TB
 Volume Serial Number is 5ED7-0C7F

 Directory of C:\Program Files\PostgreSQL\9.0\data

02/27/2011  01:30 AM             3,648 pg_hba.conf
02/27/2011  01:30 AM             1,678 pg_ident.conf
02/27/2011  01:30 AM            18,554 postgresql.conf
02/27/2011  01:30 AM            18,554 postgresql.conf.bak.original
               4 File(s)         42,434 bytes
               0 Dir(s)  611,877,171,200 bytes free

(3) Configuration can be changed in the postgresql.conf file.
I made the following changes:

#------------------------------------------------------------------------------
# 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 = on  # 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'  # 2011-03-13 enabled; directory where log files are written,
     # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 2011-03-13 enabled; log file name pattern,
     # can include strftime() escapes
log_truncate_on_rotation = off  # 2011-03-13 enabled, keep it 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 = 1d   # 2011-03-13 enaaled; Automatic rotation of logfiles will
     # happen after that time.  0 disables.
log_rotation_size = 10MB  # 2011-03-13 enabled; Automatic rotation of logfiles will 
     # happen after that much log output.
     # 0 disables.

4. Alternatively, you can view/edit configurations including postgresql.conf from pgAdmin
(1) Open pgAdmin and goto File menu
(2) Select "Open postgresql conf..."
That will open a config editor:
(3) [Update] The conf file can be opened directly from its Tools menu's Server Configuration option.


5. Confirm if logging is working.

(1) Stop and restart the server.
(2) Check the log contents.
postgresql-2011-03-13_140919.log
2011-03-13 14:09:19 EDT LOG:  database system was shut down at 2011-03-13 14:08:50 EDT
2011-03-13 14:09:19 EDT LOG:  database system is ready to accept connections
2011-03-13 14:09:19 EDT LOG:  autovacuum launcher started
2011-03-13 14:14:11 EDT LOG:  received fast shutdown request
2011-03-13 14:14:11 EDT LOG:  aborting any active transactions
2011-03-13 14:14:11 EDT LOG:  autovacuum launcher shutting down
2011-03-13 14:14:11 EDT LOG:  shutting down
2011-03-13 14:14:11 EDT LOG:  database system is shut down
postgresql-2011-03-13_141433.log
2011-03-13 14:14:33 EDT LOG:  database system was shut down at 2011-03-13 14:14:11 EDT
2011-03-13 14:14:33 EDT LOG:  database system is ready to accept connections
2011-03-13 14:14:33 EDT LOG:  autovacuum launcher started

No comments:

Post a Comment