Re: Disk Failure Scenarios

On Wed, Apr 26, 2006 at 11:54:23PM -0400, Michael Artz wrote:
> -Disk1: If the OS/Postgres install disk fails, its possible to
> reinstall the OS and the same version of Postgres and point it at disk
> 3 and everything should run, right?

Only if that doesn't include $PGDATA

> -Disk 2: If the transaction log dies, all changes since the last
> checkpoint are lost, right? Again, if I set up an empty pg_xlog
> directory somewhere else, the DB should run just fine, right?

No, because there's no way to know what state the data pages are in.
Data may have made it to disk, may not have, partial page write, etc...

> -Disk 3: This holds all the pg_* tables, which means the structure of
> the DB, right? If this disk goes, would it be possible to reinitialize
> the database directory, create the new database, create a new
> tablespaceA on Disk 4, and create a new tableA, and somehow have it use
> the data pages for tableA that are already on disk? Does it change if
> tableA inherits from table_master?

*maybe*, but it's likely to be extremely painful, if it works at all.

> -Disk 4: We're screwed without backups.
>
> -Disk 5: I figure that we can just recreate any indexes, right? Can we
> safely drop indexB if the data pages for the index don't exist on disk
> (i.e. the tablespace is empty)? Will Postgres do the "right" thing and
> delete the knowledge of the index from the pg_* tables and then stop?

You'll probably need to re-create the appropriate files and then
REINDEX. This is the only disk where you have any real chance of
recovering from a failure without losing data (other than the binaries).

Now the real question is: why are you trying to run without raid?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461