Re: My pg_xlog directory is becoming huge !?

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] <ml-node+s1045698n5920776h4(at)n3(dot)nabble(dot)com> 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 ([hidden email] <x-msg://17/user/SendEmail.jtp?type=node&node=5920776&i=0>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin <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 <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 <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5920762&code=YmVuamFtaW4uY29oZW5AcGxheXJpb24uY29tfDU5MjA3NjJ8LTE0OTE4NTc4Ng==>.
> NAML <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>

--
View this message in context: http://postgresql.nabble.com/My-pg-xlog-directory-is-becoming-huge-tp5920762p5920785.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.