Re: [GENERAL] pg_xlog is getting bigger

2013-11-25 Thread Andrew Sullivan
On Mon, Nov 25, 2013 at 03:00:56PM +0530, Pankaj Mishra wrote: > We do this to our customer on regular basis when > pg_xlog exceed more then 15 gb Wow. Your poor customers. But I'm glad you phrased it "do this to our customer" rather than "do this _for_ our customer", since your customer certain

Re: [GENERAL] pg_xlog is getting bigger

2013-11-25 Thread Alvaro Herrera
Pankaj Mishra escribió: > Dera Kevin, > > If you run checkpoint in database then you can delete the pg_xlog other > then current timestamp. We do this to our customer on regular basis when > pg_xlog exceed more then 15 gb That is "somewhat" correct (meaning you are less likely to see data corrupt

Re: [GENERAL] pg_xlog is getting bigger

2013-11-25 Thread Pankaj Mishra
Dera Kevin, If you run checkpoint in database then you can delete the pg_xlog other then current timestamp. We do this to our customer on regular basis when pg_xlog exceed more then 15 gb Regards Pankaj On Sat, Nov 23, 2013 at 1:48 AM, Kevin Grittner wrote: > Pankaj wrote: > > > now go to pg_

Re: [GENERAL] pg_xlog is getting bigger

2013-11-22 Thread Kevin Grittner
Pankaj wrote: > now go to pg_xlog folder you can can see a pattern in file names of logs. > Just remove the file other then current time stamp. Or for safety remove > logs other then current date. No!!!  NEVER remove ANY files from the pg_xlog sub-directory!  That will corrupt your database. --

Re: [GENERAL] pg_xlog is getting bigger

2013-11-22 Thread Pankaj
Go to database # psql -U postgres #checkpoint; exit now go to pg_xlog folder you can can see a pattern in file names of logs. Just remove the file other then current time stamp. Or for safety remove logs other then current date. -- View this message in context: http://postgresql.1045698

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Adrian Klaver
On 12/19/2012 04:54 PM, AI Rumman wrote: I modified checkpoint_segment to 100 form 300 and then forced some CHECKPOINT and pg_switch_xlog() and now found that the pg_xlog file got almost 1 gb of space back. Per Toms post this is a start but probably not the answer. Have you looked in the pg_x

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Adrian Klaver
On 12/19/2012 04:55 PM, Tom Lane wrote: Adrian Klaver writes: On 12/19/2012 04:12 PM, Tom Lane wrote: Idle transactions shouldn't have anything to do with pg_xlog bloat. What causes xlog bloat is inability to release old WAL because either (a) we're not able to complete checkpoints, or (b) WAL

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Tom Lane
Adrian Klaver writes: > On 12/19/2012 01:16 PM, Kevin Grittner wrote: >> AI Rumman wrote: >>> checkpoint_segments | 300 > Aah, just noticed the above. I saw that too, but it doesn't seem to explain 20GB worth of pg_xlog. The fine manual mentions somewhere that we can keep up to twice the specifi

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Tom Lane
Adrian Klaver writes: > On 12/19/2012 04:12 PM, Tom Lane wrote: >> Idle transactions shouldn't have anything to do with pg_xlog bloat. >> What causes xlog bloat is inability to release old WAL because either >> (a) we're not able to complete checkpoints, or (b) WAL archiving is >> enabled but malf

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread AI Rumman
On Wed, Dec 19, 2012 at 7:52 PM, Adrian Klaver wrote: > On 12/19/2012 04:12 PM, Tom Lane wrote: > >> Adrian Klaver writes: >> >>> Well the question is how long have those idle transactions been around? >>> >> >> Idle transactions shouldn't have anything to do with pg_xlog bloat. >> What causes xl

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Adrian Klaver
On 12/19/2012 04:12 PM, Tom Lane wrote: Adrian Klaver writes: Well the question is how long have those idle transactions been around? Idle transactions shouldn't have anything to do with pg_xlog bloat. What causes xlog bloat is inability to release old WAL because either (a) we're not able to

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Tom Lane
Adrian Klaver writes: > Well the question is how long have those idle transactions been around? Idle transactions shouldn't have anything to do with pg_xlog bloat. What causes xlog bloat is inability to release old WAL because either (a) we're not able to complete checkpoints, or (b) WAL archivin

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Adrian Klaver
On 12/19/2012 01:16 PM, Kevin Grittner wrote: AI Rumman wrote: Kevin Grittner wrote: AI Rumman wrote: Kevin Grittner wrote: AI Rumman wrote: I am working on a Postgresql 9.0 server. I have no replication and archive mode setup. But I found that the pg_xlog is getting bigger and bigger. Ri

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Adrian Klaver
On 12/19/2012 02:00 PM, AI Rumman wrote: Please reply to the list also. Well the question is how long have those idle transactions been around? For a good blog on the subject see: http://www.depesz.com/2008/08/__28/hunting-idle-in-__transactions/

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Adrian Klaver
On 12/19/2012 01:39 PM, AI Rumman wrote: Well just for grins and to go to the source, look in the postgresql.conf file itself. In particular what is the settings for: wal_keep_segments Also what does querying pg_stat_activity as the postgres user show? Looking for qu

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Adrian Klaver
On 12/19/2012 01:16 PM, Kevin Grittner wrote: AI Rumman wrote: Kevin Grittner wrote: AI Rumman wrote: Kevin Grittner wrote: AI Rumman wrote: I am working on a Postgresql 9.0 server. I have no replication and archive mode setup. But I found that the pg_xlog is getting bigger and bigger. Ri

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread dabicho
> > I don't see anything obvious. Putting this back on the list, where > it should have stayed all along. Maybe someone else has an idea; > I've only seen such behavior when there were archiving problems > which were showing up in the server log. > > -Kevin > > > -- Perhaps it cannot delete old log

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Kevin Grittner
AI Rumman wrote: > Kevin Grittner wrote: >> AI Rumman wrote: >>> Kevin Grittner wrote: AI Rumman wrote: > I am working on a Postgresql 9.0 server. I have no replication and > archive mode setup. But I found that the pg_xlog is getting bigger > and bigger. Right now it is 20

Re: [GENERAL] pg_xlog is getting bigger

2012-12-19 Thread Kevin Grittner
AI Rumman wrote: > I am working on a Postgresql 9.0 server. I have no replication and archive > mode setup. But I found that the pg_xlog is getting bigger and bigger. > Right now it is 20 GB. > > How should I recover these spaces? Do you have archiving turned on? Are you getting errors in the se