Archiving and the Write Ahead Log

The next lesson is on setting up your database so that we can recover without data loss!

Turn on in postgresql.conf
On Windows XP this can be found at
C:\Program Files\PostgreSQL\8.3\data

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#fsync = on    # turns forced synchronization on or off
#synchronous_commit = on  # immediate fsync at commit
#wal_sync_method = fsync  # the default is the first option
     # supported by the operating system:
     #   open_datasync
     #   fdatasync
     #   fsync
     #   fsync_writethrough
     #   open_sync
#full_page_writes = on   # recover from partial page writes
#wal_buffers = 64kB   # min 32kB
     # (change requires restart)
#wal_writer_delay = 200ms  # 1-10000 milliseconds

#commit_delay = 0   # range 0-100000, in microseconds
#commit_siblings = 5   # range 1-1000

# - Checkpoints -

#checkpoint_segments = 3  # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min  # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s  # 0 is off

# - Archiving -

#archive_mode = off  # allows archiving to be done
    # (change requires restart)
#archive_command = ''  # command to use to archive a logfile segment
#archive_timeout = 0  # force a logfile segment switch after this
    # time; 0 is off

Need to configure the Archive copy command

Making the following changes:

fsync = on
synchronous_commit = on
wal_sync_method = fsync
full_page_writes = on
archive_mode = on
archive_command = 'copy "%p" "J:\\server\\archivedir\\%f"'
archive_timeout = 3600 # switch logs every hour

Loads of information can be found here
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm

Testing it!

--