Re: [Q] post-crash behaviour - Mailing list pgsql-general

On 3/14/01, 8:26:08 AM, Fabien COUTANT <Fabien.Coutant@steria.fr> wrote
regarding [GENERAL] [Q] post-crash behaviour:

> Hello,
> I don't know if this the right place to post this, please excuse me if
> I'm wrong.  I didn't find any clearly appropriate E-mail for this on the
> website (I could not find the mailing lists page).

This is the right place - although the mailing lists are pretty well
indicated. Bear in mind that my answers are from a Postgresql user not a
developer.

> I'm working on a project where we are questionning ourselves about the
> reliability of PostgreSQL (our customer wants to compare to Oracle but
> it's no matter for my question).

> Our main concern is databases files' internal structure corruption.
> What is PostgreSQL's behaviour regarding software and hardware failures:
> - soft termination of the server (kill -INT),

I'm not sure that kill -INT does anything to the backends. Anyway -
closing the backend on a client should be fine.

> - hard termination (kill -9, server bug),

Considered a BAD THING TO DO.

> - power loss (or disk failure) that occurs in the middle of disk I/O,

Also a BAD THING TO DO.

However v7.1 with WAL should reduce the risks of these last 2. So long as
the files aren't scrambled, any transactions written to the WAL
(Write-Ahead Log) should be re-run on startup. Of course, beyond that you
need to look at the reliability of the underlying file-system.

> I searched the docs and found nothing about this.  I also searched
> newsgroups with no success.

It's quite a precise question. You need to consider what the MTBF you are
looking for is and what problems you expect.

> I only found something about a setting of page size in the sources that
> should equal physical disks atomic size, but this didn't convince me
> since it is not from an "official" source.

Presumably because it makes it more likely that a page-write produces one
disk-write, so less to go wrong.

> I would like to know if, in those cases:
> - is the server able to restart and operate correctly on correct data
>   (transaction wise) ?

Not sure I understand you, but in the absence of data corruption, PG
should respect transactions.

> - if internal file corruption could occur, would the server tell on
>   startup something like "The database is corrupted, can't start" ?
>   And this in a deterministic way ?

I believe not - to do so would require a complete scan of the database.

> - or is there a risk that the server could start and run with corrupted
>   files and data ?

Yes - but bad system files should be noticed immediately, bad indexes
quicker. If "John Smith" has been corrupted into "Johm Snith" you might
never notice if the field is not corrupted.

A "VACUUM ANALYZE" on all the tables at startup should pick up any index
corruption.

> Thanks if anyone can answer this.

> --
> Best regards,
> Fabien COUTANT
> STERIA

Well - HTH but bear in mind I'm only speaking for myself here.

The bottom line is if your question is "Is PostgreSQL as reliable as
Oracle" the answer would have to be "depends" - for the cost of an Oracle
installation you can buy a lot of duplicate hardware.

On the other hand - if you need someone big to sue if things go wrong,
Oracle might well be the better choice.

- Richard Huxton