Re: [GENERAL] pg_xlog keeps growing

On Monday, February 27, 2017, dhanuj hippie wrote: > So does it work like - it can go upto 16MB*1024 times which is 16GB, and > then this will be cleaned up automatically ? > > On Tue, Feb 28, 2017 at 9:47 AM, dhanuj hippie > wrote: > >> psql (9.3.5) >> wal_keep_segments = 1024 >> >> https://www

Re: [GENERAL] pg_xlog keeps growing

Based on my config, what is the max size this directory is expected to grow ? And how can I check whether this recycle/removal is happening fine ? On Tue, Feb 28, 2017 at 10:08 AM, Michael Paquier wrote: > On Tue, Feb 28, 2017 at 1:20 PM, dhanuj hippie > wrote: > > So does it work like - it can

Re: [GENERAL] pg_xlog keeps growing

On Tue, Feb 28, 2017 at 1:20 PM, dhanuj hippie wrote: > So does it work like - it can go upto 16MB*1024 times which is 16GB, and > then this will be cleaned up automatically ? On a standby each time a restart point is created the oldest segments are either recycled or removed, wal_keep_segments r

Re: [GENERAL] pg_xlog keeps growing

So does it work like - it can go upto 16MB*1024 times which is 16GB, and then this will be cleaned up automatically ? On Tue, Feb 28, 2017 at 9:47 AM, dhanuj hippie wrote: > psql (9.3.5) > wal_keep_segments = 1024 > > On Tue, Feb 28, 2017 at 9:16 AM, Rob Sargent > wrote: > >> >> > On Feb 27, 20

Re: [GENERAL] pg_xlog keeps growing

psql (9.3.5) wal_keep_segments = 1024 On Tue, Feb 28, 2017 at 9:16 AM, Rob Sargent wrote: > > > On Feb 27, 2017, at 8:33 PM, dhanuj hippie > wrote: > > > > Hi, > > > > I have a postgres cluster running in hot_standby. I see the pg_xlog is > growing over time (may files of size 16 MB each). The

Re: [GENERAL] pg_xlog keeps growing

> On Feb 27, 2017, at 8:33 PM, dhanuj hippie wrote: > > Hi, > > I have a postgres cluster running in hot_standby. I see the pg_xlog is > growing over time (may files of size 16 MB each). The replication lag is very > less ~2kB, and never goes into a bad state. > I'm manually resetting this on

[GENERAL] pg_xlog keeps growing

Hi, I have a postgres cluster running in hot_standby. I see the pg_xlog is growing over time (may files of size 16 MB each). The replication lag is very less ~2kB, and never goes into a bad state. I'm manually resetting this once a while using pg_resetxlog command. Is there a way to understand why

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 29/06/2015 11:38, Stéphane Schildknecht wrote: On 16/06/2015 10:55, Xavier 12 wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purge

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 16/06/2015 10:55, Xavier 12 wrote: > Hi everyone, > > Questions about pg_xlogs again... > I have two Postgresql 9.1 servers in a master/slave stream replication > (hot_standby). > > Psql01 (master) is backuped with Barman and pg_xlogs is correctly > purged (archive_command is used). > > Hower

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 19/06/2015 03:31, Sameer Kumar wrote: On Thu, 18 Jun 2015 15:17 Xavier 12 > wrote: On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 mailto:mania...@gmail.com>> wrote: On 17/06/2015 03:17, Sameer Kumar wrot

Re: [GENERAL] pg_xlog on a hot_stanby slave

On Thu, 18 Jun 2015 15:17 Xavier 12 wrote: On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 > wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 mailto:mania...@gmail.com>> wrote: Hi everyone, Questions abo

Re: [GENERAL] pg_xlog on a hot_stanby slave

On Wed, 17 Jun 2015 15:24 Xavier 12 wrote: On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped w

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 17/06/2015 03:17, Sameer Kumar wrote: On Tue, 16 Jun 2015 16:55 Xavier 12 > wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is back

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 17/06/2015 02:44, Venkata Balaji N wrote: On Tue, Jun 16, 2015 at 6:55 PM, Xavier 12 > wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master)

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 16/06/2015 22:28, Guillaume Lelarge wrote: 2015-06-16 15:55 GMT+02:00 Xavier 12 >: I don't think so. There is no archive_command and the master doesn't ship its wal here. But how can I check that ? What's the complete path to the directory on the sal

