Thread: pg_class and relfilenode
Hi, I am currently having the corrupted tables issues described in the following link (possibly caused by power failure, which happens pretty often) http://archives.postgresql.org/pgsql-admin/2003-04/msg00012.php. Since we have our systems being deployed to numerous remote systems (psql 7.2.3), upgrading the entire database (with data migration) will be the least preferable solution. I am thinking of creating a script that periodically check the relfilenode inside the pg_class and if there is any mismatch between what pg_class described and what actually exists in the file system, the script will touch that file and try to drop the table. However, after I wrote the script and started testing it, I found that there are already some files (mainly pg_*) tables/indexes are not being consistent with the filesystem. e.g. pg_shadow_usename_index pg_stat_activity pg_stat_database And my script look something like: select oid, * from pg_database where datname = <db> select oid, relname from pg_class and touch <base>/DB#/FILE# for everything found inside pg_class if it doesn't exist on the file system. Is it going to harm the database if I blindly touch those files? Or is there any workaround/trick/patch I can apply to version 7.2.3 without upgrading the whole database? Something like a patch/trick which can force drop a table will be my main goal. Thanks, --muteki _________________________________________________________________ Create your own personal Web page with the info you use most, at My MSN. http://click.atdmt.com/AVE/go/onm00200364ave/direct/01/
On Tue, Feb 10, 2004 at 08:29:50PM -0800, muteki muteki wrote: > Hi, > > I am currently having the corrupted tables issues > described in the following link (possibly caused by > power failure, which happens pretty often) > http://archives.postgresql.org/pgsql-admin/2003-04/msg00012.php. > Since we have our systems being deployed to numerous > remote systems (psql 7.2.3), upgrading the entire database > (with data migration) will be the least preferable > solution. I am thinking of creating a script that You don't need to do any data migration to get off 7.2.3 -- 7.2.4 is a drop-in replacement, so you should at least do that upgrade first. But I don't think that power failures would be enough to cause the kind of problem you're describing, unless you're running without fsync or something. Care to give more details? In any case, I think that your script is mighty dangerous. It sounds like a recipe for data loss to me. Postgres is considerably more robust than this, and I think you're trying to cover up some serious problems that you may have, likely with your hardware. A -- Andrew Sullivan
"muteki muteki" <muteki_f@hotmail.com> writes:
> Since we have our systems being deployed to numerous
> remote systems (psql 7.2.3), upgrading the entire database
> (with data migration) will be the least preferable
> solution.
At the very least you should be running 7.2.4. We do not make
dot-releases for entertainment value, we make them because there
are critical bug fixes. In particular, this 7.2.4 fix:
* Ensure pg_clog updates are sync'd to disk before marking checkpoint complete
might well have something to do with your problems.
> I found that there are already some files
> (mainly pg_*) tables/indexes are not being consistent
> with the filesystem.
> e.g.
> pg_shadow_usename_index
> pg_stat_activity
> pg_stat_database
Views don't have underlying files at all. Shared tables live in a
different directory. You had better study the system catalogs more
carefully before you start writing scripts to hack them.
regards, tom lane
>You don't need to do any data migration to get off 7.2.3 -- 7.2.4 is >a drop-in replacement, so you should at least do that upgrade first. Thanks for the information. If I can simply do a drop-in replacement, that may be something I can try. But I am still thinking there could be other cases database will get corrupted due to power failure and will not be able to startup correctly again. (missing some pg_files) >But I don't think that power failures would be enough to cause the >kind of problem you're describing, unless you're running without >fsync or something. Care to give more details? >In any case, I think that your script is mighty dangerous. It sounds >like a recipe for data loss to me. Postgres is considerably more >robust than this, and I think you're trying to cover up some serious >problems that you may have, likely with your hardware. And you have pointed out my concern. Even though we have WAL enable, we have intentionally disabled both fsync and fdatasync inside the kernel because of other reasons. As long as there are ways I can eliminate database being corrupted (or correctly and automatically detected the corruption and drop the tables if necessary), that should satisfy my need. The persistence of the data is important for me, but disk performance and availability of a funcational database has a higher priority for my need. Thanks, --muteki _________________________________________________________________ Let the advanced features & services of MSN Internet Software maximize your online time. http://click.atdmt.com/AVE/go/onm00200363ave/direct/01/
>At the very least you should be running 7.2.4. We do not make >dot-releases for entertainment value, we make them because there >are critical bug fixes. In particular, this 7.2.4 fix: >* Ensure pg_clog updates are sync'd to disk before marking checkpoint >complete >might well have something to do with your problems. Do you know the fix to allow forcefully drop a table (even though the relfilenode doesn't exist) is in 7.2.4? >Views don't have underlying files at all. Shared tables live in a >different directory. You had better study the system catalogs more >carefully before you start writing scripts to hack them. Thanks for the valuable information. Actually, I have tried to focus on chapter 3 of PostgreSQL 7.2 Documentation and having difficulty in finding the information you mentioned above. Am I reading the right document? Thanks, --muteki _________________________________________________________________ Optimize your Internet experience to the max with the new MSN Premium Internet Software. http://click.atdmt.com/AVE/go/onm00200359ave/direct/01/
On Wed, Feb 11, 2004 at 10:08:30PM -0800, muteki muteki wrote: > And you have pointed out my concern. Even though we have WAL > enable, we have intentionally disabled both fsync and fdatasync > inside the kernel because of other reasons. As long as there are > ways I can eliminate database being corrupted (or correctly and > automatically detected the corruption and drop the tables if > necessary), that should satisfy my need. Well, simple choice. If you don't use fsync or fdatasync then there are no guarentees for your data. Simple as that. I guess you need to evaluate exactly how much you value it... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
"muteki muteki" <muteki_f@hotmail.com> writes:
> And you have pointed out my concern. Even though we have WAL
> enable, we have intentionally disabled both fsync and fdatasync
> inside the kernel because of other reasons.
[blinks...] Surely you're joking?
If you insist on running a configuration as brain-dead as that, you
*will* get data corruption on power failures. And it's your own
fault. Don't expect any sympathy here.
regards, tom lane
On Wed, Feb 11, 2004 at 10:08:30PM -0800, muteki muteki wrote: > Thanks for the information. If I can simply do a drop-in replacement, > that may be something I can try. But I am still thinking there could > be other cases database will get corrupted due to power failure and > will not be able to startup correctly again. (missing some pg_files) Under nromal operation, there is _no way_ you should get a corrupted database from a power failure. PostgreSQL's WAL system is designed to handle machine crashes, but only. . . > enable, we have intentionally disabled both fsync and fdatasync > inside the kernel because of other reasons. . . .if you don't do things like that. That is, frankly, just a recipe for disaster. Your attempts to work around this are likely to cause at least as much damage as the corruption you might get from power failures. I advise you very strongly to reconsider your approach. > The persistence of the data is important for me, but disk performance > and availability of a funcational database has a higher priority for my > need. I think you are very likely to make your database all but completely useless with this strategy. What will you do if you get, say, a corrupted pg_class table? Can't drop that -- you'll have no database that you can see. A -- Andrew Sullivan
> e.g. > pg_shadow_usename_index > pg_stat_activity > pg_stat_database > They are system views and correspond to no file. See PostgreSQL's documents.