Message-ID: <1508481749.2624.4.camel@cybertec.at>
Subject: Re: WAL segement issues on both master and slave server
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Chris Kim <chrisk@propaas.com>, pgsql-admin@postgresql.org
Date: Fri, 20 Oct 2017 08:42:29 +0200
In-Reply-To: 
 <CABdMLSPDhWL6=i0VvnFK7A85Sma4rrdhmRa_yXHmKUjfak2WKA@mail.gmail.com>
References: 
	<CABdMLSPDhWL6=i0VvnFK7A85Sma4rrdhmRa_yXHmKUjfak2WKA@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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