From: | Chip Steele <steele(dot)chip(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Streaming Replication in PostgreSQL9.0 b4: Standby dropping connections |
Date: | 2010-08-20 20:15:32 |
Message-ID: | AANLkTimtYNphFQ0pAHT5oZjCzn9eYG+Bfv2=75_t-Pkp@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi,
--- Reposting with more information ---
In PG9.0-b4 I have successfully set up a primary-secondary pair with
streaming replication, with the secondary in hot_standby mode.
I have an application on the standby (and primary) which has constant
connections open to the database. Occasionally it refreshes its cache of
data from the hot_standby secondary server.
I am intermittently getting and error when updating the application's cache.
The cache update involves 2 queries:
SELECT COUNT(*) FROM core_list; -- this table has about 5 Million records
and
SELECT line, COUNT(*) FROM core_state WHERE state='normal' GROUP BY line
ORDER BY line; -- this table has about 50million records
normal operation of the database would not update the core_list table, and
would update maybe up to 10% of the core_state table between application
cache updates
table definitions follow:
CREATE TABLE core_list(
id int NOT NULL DEFAULT nextval('core_list_id_seq'),
name varchar(64) NOT NULL,
valid BOOLEAN NOT NULL DEFAULT '1'
);
unique index on the first 2 fields, index on the 3rd.
CREATE TABLE core_state(
core_id int NOT NULL,
line smallint NOT NULL,
state core_state NOT NULL
);
CREATE INDEX cs_core_id_idx ON core_state(core_id);
CREATE INDEX cs_state_line_idx ON core_state(state,line);
CREATE UNIQUE INDEX cs_core_id_line_idx ON core_state(core_id,line)
When I check the PostgreSQL logs with the same timestamp, I see the
following messages:
FATAL: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be
removed.
HINT: In a moment you should be able to reconnect to the database and repeat
your command.
Now, this message explains that there is a problem, but I couldn't find
anything in the streaming replication/hot standby documentation indicating
that this sort of thing might occur.
Is there any way to avoid it? Any further information about this specific
problem? Can I provide anything else?
Thanks in advance,
Chip
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Bax | 2010-08-23 11:24:42 | create a box from point? |
Previous Message | Rikard Bosnjakovic | 2010-08-20 16:05:49 | Re: Easiest way to extract owner-id from a third table |