Re: My pg_xlog directory is becoming huge !?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi, 

You are right ! 
We have 4 replications NOT ACTIVE.
We don’t have any other servers at this time; it was only a test.

That is why we drop our 4 replication_slots

Everything on max_wal_size is commented. 
#checkpoint_timeout = 5min              # range 30s-1h
#max_wal_size = 1GB
#min_wal_size = 80MB


Now, the pg_xlog directory is normal !

Thank you so much !


Le 12 sept. 2016 à 16:04, Albe Laurenz *EXTERN* [via PostgreSQL] <[hidden email]> a écrit :

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



--
Sent via pgsql-admin mailing list (<a href="" target="_top" rel="nofollow" link="external" class="">[hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



If you reply to this email, your message will be added to the discussion below:
http://postgresql.nabble.com/My-pg-xlog-directory-is-becoming-huge-tp5920762p5920776.html
To unsubscribe from My pg_xlog directory is becoming huge !?, click here.
NAML



View this message in context: Re: My pg_xlog directory is becoming huge !?
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux