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.
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:
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).
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:
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.
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
