From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | 'ben(dot)play *EXTERN*' <benjamin(dot)cohen(at)playrion(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: My pg_xlog directory is becoming huge !? |
Date: | 2016-09-12 14:03:16 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B5389DD29@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
ben.play wrote:
> We tried to replicate our database and since this moment, our pg_xlog
> directory is becoming HUGE ! (more than 500 Gb !)
>
> In fact, each minute, there is a new file of 16Mb, called like
> 00000001000001370000004D ...
>
> This is our main parameters in postgresql.conf
>
>
>
> The replication is stopped (it was only a test). And I cannot put
> wal_level=minimal without having an error at startup.
>
> Do you have any idea to reduce the size of the directory ?
First you got to find out what is causing this.
Do you use streaming replication with replication slots?
Then this can happen if a standby is down and has stopped receiving WAL.
Find out with
SELECT slot_name, slot_type, active, xmin, catalog_xmin, restart_lsn FROM pg_replication_slots;
If there is a slot that is not active, that may be your problem.
Compare "restart_lsn" with the current LSN:
SELECT pg_current_xlog_location();
and "xmin" with the current transaction ID:
SELECT txid_current();
If that is your problem, restart the standby and let it catch up.
If that is not possible, delete the replication slot with
SELECT pg_drop_replication_slot('slotname');
Other than that, large "pg_xlog" can be caused by a high value of "checkpoint_segments"
(before version 9.5) or "max_wal_size" (from 9.5 on) on the primary.
Of course, a high value for "wal_keep_segments" on the primary can also be the cause.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | ben.play | 2016-09-12 14:30:30 | Re: My pg_xlog directory is becoming huge !? |
Previous Message | Gilberto Castillo | 2016-09-12 13:51:58 | Re: [MASSMAIL]My pg_xlog directory is becoming huge !? |