Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

After a server crash, it would seem that all transactions going back a number of months are not showing up in the live Postgres DB. Investigation has found all xlog files that does contain the data but how do you commit these?

I would assume that you would need to do so sequentially from the oldest xlog file through to the most recent one (in date order).

Postgres Version 8.0 DB Size is around 500MB Number of xlog files is 14 files, each 16MB

Any help would be much appreciated.

share|improve this question

migrated from stackoverflow.com Jan 17 '13 at 18:04

This question came from our site for professional and enthusiast programmers.

2  
8.0? Really? –  a_horse_with_no_name Jan 17 '13 at 12:05
    
Yes, to say that this is a legacy system will be an understatement... Need to get it fixed before work can be started on getting it updated or migrated :( –  Daniel Jan 17 '13 at 12:09
    
Is the database mostly in idle (I mean no DML going on) state? That 14 files are not very many for a few months... Have you read this? postgresql.org/docs/8.0/static/backup-online.html –  dezso Jan 17 '13 at 19:33

1 Answer 1

This is a case where frankly the best thing to do is call for professional help. It is a best effort basis. If you are lucky someone can help process the data in the xlog files. The same would go through at least 9.3

The xlogs are normally run when the db starts. You can't just run them against an exiting db because they are binary patch files against the actual table files and so once the db starts running in read/write mode, these cannot be properly validated.

Your first orders of business should be finding out why you had the crash, why these were not properly re-run when you started up, and seeing if you can take a properly done base (file-level) backup to get running again. If not, you are going to have a lot of work ahead.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.