(Sorry if this gets posted twice ... forgot that the list doesn't like new, unregistered email addresses)
I'm setting up PG, and am curious about the failure scenarios of Postgres with respect to crashed disks. In a given Postgres installation across many disks, which sections of Postgres can fail "gracefully" (i.e. the customer data is safe and the installation can be recreated without backups)? I'm thinking of the scenario where you have numerous tablespaces with tables and indexes spread across them and have separated pg_xlog onto a separate disk. So the setup might be something like this:
Disk 1: OS + Postgres install Disk 2: pg_xlog Disk 3: initialized tablespace containing table_master Disk 4: tablespaceA containing tableA Disk 5: tablespaceB containing indexB
In this simplistic configuration, only Disk 4 contains any real customer data, right? If any of the other disks fail, would it be possible to slap in a replacement disk and rebuild the database install around disk 4? I.e.:
-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?
-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?
-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?
-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?