Re: crash proof for semi-embedded system

On Apr 11, 2013, at 5:11 AM, David Welton <davidnwelton(at)gmail(dot)com> wrote:

> Hi,
>
> I'm going to be deploying Postgres in a semi-embedded system where end
> users might simply power the thing off from one moment to the next.
> Or the disk might start to go wonky, or any number of other problems.
> Because it's a standalone device, it may well run in an environment
> where we can't ship backups off of the machine.
>
> I've been reading this:
>
> http://www.postgresql.org/docs/9.2/static/continuous-archiving.html

You should take a look at http://www.postgresql.org/docs/current/static/wal.html
too.

>
> And it looks pretty good. It appears that, since I can't really back
> things up to a separate disk unit, it will "just work" unless the disk
> gets corrupted? In other words, in the case of someone pulling the
> power plug, it ought to be able to get things up and running more or
> less automatically, correct? Besides utilizing that, and keeping the
> fsync option set to true, what other steps can I take to make sure
> that data is not lost even in extraordinary circumstances? Having to
> manually fix things up is acceptable in the use case we're planning
> for, even if it's clearly preferable to not have to intervene.

If you don't do anything extra, postgresql should survive power
being pulled, the disk being pulled and anything else you do,
as long as the underlying filesystem isn't damaged in the process.
It writes, and commits, all changes to the WAL as they're made,
and uses that to replay changes at startup if needed. That's
all assuming that your storage layer doesn't lie about fsync -
which is something to check, especially on embedded hardware
(there's a tool to do that at the link above).

It does mean that the database might not be immediately
available at system startup, while it's recovering, so your app
should be able to deal with that.

If you only have a single storage device, that's about the best
you can do (though taking a periodic backup for disaster recovery
wouldn't be the worst idea in the world).

Cheers,
Steve