Re: PostgreSQL 9.1, replica and unlogged tables

On Tue, Sep 13, 2011 at 9:11 AM, Ferruccio Zamuner <nonsolosoft(at)diff(dot)org> wrote:
> I'm starting to play with PostgreSQL 9.1, thank you all for this nice and
> sweet piece of software.
>
> I've two hosts in my cluster:
>
> a) postgresql master
> b) postgresql standby
>
> I've created two tables on master:
>
> create table test_logged (id serial, nome text);
> create unlogged table test_unlogged (id serial, nome text);
>
>
> Both tables appears on standby too but on standby following query:
>
> select * from test_unlogged;
>
> gives me following message:
>
> ERROR:  cannot access temporary or unlogged relations during recovery
>
>
> I understand that unlogged table are not replicated, but I expected:
> 1) not see defined unlogged tables on standby
> OR
> 2) see them void on standby and use them to store different set of records
> for each standby (like web sessions) those need not to be replicated in the
> cluster.
>
> Robe on #postgresql suggest me to run another postgresql instance on each
> custer host node to store local volatile data (like web app sessions).
> Is it this the best option actually?

depends. The postgresql system tables which contain your schema are
replicated along with everything else which is why the table is
visible on the standby -- however the data itself is not replicated.
I somewhat prefer the existing behavior vs the alternatives you list
-- it just seems the most regular.

Writing to any table on the standby is strictly forbidden so you can
forget having your own volatile copy. Regarding setting up a volatile
postgresql instance, that's too difficult to answer based on the
information given, I'd say only do that if you absolutely can't work
your requirements around a standard HS/SR setup. One possible
workaround for managing volatile data in the standby would be using
function managed data stores (like a pl/perl hash, etc). Note that
those data stores wont honor mvcc, so use caution.

merlin