pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMy pg_xlog directory is becoming huge !?
4+ messages / 3 participants
[nested] [flat]
* My pg_xlog directory is becoming huge !?
@ 2016-09-12 12:34 ben.play <benjamin.cohen@playrion.com>
2016-09-12 13:51 ` Re: [MASSMAIL]My pg_xlog directory is becoming huge !? Gilberto Castillo <gilberto.castillo@etecsa.cu>
2016-09-12 14:03 ` Re: My pg_xlog directory is becoming huge !? Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 2 replies; 4+ messages in thread
From: ben.play @ 2016-09-12 12:34 UTC (permalink / raw)
To: pgsql-admin
Hi all,
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 ?
Thank you a lot.
--
View this message in context: http://postgresql.nabble.com/My-pg-xlog-directory-is-becoming-huge-tp5920762.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [MASSMAIL]My pg_xlog directory is becoming huge !?
2016-09-12 12:34 My pg_xlog directory is becoming huge !? ben.play <benjamin.cohen@playrion.com>
@ 2016-09-12 13:51 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
1 sibling, 0 replies; 4+ messages in thread
From: Gilberto Castillo @ 2016-09-12 13:51 UTC (permalink / raw)
To: ben.play <benjamin.cohen@playrion.com>; +Cc: pgsql-admin
> Hi all,
>
> 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 ?
What schema type of replication you use?
--
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: My pg_xlog directory is becoming huge !?
2016-09-12 12:34 My pg_xlog directory is becoming huge !? ben.play <benjamin.cohen@playrion.com>
@ 2016-09-12 14:03 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2016-09-12 14:30 ` Re: My pg_xlog directory is becoming huge !? ben.play <benjamin.cohen@playrion.com>
1 sibling, 1 reply; 4+ messages in thread
From: Albe Laurenz @ 2016-09-12 14:03 UTC (permalink / raw)
To: ben.play *EXTERN* <benjamin.cohen@playrion.com>; 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: My pg_xlog directory is becoming huge !?
2016-09-12 12:34 My pg_xlog directory is becoming huge !? ben.play <benjamin.cohen@playrion.com>
2016-09-12 14:03 ` Re: My pg_xlog directory is becoming huge !? Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2016-09-12 14:30 ` ben.play <benjamin.cohen@playrion.com>
0 siblings, 0 replies; 4+ messages in thread
From: ben.play @ 2016-09-12 14:30 UTC (permalink / raw)
To: pgsql-admin
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@n3.nabble.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&...;.
> NAML <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabbl...;
--
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-09-12 14:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-09-12 12:34 My pg_xlog directory is becoming huge !? ben.play <benjamin.cohen@playrion.com>
2016-09-12 13:51 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2016-09-12 14:03 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2016-09-12 14:30 ` ben.play <benjamin.cohen@playrion.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox