Re: Hot Standby Design

On Wed, 2008-09-24 at 00:30 -0400, Robert Treat wrote:

> here are some scattered thoughts i had while reading it :

Thanks for your comments.

It is very detailed, so further feedback is going to be very beneficial
in making this all work in the way we hope and expect.

> * "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)

Scheduling tools can help here. Run set of queries on Standby, then when
complete run DDL on Primary and have its changes filter through.

> * 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?

I checked...

COPY TO is allowed, since it extracts data. So pg_dump will run. 
COPY FROM will not be allowed since it loads data. So pg_restore will
not run.

> * 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?  

Yes it will.

> 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)

Yes

> * 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 

check_pgsql will work, but its very simple.

check_postgres will also work, though many some actions could give
different or confusing results. e.g. last vacuum time will not be
maintained for example, since no vacuum occurs on the standby.

slony won't run on the standby either, so those checks won't work
either. 

> everyone will want some way to check how far the lag 
> is on a stnadby machine. 

Agreed

> * 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). 

No you can't have different users. (In time, I see this as a good thing
because it will allow us to move queries around to different nodes for
execution so that a single database acts like a "hive mind".)

Nearly everything must be set via the Primary. Users, passwords.
Changes to .conf files will be possible. There is no mechanism to auto
synchronise the .conf files between nodes.

The Standby is a Clone and not a Slave. A Slave is a separate database
that is forced to duplicate the actions of the Master. The Standby is an
exact copy, in every detail that matters.

I can see it might be desirable to have it work that way, but that's not
going to happen in the first release.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support