Re: [GENERAL] pg_xlog on a hot_stanby slave

On Tue, 16 Jun 2015 16:55 Xavier 12 wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (sl

Re: [GENERAL] pg_xlog on a hot_stanby slave

On Tue, Jun 16, 2015 at 6:55 PM, Xavier 12 wrote: > Hi everyone, > > Questions about pg_xlogs again... > I have two Postgresql 9.1 servers in a master/slave stream replication > (hot_standby). > > Psql01 (master) is backuped with Barman and pg_xlogs is correctly > purged (archive_command is used)

Re: [GENERAL] pg_xlog on a hot_stanby slave

2015-06-16 15:55 GMT+02:00 Xavier 12 : > I don't think so. There is no archive_command and the master doesn't > ship its wal here. > But how can I check that ? > > What's the complete path to the directory on the salve that contains 951 files? what does PostgreSQL say on its log files? 2015-06-16

Re: [GENERAL] pg_xlog on a hot_stanby slave

I don't think so. There is no archive_command and the master doesn't ship its wal here. But how can I check that ? 2015-06-16 12:41 GMT+02:00 Guillaume Lelarge : > Le 16 juin 2015 10:57 AM, "Xavier 12" a écrit : >> >> Hi everyone, >> >> Questions about pg_xlogs again... >> I have two Postgresql 9

Re: [GENERAL] pg_xlog on a hot_stanby slave

Le 16 juin 2015 10:57 AM, "Xavier 12" a écrit : > > Hi everyone, > > Questions about pg_xlogs again... > I have two Postgresql 9.1 servers in a master/slave stream replication > (hot_standby). > > Psql01 (master) is backuped with Barman and pg_xlogs is correctly > purged (archive_command is used).

[GENERAL] pg_xlog on a hot_stanby slave

Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purged (archive_command is used). Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7

Re: [GENERAL] pg_xlog Concern

On Wed, May 20, 2015 at 4:12 PM, Sachin Srivastava wrote: > Dear Venkata, > > I have not set this parameter "archive_timeout" > > I think that is also the reason. > > What value I'll assign to this parameter if my *Checkpoint_segments= 200 *and > *checkpoint_timeout= 5min, *kindly confirm. > > Be

Re: [GENERAL] pg_xlog Concern

(I'm not going to try to fix the top-posting) I've seen what I think you're describing on several occasions. What we basically discovered is very simple: disks have a top speed and you can't exceed it. If I understand you correctly, you see the # of wal segments grow very large during and shortl

Re: [GENERAL] pg_xlog Concern

-- > "MY each WAL file is 16 MB." --- Are you saying that you want WAL segments to be smaller? WAL file segments are always the same size, which can be set with --with-WAL-segsize at build time but not at runtime. Regards Mathew Sent from my iPhone > On May 20,

Re: [GENERAL] pg_xlog Concern

Hello, >First: When I have given checkpoint segments=200 (As per my knowledge WAL file should be generated >200*2+3=403 only) but why it’s generating too much file. MY each WAL file is 16 MB. The formula is (2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal

Re: [GENERAL] pg_xlog Concern

On Mon, May 18, 2015 at 9:44 PM, Sachin Srivastava wrote: > Hi, > > > > I have done below changes in my postgresql.conf. > > > > *Checkpoint_segments= 200* > > and > > *checkpoint_timeout= 5min* > > > > I am also doing archiving and below changes in my postgresql.conf. > > > > *Wal_level=archive*

Re: [GENERAL] pg_xlog Concern

Hi, Any kind of slowness on your archive directory may cause the archiving process fall behind thus accumulating segment files in your cluster's pg_xlog directory. I assume that you are on PostgreSQL 9.4. Could you please check your archiver status by typing "select * from pg_catalog.pg_stat_arch

Re: [GENERAL] pg_xlog Concern

On 18/05/15 13:44, Sachin Srivastava wrote: > But currently my pg_xlog size is 60 GB and there are 3740 WAL file in > this folder and in Last week this was 400 GB(pg_xlog folder) and WAL > file were approx. 3. Due to archiving pg_xlog folder size is > decreasing now but it’s taking one week to

[GENERAL] pg_xlog Concern

Hi, I have done below changes in my postgresql.conf. *Checkpoint_segments= 200* and *checkpoint_timeout= 5min* I am also doing archiving and below changes in my postgresql.conf. *Wal_level=archive* *archive_command= cp %p /dbkup/momacpp_213_live/%f* *archive_mode=on* Achievin

Re: [GENERAL] pg_xlog is getting bigger

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

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

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

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

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 size growing untill it fills the partition

On Monday 07 October 2013 15:23:33 Marcin Mańk wrote: > On Thu, Oct 3, 2013 at 11:56 PM, Michal TOMA wrote: > > Now I have: > > checkpoint_completion_target = 0.9 > > wal_buffers = 8MB > > checkpoint_segments = 16 > > checkpoint_timeout = 20min > > shared_bu

Re: [GENERAL] pg_xlog size growing untill it fills the partition

On Monday 07 October 2013 21:33:14 Jeff Janes wrote: > On Mon, Oct 7, 2013 at 11:44 AM, Michal TOMA wrote: > > I gave it in my first post. It is a software raid 1 of average 7200 rpm > > disks > > (Hitachi HDS723020BLE640) for the main tablespace and a software raid 1 > > of SSDs for onother table

Re: [GENERAL] pg_xlog size growing untill it fills the partition

On Mon, Oct 7, 2013 at 11:44 AM, Michal TOMA wrote: > I gave it in my first post. It is a software raid 1 of average 7200 rpm > disks > (Hitachi HDS723020BLE640) for the main tablespace and a software raid 1 of > SSDs for onother tablespace and alos the partition holding the pg_xlog > directory.

Re: [GENERAL] pg_xlog size growing untill it fills the partition

I gave it in my first post. It is a software raid 1 of average 7200 rpm disks (Hitachi HDS723020BLE640) for the main tablespace and a software raid 1 of SSDs for onother tablespace and alos the partition holding the pg_xlog directory. The problem is not the workload as the application is a web c

Re: [GENERAL] pg_xlog size growing untill it fills the partition

On Mon, Oct 7, 2013 at 6:23 AM, Marcin Mańk wrote: > On Thu, Oct 3, 2013 at 11:56 PM, Michal TOMA wrote: > >> >> This is what I can see in the log: >> 2013-10-03 13:58:56 CEST LOG: checkpoint starting: xlog >> 2013-10-03 13:59:56 CEST LOG: checkpoint complete: wrote 448 buffers >> (0.2%);

Re: [GENERAL] pg_xlog size growing untill it fills the partition

The database is about 1TB large. Thera are mainly 4 tables 2 with row sizes about 1000 bytes and 2 with rows with about 200 bytes but contaniong also several usually large (the size of an average webpage) text fields. My initial setting was 16GB of shared buffers I tried with 2GB and did not not

Re: [GENERAL] pg_xlog size growing untill it fills the partition

On Thu, Oct 3, 2013 at 11:56 PM, Michal TOMA wrote: > > Now I have: > checkpoint_completion_target = 0.9 > wal_buffers = 8MB > checkpoint_segments = 16 > checkpoint_timeout = 20min > shared_buffers = 2GB > log_checkpoints = on > > This is what I can

Re: [GENERAL] pg_xlog size growing untill it fills the partition

Hello Alban, Yes I think I'm saturating the HDD IO but the problem is that the only thing that runs on that server is postgres itself. I have two tablespeces one on a SSD software raid 1 another one on a HDD software raid 1. The disk which is saturating is the HDD and the pg_xlog directory is on

Re: [GENERAL] pg_xlog size growing untill it fills the partition

On Oct 3, 2013, at 23:56, Michal TOMA wrote: > I have a problem on my pg 9.2.4 setup (OpenSuse 12.2, kernel 3.2.13). > My pg_xlog directory is growing uncontrolably untill it fills the partition. > The database is under heavy write load and is spread on two tablesapces one > on a ssd software r

Re: [GENERAL] pg_xlog size growing untill it fills the partition

I have it set to 0 already. On Friday 04 October 2013 00:16:28 Ken Brush wrote: > Try setting the following in your postgresql.conf: > > wal_keep_segments = 0 > > On Thu, Oct 3, 2013 at 2:56 PM, Michal TOMA wrote: > > Hello, > > > > I have a problem on my pg 9.2.4 setup (OpenSuse 12.2, kernel 3.2

Re: [GENERAL] pg_xlog size growing untill it fills the partition

Try setting the following in your postgresql.conf: wal_keep_segments = 0 On Thu, Oct 3, 2013 at 2:56 PM, Michal TOMA wrote: > Hello, > > I have a problem on my pg 9.2.4 setup (OpenSuse 12.2, kernel 3.2.13). > My pg_xlog directory is growing uncontrolably untill it fills the > partition. The da

[GENERAL] pg_xlog size growing untill it fills the partition

Hello, I have a problem on my pg 9.2.4 setup (OpenSuse 12.2, kernel 3.2.13). My pg_xlog directory is growing uncontrolably untill it fills the partition. The database is under heavy write load and is spread on two tablesapces one on a ssd software raid1 partition and a second one on a hdd softwa

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

I had not run these commands on the master as I was only doing sql updates ~1mil of them

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

Cliff de Carteret wrote: > I have now deleted the copy on the remote wal_archive folder and the > archiving is now functioning and sending the logs from the local to the > remote folder. The remote database does not startup and the following is in > the log: > > LOG: database system was shut down

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

On 22 January 2013 16:43, Kevin Grittner wrote: > [Please keep the list copied, and put your reply in-line instead > of at the top.] > > Cliff de Carteret wrote: > > On 22 January 2013 16:07, Kevin Grittner wrote: > > > >> Cliff de Carteret wrote: > >> > >>> The current setup has been working su

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

[Please keep the list copied, and put your reply in-line instead of at the top.] Cliff de Carteret wrote: > On 22 January 2013 16:07, Kevin Grittner wrote: > >> Cliff de Carteret wrote: >> >>> The current setup has been working successfully for several years >>> until the recent database crash >

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

The local wal_archive directory is a directory named "wal_archive" which is at the same file system level as the data directory. The time stamp states that it has not been used for several years so it looks to be redundant. To get the database running again I moved an old postgres installation to

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

On 01/22/2013 07:57 AM, Cliff de Carteret wrote: There are no files in the pg_xlog directory from before the database crash except one file which ends in .backup, there are 759 files. In the pg_xlog/archive_status folder there are 755 files. The local ../wal_archive folder has no files in it at a

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

Cliff de Carteret wrote: > The current setup has been working successfully for several years > until the recent database crash What file does the server log say it is trying to archive? What error are you getting? Does that filename already exist on the archive (or some intermediate location used

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

There are no files in the pg_xlog directory from before the database crash except one file which ends in .backup, there are 759 files. In the pg_xlog/archive_status folder there are 755 files. The local ../wal_archive folder has no files in it at all whereas the remote wal_archive folder has 147 fi

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

On 01/22/2013 06:59 AM, Cliff de Carteret wrote: Hello, thanks for your response. The xlogs that are filling up are in the original data directory and the wal_keep_segments is commented out on my master and slave postgresql.conf It is expected that there may a certain increase in WAL files. I

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

Hello, thanks for your response. The xlogs that are filling up are in the original data directory and the wal_keep_segments is commented out on my master and slave postgresql.conf Cliff On 22 January 2013 14:48, Adrian Klaver wrote: > On 01/22/2013 03:12 AM, Cliff de Carteret wrote: > >> My d

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

On 01/22/2013 03:12 AM, Cliff de Carteret wrote: My database crashed a couple of days ago during an upgrade several seconds after committing a large transaction to the database. Eventually we found out that this was due to the disk being full as the transaction had created several gigs of data. A

[GENERAL] Pg_xlog increase due to postgres crash (disk full)

My database crashed a couple of days ago during an upgrade several seconds after committing a large transaction to the database. Eventually we found out that this was due to the disk being full as the transaction had created several gigs of data. A day or so later the disk is full again and Postgre

Re: [GENERAL] pg_xlog is getting bigger

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

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

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

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

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

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

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

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

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

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

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

> > 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

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

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

[GENERAL] pg_xlog is getting bigger

Hi, 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? Please let me know. Thanks.

[GENERAL] pg_xlog growth on slave with streaming replication

Hey Everyone, I've got a bit of an interesting issue going on with pg_xlog growing on a streaming replication slave. We're running postgres 9.1.1 x64 built from source on Centos 5.8 x64. On both the master and the slave we have wal_keep_segments configured for 1000 wal_keep_segments = 1000 T

Re: [GENERAL] pg_xlog content

Albe Laurenz wrote: Siddharth Shah wrote: I am not using wall based replication ? I no not have frequent & long transaction , need of checkpoints Just want to get what data resides in pg_xlog that it takes 16MB without any transactions. It contains the transaction log files. The

Re: [GENERAL] pg_xlog content

Siddharth Shah wrote: > I am not using wall based replication ? I no not have frequent & > long transaction , need of checkpoints > Just want to get what data resides in pg_xlog that it takes 16MB without > any transactions. It contains the transaction log files. The files are precreated wi

Re: [GENERAL] pg_xlog content

Siddharth Shah wrote: >I am not using wall based replication ? I no not have frequent & long > transaction , need of checkpoints > Just want to get what data resides in pg_xlog that it takes 16MB without > any transactions. The files are created 16MB long, it is faster to overwrite that than t

Re: [GENERAL] pg_xlog content

Richard Huxton wrote: Siddharth Shah wrote: Hi, When I initialize database, pg_xlog direcory takes almost 17 MB size Why pg_xlog/WAL takes 17 MB of size while there is no transaction occurred ? What is the content of pg_xlog , If it's only wal file it's must be blank after on write

Re: [GENERAL] pg_xlog content

Siddharth Shah wrote: > Hi, > >When I initialize database, pg_xlog direcory takes almost 17 MB size >Why pg_xlog/WAL takes 17 MB of size while there is no transaction > occurred ? > >What is the content of pg_xlog , If it's only wal file it's must be > blank after on write of transact

[GENERAL] pg_xlog content

Hi, When I initialize database, pg_xlog direcory takes almost 17 MB size Why pg_xlog/WAL takes 17 MB of size while there is no transaction occurred ? What is the content of pg_xlog , If it's only wal file it's must be blank after on write of transaction on database. regards, Siddha

Re: [GENERAL] pg_xlog and standby - SOLVED

On Jan 24, 2008 4:30 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Thu, 24 Jan 2008, Erik Jones wrote: > > > To disable archiving, set archive_command="", commenting it out won't > > change it...However, commenting a config is not necessarily equivalent > > to disabling something. > > This is actu

Re: [GENERAL] pg_xlog and standby - SOLVED

On Thu, 24 Jan 2008, Erik Jones wrote: To disable archiving, set archive_command="", commenting it out won't change it...However, commenting a config is not necessarily equivalent to disabling something. This is actually a problem that is corrected in the upcoming 8.3 release. From those re

Re: [GENERAL] pg_xlog and standby - SOLVED

On Jan 24, 2008, at 6:09 AM, Roberto Scattini wrote: On Jan 23, 2008 11:07 PM, Greg Smith <[EMAIL PROTECTED]> wrote: On Wed, 23 Jan 2008, Roberto Scattini wrote: the problem that im having is that i have A LOT of archive files on pg_xlog dir, and thats because the archive_command keeps faili

Re: [GENERAL] pg_xlog and standby - SOLVED

On Jan 23, 2008 11:07 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Wed, 23 Jan 2008, Roberto Scattini wrote: > > > the problem that im having is that i have A LOT of > > archive files on pg_xlog dir, and thats because the archive_command > > keeps failing (the standby server had filled his disk w

Re: [GENERAL] pg_xlog and standby

On Wed, 23 Jan 2008, Roberto Scattini wrote: the problem that im having is that i have A LOT of archive files on pg_xlog dir, and thats because the archive_command keeps failing (the standby server had filled his disk with archives received but not proccesed), so now, i dont know how i can remov

Re: [GENERAL] pg_xlog and standby

On Wed, 2008-01-23 at 18:18 -0200, Roberto Scattini wrote: > the standby server had filled his disk with archives > received but not proccesed Sounds like your standby has fallen badly behind. You should always monitor the lag between primary and standby. You will need to take steps to ensure th

Re: [GENERAL] pg_xlog and standby

On Jan 23, 2008 2:28 PM, Erik Jones <[EMAIL PROTECTED]> wrote: > > You don't. The main server should not be keeping archived WAL files > directly in pg_xlog/. As it queues WAL files to be archived it puts > them in pg_xlog/archive_status/ with file names suffixed with .ready, > once they are arch

Re: [GENERAL] pg_xlog and standby

On Jan 23, 2008, at 2:18 PM, Roberto Scattini wrote: On Jan 23, 2008 2:28 PM, Erik Jones <[EMAIL PROTECTED]> wrote: You don't. The main server should not be keeping archived WAL files directly in pg_xlog/. As it queues WAL files to be archived it puts them in pg_xlog/archive_status/ with fi

Re: [GENERAL] pg_xlog and standby

On Jan 23, 2008, at 9:28 AM, Roberto Scattini wrote: hello everybody: im trying to reconfigure a warm-standby server. the problem is that for some reason, one day the standby server stopped recovering the archives. this leaded to a full disk on that server, so i turned off (commented) the arch

[GENERAL] pg_xlog and standby

hello everybody: im trying to reconfigure a warm-standby server. the problem is that for some reason, one day the standby server stopped recovering the archives. this leaded to a full disk on that server, so i turned off (commented) the archive_command on the main server. i want to restart the pr

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?

On Thu, 14 Jun 2007, Frank Wittig wrote: But I also agree that if there is a possibility that times differ (and there of course is) the question is not if it can happen but when. Sure, but it doesn't matter one bit if the times between the primary and secondary servers differ. If the timesta

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?

Johannes Konert schrieb: > and time goes by, because everything seem to work just fine. One should never rely on something seeming to be any kind of anything. There are ways to _know_ things are right. Like using nagios to check every vital sign state of your systems. That should include check

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialynamed?

On Wed, 2007-06-13 at 21:39 +0200, Frank Wittig wrote: > But I would say one can > rely on serial file names to increase steadily. The whole of the PostgreSQL recovery system relies upon that, so yes we can use that externally from the database also. There's a patch to 8.3 for the restore_comman

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?

Frank Wittig wrote: 24 Hex digits means 24^16 unique file names. Assuming your server saves a WAL file each second (you should review your config it it does) it takes (24^16)/(60*60*24*365)=3.84214066×10^14 years to reach the upper bound. (..) It has to be 16^24. But pg does forge filename

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?

Greg Smith wrote: On Wed, 13 Jun 2007, Johannes Konert wrote: If someone corrects the servers computer-time/date to a date before current time (e.g. set the clock two hours back), then the newer WAL files will have an older timestamp and will be deleted by accident. This should never happen;

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?

Frank Wittig schrieb: > 24 Hex digits means 24^16 unique file names. Assuming your server saves > a WAL file each second (you should review your config it it does) it > takes (24^16)/(60*60*24*365)=3.84214066×10^14 years to reach the upper > bound. How embarrassing - I messed up the calculation.

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?

Hello Johannes, Johannes Konert schrieb: > Thus the newest WAL xlog-file is on top and I can delete all not needed > files at the bottom of the list. You're using pg_controldata to figure out which file's serial is older than the latest redo checkpoint. In case of restart of the slave server PgSQ

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?

On Wed, 13 Jun 2007, Johannes Konert wrote: If someone corrects the servers computer-time/date to a date before current time (e.g. set the clock two hours back), then the newer WAL files will have an older timestamp and will be deleted by accident. This should never happen; no one should ever

Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?

Johannes Konert wrote: But during the day I came out with an solution: I store the WAL-files with the time-stamp of archiving in their file-name. Thus I can order and delete them safely. Your hint was the one, that helped me to find that solution - so thanks for that, Greg.and the others.

  1   2   >