Re: pg_xlog Concern

Hi,

Any kind of slowness on your archive directory may cause the archiving
process fall behind thus accumulating segment files in your cluster's
pg_xlog directory.

I assume that you are on PostgreSQL 9.4. Could you please check your
archiver status by typing "select * from pg_catalog.pg_stat_archiver;" in
psql ? If the last_archived_wal column's value is not so close to your
current xlog location, then it probably means a slow write speed on your
archive path compared to your pg_xlog path.

You can check your current xlog file by typing "select
pg_xlogfile_name(pg_current_xlog_location());" in psql.

If you are not on PostgreSQL 9.4, you can check your archiver status by
typing "ps -ef | grep archiver" in your shell.

I've also assumed that you are not using replication slots.

As far as I know, long running transactions (just sitting in idle) won't
affect pg_xlog directory size. Correct me if I'm wrong.

Hope that helps.

Have a nice day.

Koray

On Mon, May 18, 2015 at 5:00 PM, Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net>
wrote:

> On 18/05/15 13:44, Sachin Srivastava wrote:
> > But currently my pg_xlog size is 60 GB and there are 3740 WAL file in
> > this folder and in Last week this was 400 GB(pg_xlog folder) and WAL
> > file were approx. 30000. Due to archiving pg_xlog folder size is
> > decreasing now but it’s taking one week to come in normal size.
>
> Any chance you have unfinished transactions running for a week?
>
> pg_stat_activity should be able to tell you that.
>
> Torsten
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>