Re: WAL segement issues on both master and slave server

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