Re: [GENERAL] Can Postgres 8.x start if some disks containing tablespaces are not mounted?

------=_Part_5513_26404606.1206990913610
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Mon, Mar 31, 2008 at 11:40 PM, Tom Lane <tgl@[EMAIL PROTECTED]
> wrote:

> "Morris Goldstein" <morris.x.goldstein@[EMAIL PROTECTED]
> writes:
> > Suppose I have a database with $PGDATA on /dev/sda, and a tablespace
> > directory on /dev/sdb. Will Postgres start successfully if /dev/sda is
> > mounted and /dev/sdb is not? If not, why not?
>
> It will start, but you will have unpleasant failures when you try to use
> tables in the secondary tablespace ... note that if autovacuum is on,
> that is likely to happen even without any explicit action on your part.
>
>
One of the gripes I have with postgres is that, that it won't even
complain
if one of the segments of a relation goes missing unless the missing
segment
is referred to by an index!!!

The most troublesome part is that count(*) (i.e seq scan) scans only upto
the last sequential segment found. Here's a case in example:

Healthy:
--------
count(*) : 2187001
size: 2441 MB
segments: 17651, .1, .2

Corrupt: 17651.1 missing
-------------------------
count(*) : 917503
size: 1024 MB
segments: 17651, .2
select max(a) from temp: 2187001 (uses index to locate the last tuple in
segment .2)

select a from temp where a = (select max(a) from temp)/2
ERROR:  could not read block 156214 of relation 1663/11511/17651: read
only
0 of 8192 bytes

retore missing segment:
-----------------------
select a from temp where a = (select max(a) from temp)/2
  : 1093500


    I think that the counter-argument would be that this has never been
re****ted in the field, but I wish our metadata records this somehow, and
re****ts an ERROR if it finds that a segment is missing.

Best regards,
-- 
gurjeet[.singh]@[EMAIL PROTECTED]
 gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

------=_Part_5513_26404606.1206990913610
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Mon, Mar 31, 2008 at 11:40 PM, Tom Lane &lt;<a
href="mailto:tgl@[EMAIL PROTECTED]
">tgl@[EMAIL PROTECTED]
>&gt; wrote:<br><div
class="gmail_quote"><blockquote class="gmail_quote" style="border-left:
1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;">
<div><div></div><div class="Wj3C7c">&quot;Morris Goldstein&quot; &lt;<a
href="mailto:morris.x.goldstein@[EMAIL PROTECTED]
">morris.x.goldstein@[EMAIL PROTECTED]
>&gt;
writes:<br>
&gt; Suppose I have a database with $PGDATA on /dev/sda, and a
tablespace<br>
&gt; directory on /dev/sdb. Will Postgres start successfully if /dev/sda
is<br>
&gt; mounted and /dev/sdb is not? If not, why not?<br>
<br>
</div></div>It will start, but you will have unpleasant failures when you
try to use<br>
tables in the secondary tablespace ... note that if autovacuum is on,<br>
that is likely to happen even without any explicit action on your
part.<br>
<br></blockquote></div><br>One of the gripes I have with postgres is that,
that it won&#39;t even complain if one of the segments of a relation goes
missing unless the missing segment is referred to by an index!!!<br><br>
The most troublesome part is that count(*) (i.e seq scan) scans only upto
the last sequential segment found. Here&#39;s a case in
example:<br><br>Healthy:<br>--------<br>count(*) : 2187001<br>size: 2441
MB<br>segments: 17651, .1, .2<br>
<br>Corrupt: 17651.1 missing<br>-------------------------<br>count(*) :
917503<br>size: 1024 MB<br>segments: 17651, .2<br>select max(a) from temp:
2187001 (uses index to locate the last tuple in segment .2)<br><br>select a
from temp where a = (select max(a) from temp)/2<br>
ERROR:&nbsp; could not read block 156214 of relation 1663/11511/17651:
read only 0 of 8192 bytes<br><br>retore missing
segment:<br>-----------------------<br>select a from temp where a =
(select max(a) from temp)/2<br>&nbsp; : 1093500<br>
<br>
<br>&nbsp;&nbsp;&nbsp; I think that the counter-argument would be that
this has never been re****ted in the field, but I wish our metadata records
this somehow, and re****ts an ERROR if it finds that a segment is
missing.<br><br>Best regards,<br>
-- <br>gurjeet[.singh]@[EMAIL PROTECTED]
>singh.gurjeet@[EMAIL PROTECTED]
 gmail | hotmail
| indiatimes | yahoo }.com<br><br>EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br><br>Mail
sent from my BlackLaptop device

------=_Part_5513_26404606.1206990913610--