From: chris <chrisk@pgsqlrocket.com>
To: pgsql-admin@lists.postgresql.org
Subject: pg_xlog piling
Date: Tue, 30 Jan 2018 12:04:17 -0700
Message-ID: <575c1026-aabd-4854-55f7-1292c57b3da6@pgsqlrocket.com> (raw)
Hi,
I have a standby that is in replication only mode but the pg_xlog
directory is currently at 577GB utilized and is growing daily and I
can't figure out why. This was a problem before when we were on
postgresl 9.2 but changed some postgresql.conf parameters and then
rebuilt it but still running into the same problem.
Thanks in advance,
Chris
Here is some more info about the standby.
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
wal_level = hot_standby # minimal, archive, or hot_standby
# (change requires restart)
#fsync = off # turns forced synchronization on or off
#synchronous_commit = off # synchronization level;
# off, local, remote_write, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (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 = 128 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 30min
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0
- 1.0
checkpoint_warning = 30min
#checkpoint_warning = 30s # 0 disables
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
# Set these on the master and on any standby that will send replication
data.
max_wal_senders = 3 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 256 # in logfile segments, 16MB each; 0 disables
#wal_keep_segments = 500 # in logfile segments, 16MB each; 0 disables
#replication_timeout = 60s # in milliseconds; 0 disables
# - Master Server -
# These settings are ignored on a standby server.
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is
delayed
# - Standby Servers -
# These settings are ignored on a master server.
hot_standby = on # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
postgres=# show checkpoint_segments;
checkpoint_segments
---------------------
128
(1 row)
postgres=# show wal_keep_segments;
wal_keep_segments
-------------------
256
(1 row)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: chrisk@pgsqlrocket.com, pgsql-admin@lists.postgresql.org
Subject: Re: pg_xlog piling
In-Reply-To: <575c1026-aabd-4854-55f7-1292c57b3da6@pgsqlrocket.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox