Friday, August 27, 2010

PostgreSQL Server Configuration

PostgreSQL Server configuration files are in /etc/postgresql/8.4/main/ directory.

$ locate postgresql
...
/etc/postgresql/8.4/main
/etc/postgresql/8.4/main/environment
/etc/postgresql/8.4/main/pg_ctl.conf
/etc/postgresql/8.4/main/pg_hba.conf
/etc/postgresql/8.4/main/pg_ident.conf
/etc/postgresql/8.4/main/postgresql.conf
/etc/postgresql/8.4/main/start.conf
...

The log file is at:
...
/var/log/postgresql
/var/log/postgresql/postgresql-8.4-main.log
...

I will make a change in conf file for log output.

1. Change to the configuration file directory:
$ cd /etc/postgresql/8.4/main/
$ ls -la
drwxr-xr-x 2 postgres postgres  4096 2010-08-24 07:59 .
drwxr-xr-x 3 postgres postgres  4096 2010-08-24 07:59 ..
-rw-r--r-- 1 postgres postgres   316 2010-08-24 07:59 environment
-rw-r--r-- 1 postgres postgres   143 2010-08-24 07:59 pg_ctl.conf
-rw-r----- 1 postgres postgres  3822 2010-08-24 07:59 pg_hba.conf
-rw-r----- 1 postgres postgres  1631 2010-08-24 07:59 pg_ident.conf
-rw-r--r-- 1 postgres postgres 16974 2010-08-24 07:59 postgresql.conf
-rw-r--r-- 1 postgres postgres   378 2010-08-24 07:59 start.conf

2. Make a backup copy of the original conf file.
Since postgresql.conf file is owned by postgres, I will make a copy with the same owner.
$ sudo -u postgres cp postgresql.conf postgresql.conf.bak.original
$ ls -la
drwxr-xr-x 2 postgres postgres  4096 2010-08-27 22:24 .
drwxr-xr-x 3 postgres postgres  4096 2010-08-24 07:59 ..
-rw-r--r-- 1 postgres postgres   316 2010-08-24 07:59 environment
-rw-r--r-- 1 postgres postgres   143 2010-08-24 07:59 pg_ctl.conf
-rw-r----- 1 postgres postgres  3822 2010-08-24 07:59 pg_hba.conf
-rw-r----- 1 postgres postgres  1631 2010-08-24 07:59 pg_ident.conf
-rw-r--r-- 1 postgres postgres 16974 2010-08-24 07:59 postgresql.conf
-rw-r--r-- 1 postgres postgres 16974 2010-08-27 22:24 postgresql.conf.bak.original
-rw-r--r-- 1 postgres postgres   378 2010-08-24 07:59 start.conf

3. Make a change.
The following changes are made base on suggestion by Suzuki Koichi at
http://lets.postgresql.jp/documents/tutorial/ubuntu/3

#-------------------------------------------------
# ERROR REPORTING AND LOGGING
#-------------------------------------------------
log_destination = 'stderr'       # 2010.08.27 - enabled
logging_collector = on           # 2010.08.27 - enabled and turned on
log_directory = 'pg_log'         # 2010.08.27 - enabled (I will create)
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 2010.08.27 - enabled
log_truncate_on_rotation = off   # 2010.08.27 - enabled, keep it off
log_rotation_age = 1d            # 2010.08.27 - enabled
log_rotation_size = 10MB         # 2010.08.27 - enabled

4. Now create a new directory (pg_log) for log outputs.
$ su postgres 
$ cd ~/8.4/main/
$ mkdir pg_log
$ ls -F
PG_VERSION  global/  pg_log/  ....

5. Finally, restart the server.
$ sudo /etc/init.d/postgresql-8.4 restart
 * Restarting PostgreSQL 8.4 database server  

6. Confrim the log output.
$ sudo ls /var/lib/postgresql/8.4/main/pg_log
postgresql-2010-08-27_230554.log

No comments:

Post a Comment