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.foertsch@xxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
