Postgre Xlog
Note: I am not a DB admin, basic answers please guys. Thanks.
Backround: WISP, 33 Users, Prizm latest Version, Postgre latest version, 2003 Domain Server 20gig drives, 2 gigs of memory 3ghz dual Xeon.
Problem:
Postgre Crashs every 1-2 months, I seem to be filling up the Xlog file that I cannot figure out how to clean or maintain it. (looked everywhere even Postgre website and Forums)
Detail of problem:
Xlog file fills up completely, causing the Service in 2003 server to Stop running the postgre Service.
When I try to Vacuum the DATABASE it causes to Xlog file to fill up again within 1 minute, then kicks off the Postgre Service.
Temp Workaround:
1st Turn off authentication on the ap’s, run full server system Restore of a date that works, Turn on authentication, within 1-2 months of running it fills up again.
Soulations Attempted and Failed:
Rotate the log files (16.56MB now) in the postgresql.conf and I have tried to Create new ones , also tried truncate still no luck.
Questions:
How do I automate the maintenance of the Xlog file?
How do I maintain the Database correctly?
Do I have the correct Config in my post below?
Working to 250 users, what should I change in my config and why?
Here is my Config of the Xlog.
# - Where to Log -
log_destination = ‘stderr’ # Valid values are combinations of stderr,
# syslog and eventlog, depending on
# platform.
# This is relevant when logging to stderr:
redirect_stderr = true # Enable capturing of stderr into log files.
# These are only relevant if redirect_stderr is true:
#log_directory = ‘pg_log’ # Directory where log files are written.
# May be specified absolute or relative to PGDATA
#log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’ # Log file name pattern.
# May include strftime() escapes
#log_truncate_on_rotation = true # If true, any 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 false, meaning append to existing
# files in all cases.
#log_rotation_age = 1440 # Automatic rotation of logfiles will happen after
# so many minutes. 0 to disable.
#log_rotation_size = 10240 # Automatic rotation of logfiles will happen after
# so many kilobytes of log output. 0 to disable.
# These are relevant when logging to syslog:
#syslog_facility = ‘LOCAL0’
#syslog_ident = ‘postgres’