Skip site navigation (1) Skip section navigation (2)

WAL files containing old and unmodified data?

From: Brian Fehrle <brianf(at)consistentstate(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: WAL files containing old and unmodified data?
Date: 2012-02-22 20:35:11
Message-ID: 4F45517F.4050508@consistentstate.com (view raw or flat)
Thread:
Lists: pgsql-admin
Hi all,

I have a very active database that generates between 200 and 400 WAL 
files that are archived for backups. Yesterday, the average number of 
WAL files being generated skyrocketed.

We took a look at some WAL files using the strings command, and notice 
quite a bit of data from a specific table with timestamps of last year. 
This table does some basic tracking, where a row is inserted, then it's 
updated, and after that it's never modified again. So we're having data 
appear in our WAL files from rows that haven't (or should not have been) 
modified at all in over a year.

We figured that the rows were in fact being updated, so we turned 
log_min_duration_statement to 0 for a period of time and monitored all 
queries going through, however we never found a single query that could 
possibly be updating these pieces of data.

Is there any other reason why a WAL file could contain data that (we 
believe) hasn't changed? Or is the fact that it's in the WAL file a 
guarantee that the data is either changed or new? Can server processes 
such as autovacuum processes cause this behavior? Anywhere else I should 
try to look?

Thanks in advance,
- Brian F

Responses

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2012-02-22 20:53:23
Subject: Re: WAL files containing old and unmodified data?
Previous:From: Kevin GrittnerDate: 2012-02-22 15:31:28
Subject: Re: constraints primary key and foreign key lost.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group