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