(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?
Thanks for any help,
-Mike
