From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Chris Kim <chrisk@propaas.com>
To: pgsql-admin@postgresql.org
Subject: Re: WAL segement issues on both master and slave server
Date: Fri, 20 Oct 2017 08:42:29 +0200
Message-ID: <1508481749.2624.4.camel@cybertec.at> (raw)
In-Reply-To: <CABdMLSPDhWL6=i0VvnFK7A85Sma4rrdhmRa_yXHmKUjfak2WKA@mail.gmail.com>
References: <CABdMLSPDhWL6=i0VvnFK7A85Sma4rrdhmRa_yXHmKUjfak2WKA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Chris Kim wrote:
> I am running into an issue with the number of files that reside on the
> pg_xlog directory of my compliance database server (This one is the
> master server in our master-slave setup). Sometime earlier this year,
> I modified the location of the PITR directory and that caused an issue
> with WAL segments not being sent to the correct location and crashing
> the DB. I went ahead and fixed that up so that it points to the correct
> location but since then the number of files on the pg_xlog directory
> went up from around 898 to 1025. I didn't have a chance to look in
> to this issue until now so my question is do you know if there is
> an easy way to clean up some of these files in the pg_xlog directory
> safely? I believe that there might be some orphaned files there and
> would like to clean those up.
You should never remove files manually from pg_xlog.
Look at "pg_stat_archiver" to see what's going on with archiving.
Is it behind schedule?
There are several settings that can cause pg_xlog to grow:
- very high wal_keep_segments
- very high checkpoint_segments
You probably have an old version of PostgreSQL if you didn't
touch the configuration in 5 years, but if not, you should also
look if there are active replication slots that keep WAL around.
> Also, on the Standby, the pg_xlog directory appears like it is growing
> on a daily basis. The WAL files are being cleaned up but I don't
> believe at a fast enough rate. This directory is approximately
> over 650GB in size and I would like to revisit if any of the parameters
> will need to be changed in the postgresql.conf file since it's almost
> 5 years since I last touched this.
Look at the "pg_stat_replication" view in the primary to see how
replication is doing.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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: laurenz.albe@cybertec.at, chrisk@propaas.com
Subject: Re: WAL segement issues on both master and slave server
In-Reply-To: <1508481749.2624.4.camel@cybertec.at>
* 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