Thread: Zeroed out pages at the end of the database files
Hello,
I'm running PostgreSQL 9.2.4 on Ubuntu 12.04.3 LTS. It's being used by an application that has recently gone from demo (very, very low usage) to production (low, but constant usage). It runs the autovacuum daemon and zero_damaged_pages configuration parameter is off.Drazen Kacar <drazen.kacar@oradian.com> writes: > It turned out that pg_filedump reported errors for 34 database files, but > that was because all those files had one or more database pages (8k) at the > end of the file zeroed out. This can definitely happen after a database crash; it's not a problem since the wasted space will be recovered by VACUUM. I don't recall at the moment if there are any code paths that can cause it without a crash. However, if you're looking directly at the disk files it's definitely possible for there to be zeroes in a page that has been obtained from the OS but never yet flushed out of shared buffers. regards, tom lane
Drazen Kacar <drazen.kacar@oradian.com> writes: > Hello, > > I'm running PostgreSQL 9.2.4 on Ubuntu 12.04.3 LTS. It's being used by an application that has recently gone from demo(very, very low usage) to production (low, but > constant usage). It runs the autovacuum daemon and zero_damaged_pages configuration parameter is off. > > Since Postgres 9.2 doesn't have block checksums I implemented a poor man's consistency checking: on the backup server everybackup is unpacked and pg_filedump <db_file> > | grep Error is run on every database file. I have never seen any error reported by that, until the last Sunday the firstone appeared. Are you running pg_filedump against either or both of a. live DB, b. unpacked hot-backup that hasn't been started and allowed to reach consistent recovery state? If so, then I don't presume you'll have a reliable outcome though if this had been done in the past on a system with very little throughput, it might work by luck. > It turned out that pg_filedump reported errors for 34 database files, but that was because all those files had one or moredatabase pages (8k) at the end of the file > zeroed out. So pg_filedump couldn't find the proper header and thus reported an error. This was also the case on the mainserver, so it wasn't some error present on the > backup server only. Database files stay in this condition for a time (I'm not sure how long) and then the zeroed out pagesdisappear. Postgres doesn't report any kind > of an error. > > I want to ask if this behaviour is normal. > > I know that autovacuum can truncate the db file if it happens that the last page(s) don't contain live data, so this behaviourcould be that, but it seems weird that > the pages would first be zeroed out and then, some time later, the file truncated. Why not truncate it immediately? > > My checking incantation is: > > cd $PGDATA > for f in `find base global -type f -name '[0-9]*' \! -empty`; do > ??? pg_filedump $f | grep -q Error > ??? [[ $? -eq 0 ]] && echo $f > done > > And later, to verify that all errors are zeroed-out pages at the end of the file: > > pg_filedump -d <file> > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800