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.

We are planning to use PostgreSQL in our application but are concerned about crash-safety and recovery.

I can't find any database crash recovery method or process in PostgreSQL. I know there must be something in PostgreSQL.

Advice? So if anyone can help me out to find this then that would be great.

share|improve this question

migrated from stackoverflow.com Aug 20 '12 at 22:27

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

    
I don't know why this question is down voted. If you don't know the answer then please don't miss use your power and if you know the answer then please give some hint at least and then you can down vote. If there anything is not clear in this question then please let me know. –  user965884 Aug 20 '12 at 20:16
2  
What do you mean with "crash recovery"? Backup and restore? –  edze Aug 20 '12 at 20:31
    
Crash recovery means if my database crashes due to some reasons then is there any possibility in postgressql that I can recover my data. –  user965884 Aug 20 '12 at 21:12
add comment

2 Answers

up vote 10 down vote accepted

Crash Safety

PostgreSQL is crash-safe within certain limits. It guarantees to always preserve committed data if the database system crashes or the host it's on reboots/loses power/crashes unexpectedly. That's what the D in ACID means - atomicity, consistency, isolation, durability.

The critical feature in PostgreSQL's crash safety is the write-ahead log (WAL). The documentation explains it much better than I ever could.

This crash safety is not a substitute for good backups.

  • PostgreSQL cannot protect you against file system corruption or hard drive failure. The storage system it uses needs to be reliable.

  • Hard drives and SSDs will fail, so RAID is necessary but not sufficient for protection. You need backups and/or replication to protect against multi-drive failures, file system corruption, etc.

  • There are certain settings documented in PostgreSQL that weaken the crash-safety guarantees it makes. For example, if you set fsync=off you give PostgreSQL permission to destroy your data if it crashes in exchange for going a bit faster.

  • PostgreSQL guarantees that it will not save uncommitted data, it will be lost if the system crashes before a transaction COMMITs. Statements not surrounded by BEGIN and COMMIT for this purpose commit when the statement returns.

There's more, but the short version is that Pg is crash-safe and durable, but you still need to look after the other layers in your infrastructure, which is why you need:

Backups

Don't just do a weekly pg_dump where you overwrite the same dump file over and over, that'll end in tears. Use more than one backup strategy, and keep historical backups not just a most recent backup.

For example, I keep both dumps and a continuous archiving/PITR replica. I keep daily pg_dumps for a week, then weekly dumps for a month and monthly dumps for a year to give me some timeline protection. For the PITR setup I refresh from a base backup every week; it's mostly there to give me the ability to recover to the nearest 5-minutes from any failure or catastrophic mistake. Nightly dumps won't help me recover the last 23 hours of data when I accidentally drop a table; PITR will.

There's lots more here:

Don't be one of the people who turns up on the Pg list saying "my hard drive crashed, how can I recover my database?". Be like this guy, who asked the question for fun because he had backups but was curious if his DB could be recovered. (Answer: probably not).

Repair or recovery of corrupt databases

If you're reading this because you found it in a search and you suspect you have a corrupt Pg database, read this before attempting any repair.

If by crash recovery you mean a repair tool to fix a damaged database, no, there isn't anything much. pg_resetxlog may save you from corrupt/lost transaction logs, but will probably corrupt your database in the process, so you usually need to dump and reload afterwards. There isn't any kind of fsck tool, nor is there anything like myisamchk because unlike MyISAM, Pg's tables are designed not to get corrupted as a routine part of normal operation. It'd actually be quite nice to have some sort of pg_dbcheck, but it would probably not be able to tell you much more than "Yup, there's some corruption here, go find a backup".

The database just doesn't get damaged in the first place unless:

  • The underlying hardware fails
  • The OS or file system has a bug
  • PostgreSQL has a bug
  • PostgreSQL is explicitly configured not to be durable
  • The user did something silly like went around deleting things from the database directory

In all of these cases automated repair would be impractical anyway.

Keep good backups of any database, not just Pg. Don't rely on being able to repair corruption, because you probably won't be able to.

share|improve this answer
add comment

It sounds you are looking for Point In Time Recovery: http://www.postgresql.org/docs/current/static/continuous-archiving.html

The chapter about high availability might also be interesting for you: http://www.postgresql.org/docs/current/static/high-availability.html

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.