Why is my pg_xlog directory so huge?

From: Niels Kristian Schjødt <nielskristian(at)autouncle(dot)com>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Why is my pg_xlog directory so huge?
Date: 2013-03-18 09:14:52
Message-ID: 02E032A0-7E7A-4CB3-9C76-5739B05660D5@autouncle.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

After installing my new server I just discovered something that doesn't seem right:

sudo du -h /var/lib/postgresql/9.2/main

4.0K /var/lib/postgresql/9.2/main/pg_snapshots
4.0K /var/lib/postgresql/9.2/main/pg_serial
4.0K /var/lib/postgresql/9.2/main/pg_tblspc
29M /var/lib/postgresql/9.2/main/pg_clog
6.8G /var/lib/postgresql/9.2/main/pg_log
104K /var/lib/postgresql/9.2/main/pg_stat_tmp
81G /var/lib/postgresql/9.2/main/base/27132
6.1M /var/lib/postgresql/9.2/main/base/12040
4.0K /var/lib/postgresql/9.2/main/base/pgsql_tmp
6.0M /var/lib/postgresql/9.2/main/base/12035
6.0M /var/lib/postgresql/9.2/main/base/1
81G /var/lib/postgresql/9.2/main/base
80K /var/lib/postgresql/9.2/main/pg_multixact/members
108K /var/lib/postgresql/9.2/main/pg_multixact/offsets
192K /var/lib/postgresql/9.2/main/pg_multixact
12K /var/lib/postgresql/9.2/main/pg_notify
4.0K /var/lib/postgresql/9.2/main/pg_twophase
160K /var/lib/postgresql/9.2/main/pg_subtrans
752K /var/lib/postgresql/9.2/main/pg_xlog/archive_status
202G /var/lib/postgresql/9.2/main/pg_xlog
496K /var/lib/postgresql/9.2/main/global
289G /var/lib/postgresql/9.2/main

As you can see the pg_xlog folder is 202G, which is more than my entire database - this seems wrong to me, however I have no clue why this would happen.

In short, this is my postgresql.conf

data_directory = '/var/lib/postgresql/9.2/main' # use data in another directory
hba_file = '/etc/postgresql/9.2/main/pg_hba.conf' # host-based authentication file
ident_file = '/etc/postgresql/9.2/main/pg_ident.conf' # ident configuration file
external_pid_file = '/var/run/postgresql/9.2-main.pid' # write an extra PID file
listen_addresses = '192.168.0.4, localhost' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 300 # (change requires restart)
unix_socket_directory = '/var/run/postgresql' # (change requires restart)
wal_level = hot_standby # minimal, archive, or hot_standby
synchronous_commit = on # synchronization level; on, off, or local
checkpoint_segments = 100 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 10min # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
archive_mode = on # allows archiving to be done
archive_command = 'rsync -a %p postgres(at)192(dot)168(dot)0(dot)2:/var/lib/postgresql/9.2/wals/%f </dev/null' # command to use to archive a logfile segment
max_wal_senders = 1 # max number of walsender processes
wal_keep_segments = 32 # in logfile segments, 16MB each; 0 disables
hot_standby = on # "on" allows queries during recovery
log_line_prefix = '%t ' # special values:
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
default_statistics_target = 100
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
effective_cache_size = 22GB
work_mem = 160MB
wal_buffers = 4MB
shared_buffers = 4GB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Magnus Hagander 2013-03-18 09:26:17 Re: Why is my pg_xlog directory so huge?
Previous Message Mark Kirkwood 2013-03-16 08:47:34 Re: New server setup