Thread: Can Postgres 8.x start if some disks containing tablespaces are not mounted?

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?

Morris

Morris Goldstein napsal(a):
> 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 is not good idea to run PostgreSQL in your scenario. However PostgeSQL needs
only catalog tables to start. Until you don't touch data stored on unmounted
disk you should not get any error.


        Zdenek

"Morris Goldstein" <morris.x.goldstein@gmail.com> 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.

            regards, tom lane

On Mon, Mar 31, 2008 at 11:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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 reported in the field, but I wish our metadata records this somehow, and reports an ERROR if it finds that a segment is missing.

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

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

On Mon, Mar 31, 2008 at 2:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Morris Goldstein" <morris.x.goldstein@gmail.com> 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

Understood. I can guarantee that there will be no such accesses until
/dev/sdb is mounted.

> ... note that if autovacuum is on,
> that is likely to happen even without any explicit action on your part.

That's a good point. We don't use autovacuum, and I guess we'd have to
forego it. (Or can it be turned on and off dynamically?)

But that makes me wonder: what about this sequence of events:

- Postgres running normally on /dev/sda and /dev/sdb.
- Update to table in /dev/sdb tablespace is committed but still exists in WAL.
- Postgres crashes (e.g. power failure).
- Postgres starts with /dev/sda only.
- Recovery needs to update table in /dev/sdb tablespace.

I assume bad things will happen in this case.

Jack

On Tue, Apr 1, 2008 at 7:42 PM, Morris Goldstein <morris.x.goldstein@gmail.com> wrote:

200% correct.

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Morris Goldstein napsal(a):

> But that makes me wonder: what about this sequence of events:
>
> - Postgres running normally on /dev/sda and /dev/sdb.
> - Update to table in /dev/sdb tablespace is committed but still exists in WAL.
> - Postgres crashes (e.g. power failure).
> - Postgres starts with /dev/sda only.
> - Recovery needs to update table in /dev/sdb tablespace.
>
> I assume bad things will happen in this case.

yes, Probably best solution in this case is to implemented tablespace
availability into postgreSQL and PostgreSQL shouldn't start when tablespace is
missing.

        Zdenek

On Wed, Apr 2, 2008 at 8:44 AM, Zdenek Kotala <Zdenek.Kotala@sun.com> wrote:
> Morris Goldstein napsal(a):
> > - Postgres running normally on /dev/sda and /dev/sdb.
> > - Update to table in /dev/sdb tablespace is committed but still exists in
> WAL.
> > - Postgres crashes (e.g. power failure).
> > - Postgres starts with /dev/sda only.
> > - Recovery needs to update table in /dev/sdb tablespace.
> >
> > I assume bad things will happen in this case.
> >
>
>  yes, Probably best solution in this case is to implemented tablespace
> availability into postgreSQL and PostgreSQL shouldn't start when tablespace
> is missing.

I would rather see Postgres complain on the first reference to the
missing tablespace, (i.e. current behavior). Our schema has multiple
schemas, some of which are needed for our application at startup time;
others are needed later, once startup is complete.

Without going into a lot of details about our application, I'll just
say that the ability to start Postgres with just the "startup schema"
present is incredibly useful. On a SAN, we put this startup schema
on a volume that is permanently bound to a node, and the other schema
on a volume that can fail over to another node. Tablespaces allow us
to start Postgres and then fail back the volume containing the second
schema.

Morris

"Morris Goldstein" <morris.x.goldstein@gmail.com> writes:
> Without going into a lot of details about our application, I'll just
> say that the ability to start Postgres with just the "startup schema"
> present is incredibly useful. On a SAN, we put this startup schema
> on a volume that is permanently bound to a node, and the other schema
> on a volume that can fail over to another node. Tablespaces allow us
> to start Postgres and then fail back the volume containing the second
> schema.

[ retrieves eyebrows from ceiling... ]  Have you actually tested this?
It sounds to me like you are thinking you can reattach the tablespace
to a different database instance, which is guaranteed not to work.
You've got to fail over the entire instance as a whole.

            regards, tom lane

On Wed, Apr 2, 2008 at 10:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Morris Goldstein" <morris.x.goldstein@gmail.com> writes:
>
> > Without going into a lot of details about our application, I'll just
>  > say that the ability to start Postgres with just the "startup schema"
>  > present is incredibly useful. On a SAN, we put this startup schema
>  > on a volume that is permanently bound to a node, and the other schema
>  > on a volume that can fail over to another node. Tablespaces allow us
>  > to start Postgres and then fail back the volume containing the second
>  > schema.
>
>  [ retrieves eyebrows from ceiling... ]  Have you actually tested this?
>  It sounds to me like you are thinking you can reattach the tablespace
>  to a different database instance, which is guaranteed not to work.
>  You've got to fail over the entire instance as a whole.

No, nothing like that. Node1 has a database. Let's say that pg_data is
on /dev/sda and the database hasa tablespace on /dev/sdb.  node1
fails. /dev/sdb is actually a SAN volume, and can fail over to another
node, node2. /dev/sdb2 also has non-postgres data that node2 needs.
When the failover happens, node2 uses the non-postgres data and leaves
the postgres data alone. node1 restarts. Postgres restarts and, as
long as it doesn't refer to the /dev/sdb tablespace, my understanding
is that everything is OK. Eventually, /dev/sdb fails back to node1,
and at that point references to the tablespace on that volume should
work.

(Why not give postgres its own volumes? Long and not very interesting story.)

None of this is implemented -- I'm checking out the issues here as
part of a design task. I'm not using autovacuum, and the only other
issue I've turned up is references to the tablespace on /dev/sdb from
the WAL.

Morris

On Wed, Apr 02, 2008 at 02:42:08PM -0400, Morris Goldstein wrote:
> (Why not give postgres its own volumes? Long and not very interesting story.)

But relevant to this case.  I think you need to give it its own volumes.

A