Thread: PostgreSQL 9.1, replica and unlogged tables

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?


Thank you in advance,            \ferz


PS: I've written some simply tests and I've seen that inserts on
unlogged tables are 10 times faster.

On Tue, Sep 13, 2011 at 9:11 AM, Ferruccio Zamuner <nonsolosoft@diff.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

> Writing to any table on the standby is strictly forbidden so you can
> forget having your own volatile copy. 

Hello,

It should be possible to declare a -non postgres- SQL/MED table pointing e.g. to a csv on localhost, souldn't it ?

best regards,

Marc Mamin