PostgreSQL HA questions

First, I'm moving this to -general, because this is way off topic for
-hackers as near as I can tell.

On Tue, Sep 26, 2006 at 10:39:18PM +0200, Dragan Zubac wrote:

1. Is it possible for multiply PostgreSQL instances (engines,cores) to use
same DATA space?

No. In fact, this is a very good way to cause corruption.

What you _can_ do is set up a "watchdog" process that allows a
different machine to take over the filesystem on a shared disk array,
for instance, and come back up in recovery mode. So your outage is
roughly as long as the time to notice your primary node failed, plus
the time to recover from database crash.

There are various software packages that will allow you to do this.
NOT ALL OF THEM WORK WELL. Go back and read that sentence again.
No, I am not saying this because of any painful experiences I have
ever had ;-)

2. Becouse of vaccuming issues,is it possible to create such a client
process which will use two identical tables,and on receiving a signal,it
will switch between those tables.For example,first a client application uses
table1,after some time,send a signal to process,it will switch using
table2,so You can freely vacuum table1 or whatsoever.After vacuuming
done,table 1 will sinchronize with table2 and keep up-to-date until You send
next signal to application,which will switch using table1,so You can vacuum
table2.

It isn't clear to me why you think you need to do this: vacuum
doesn't block your queries anyway. If the idea is that you have a
table that you'd rather TRUNCATE and not have to vacuum, however,
that makes sense. There are several strategies for this. My
colleague Chris Browne seems really to like this kind of
functionality, and has discussed it more than once on the -general
list. I think you can find his detailed outlines of how to do this
sort of thing by searching for "rotor tables".

A

--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin

ajs@crankycanuck.ca (Andrew Sullivan) writes:

It isn't clear to me why you think you need to do this: vacuum
doesn't block your queries anyway. If the idea is that you have a
table that you'd rather TRUNCATE and not have to vacuum, however,
that makes sense. There are several strategies for this. My
colleague Chris Browne seems really to like this kind of
functionality, and has discussed it more than once on the -general
list. I think you can find his detailed outlines of how to do this
sort of thing by searching for "rotor tables".

I'd suggest looking at the section in the documentation on
Partitioning; the mechanisms there look like the Better Way these
days.

<http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html&gt;

There were some things that were Pretty Neat about rotor tables; as of
8.1, the benefits gotten from constraint propagation with partitioning
seems to make that a much more attractive way to go about things.

There are always going to be some caveats for whatever mechanism is
used to partition data; it looks like 8.1's constraint propagation
pushes preference towards using inheritance...
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/linux.html
"It is easier to optimize correct code, than correct optimized code"
-- Yves Deville