Why is my pg_xlog directory so huge?

Поиск
Список
Период
Сортировка
От Niels Kristian Schjødt
Тема Why is my pg_xlog directory so huge?
Дата
Msg-id 02E032A0-7E7A-4CB3-9C76-5739B05660D5@autouncle.com
обсуждение исходный текст
Ответы Re: Why is my pg_xlog directory so huge?  (Magnus Hagander <magnus@hagander.net>)
Список 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
haveno 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@192.168.0.2:/var/lib/postgresql/9.2/wals/%f </dev/null'  # command to use to
archivea 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Mark Kirkwood
Дата:
Сообщение: Re: New server setup
Следующее
От: Magnus Hagander
Дата:
Сообщение: Re: Why is my pg_xlog directory so huge?