Re: Hot Standby Design

On Tuesday 23 September 2008 14:15:34 Simon Riggs wrote:
> Hot Standby design has been growing on the PostgreSQL Wiki for some
> weeks now.
>
> I've updated the design to reflect all feedback received so far on
> various topics.
>
> http://wiki.postgresql.org/wiki/Hot_Standby
>
> It's not hugely detailed in every area, but it gives a flavour of the
> topics and issues related to them.
>
> Comments or questions welcome here, or I will discuss specific areas in
> more detail as I tackle those topics.
>

very nice work. very in depth. unfortunatly, this means it is long and the
hour is late... so here are some scattered thoughts i had while reading it :

* "However, some WAL redo actions will be for DDL actions. These DDL actions
are repeating actions that have already committed on the primary node, so
they must not fail on the standby node. These DDL locks take priority and
will automatically cancel any read-only transactions that get in their way."

Some people will want the option to stack-up ddl transactions behind
long-running queries (one of the main use cases of a hot slave is reporting
stye and other long running queries)

* Actions not allowed on Standby are:
DML - Insert, Update, Delete, COPY FROM, Truncate

copy from suprised me a bit, since it is something i could see people wanting
to do... did you mean COPY TO in this case?

* Statspack functions should work OK, so tools such as pgAdminIII should work.
pgAgent will not.

I presume this means the backend kill function would work? Also, can you go
into why pgAgent would not work? (I presume it's because you can't update
information that needs to be changed when jobs run, if thats the case it
might be worth thinking about making pgAgent work across different clusters)

* Looking for suggestions about what monitoring capability will be required.

one place to start might be to think about which checks in check_nagios might
still apply. Possibly also looking to systems like slony for some
guidence... for example everyone will want some way to check how far the lag
is on a stnadby machine.

* The following commands will not be accepted during recovery mode
GRANT, REVOKE, REASSIGN

How is user management done on a standby? can you have users that dont exist
on the primary (it would seem not).

... more to come i'm sure, but fading out... thanks again for the work so far
Simon.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL