Thread

1. Recover from failed files

   Nyamul Hassan <nyamul@gmail.com> — 2012-11-04T23:28:15Z

   Hi,
   
   A small office customer of ours is in a bit of fix due to a HDD crash on
   their PGSQL machine.  Their IT support staff on duty left around a week
   ago, without notice, and apparently, he was not good at keeping backups.
   
   Upon inspecting the hard drive, we were able to salvage the "data" folder,
   but when we try to load it into the PGSQL version of the data folder (8.2),
   the server would not load.  Upon closer inspection, we suspect the culprit
   to be the file 0015 in pg_clog which is 214kb instead of 256kb size of all
   other files in the same folder.
   
   Is there any way that we can salvage the data from this data folder?
   
   Thank you in advance for your suggestions.
   
   Regards
   HASSAN
   

2. Re: Recover from failed files

   Richard Huxton <dev@archonet.com> — 2012-11-05T10:48:06Z

   On 04/11/12 23:28, Nyamul Hassan wrote:
   > Upon inspecting the hard drive, we were able to salvage the "data" 
   > folder, but when we try to load it into the PGSQL version of the data 
   > folder (8.2), the server would not load.  Upon closer inspection, we 
   > suspect the culprit to be the file 0015 in pg_clog which is 214kb 
   > instead of 256kb size of all other files in the same folder.
   >
   > Is there any way that we can salvage the data from this data folder?
   
   1. Make sure you have a complete backup of all of the data dir (pg_xlog, 
   clog - everything) before making any changes.
        This one is vital. Don't skip it. Any fixes you attempt may end up 
   making things worse.
   
   2. What is the exact error-message you get when trying to start up 
   PostgreSQL?
        When starting the server, try doing it directly in single-user mode 
   until everything is up and running
        http://www.postgresql.org/docs/8.2/static/app-postgres.html
   
   3. Have a quick read of the blog-post below as a start point explaining 
   the various files.
        It's a useful introduction and can give you some keywords to search 
   against.
        
   http://it.toolbox.com/blogs/database-soup/pg_log-pg_xlog-and-pg_clog-45611
   
   4. You may end up needing the resetxlog tool, but don't just blindly run it:
        http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html
   
   5. Version 8.2 is end-of-life since 2011.
        Once your database is working again, dump the data and upgrade as 
   soon as possible.
        Until you upgrade, make sure you have the last release of 8.2.23 
   running.
   
   6. Obviously, make sure backups are scheduled regularly.
   
   It may well be that you can get things up and running by just padding 
   pg_log/0015 to 256KB with zeroes. That doesn't mean your database will 
   be 100% consistent though - transaction information will have been lost.
   
   But before you do anything drastic, do steps #1 and #2.
   
   --
      Richard Huxton