Обсуждение: 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