#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    6
    Rep Power
    0

    Question Checking data and index file corruption


    Hi all,
    how could we check the data files or index corruption in postgres. Is there any command or way by which we can know it and works for repairing them.
    Thanks a lot in advance..
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Originally Posted by shadow1983
    Hi all,
    how could we check the data files or index corruption in postgres.
    I don't understand what you mean with that.

    What kind of corruption?
    Are you talking about hardware (harddisk) failures?

    Is there any command or way by which we can know it and works for repairing them.
    If you are talking about hardware failures, then the only "tool" is restoring a backup.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    6
    Rep Power
    0

    Question


    Originally Posted by shammat
    I don't understand what you mean with that.

    What kind of corruption?
    Are you talking about hardware (harddisk) failures?

    If you are talking about hardware failures, then the only "tool" is restoring a backup.
    Actually I would like to ask database curruption or table corruption. I am not talking about hardware failures. I am just talking about that sometimes it happens that the index files or data files sometimes corrupted and doesnt work properly.
    Like in mysql, myisamchk is the command which is basically used for checking myisam tables
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Originally Posted by shadow1983
    Actually I would like to ask database curruption or table corruption.
    If it's not corruption through a hardware failure, then I don't know what kind of corruption you are talking about.

    Like in mysql, myisamchk is the command which is basically used for checking myisam tables
    I don't know what this check is doing, but there is nothing (needed) in Postgres to "check" a table...
  8. #5
  9. So help me Codd
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Location
    Brazil
    Posts
    362
    Rep Power
    25
    I am just talking about that sometimes it happens that the index files or data files sometimes corrupted and doesnt work properly.
    PostgreSQL doesn't corrupt tables nor indexes. That's a bug in MySQL, myisamchk and REPAIR are some workaround to fix the problem until it happens again. Don't worry, you don't have these problems with PostgreSQL.

    If you use VACUUM FULL to much, you might have some index bloat, but that's not a corrupt index. You can fix this bloat with REINDEX, that's it.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    6
    Rep Power
    0
    Originally Posted by pgFrank
    PostgreSQL doesn't corrupt tables nor indexes. That's a bug in MySQL, myisamchk and REPAIR are some workaround to fix the problem until it happens again. Don't worry, you don't have these problems with PostgreSQL.

    If you use VACUUM FULL to much, you might have some index bloat, but that's not a corrupt index. You can fix this bloat with REINDEX, that's it.

    Thanks a lot for responding...
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    1
    Rep Power
    0
    Well, you can try this -
    Use ALTER TABLE command: Modify the structure of MYI file using the 'Alter table' command so that it can insert more records. Use the syntax given below-
    ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;
    Using myisamchk utility: MyISAM table corruption can be resolved using the 'myisamchk' utility. The command will check and then repair the damage. Run the command-line utility as follows-
    shell> myisamchk --silent --force --fast --update-state \
    --key_buffer_ --sort_buffer_ \
    --read_buffer_ --write_buffer_ \
    /path/to/datadir/*/*.MYI

    However, if the issue still persists, then you can take the commercial MySQL Repair software to get rid of this issue. I hope this helps you out...

    All the best!
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Posts
    3
    Rep Power
    0
    Here is what i do and it seems to get bad indexs and missing chunks

    pg_dumpall > /dev/null 2> dump.err

    It goes by 5 times faster than writing to disk.

IMN logo majestic logo threadwatch logo seochat tools logo