Richard's Diary

Saturday, December 13, 2008

PostgreSQL Configuration and Backup After reading chapter 18 and most of the manual, I enabled the following configuration in postgresql.conf
  1. checkpoint_segments = 1
  2. archive_mode = on
  3. archive_command = 'cp -i %p /var/log/postgresqlwal/%f </dev/null'
  4. log_min_duration_statement = 250ms
  5. log_checkpoints = on
  6. log_connections = on
  7. log_disconnections = on
  8. log_duration = off
  9. log_hostname = off
  10. log_lock_waits = on
  11. log_autovacuum_min_duration = 250ms
Then I followed file:///usr/share/doc/packages/postgresql/html/continuous-archiving.html#BACKUP-BASE-BACKUP and made a successful backup and saw the corresponding WAL get written to /var/log/postgresqlwal/ along with the backup history file.

Backup command was
tar -cvzf dec1308postgresbackup.tar * --exclude=pg_xlog

Tomorrow I will verify the integrity of the backup to see if the WAL recorded a phrase I added after starting the backup.

After doing the backup I understand this now "The archive command is only invoked on completed WAL segments. Hence, if your server generates only little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage."

I suppose the last WAL file could be copied manually but if it could then there wouldn't be a warning that there could be data loss. ie Chapter 25 does not say that warm standbys will never lose data.

© 2010 Picky Ricky, Inc. originalblog