Solved

Postgres randomly disconnects users from a Hot Standby database

Posted on 2012-04-02
4
821 Views
Last Modified: 2012-05-17
I am running Postgres 9.1.2(and 9.0.5) and have a primary database set up with 5 hot standby's.  I run a fairly large update once an hour.  During this update, I get the following message on my standby databases.  This does not happen every hour, nor does it happen at any specific times, just during random updates, at random times.

2012-04-02 13:36:13.269 PDT,"username","database",39523,"127.0.0.1:57893",4f79ffad.9a63,1,"",2012-04-02 12:36:13 PDT,3/32,0,FATAL,40001,"terminating connection due to conflict with recovery","User query might have needed to see row versions that must be removed.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""

Open in new window


The issue is that the user that is being disconnected is only executing a "SELECT 1;" while connected to the database.  It does this every second indefinitely.  When it does get disconnected, it is able to immediately reconnect, and resume selecting 1, but the disconnect is very strange for a user that isn't using any data/tables in the database.

Has anyone experienced this?  Or know why this might be happening?

Thank you
0
Comment
Question by:Alex Matzinger
  • 2
  • 2
4 Comments
 
LVL 61

Expert Comment

by:gheist
ID: 37876524
You get enough help in postgresql forums. And here you post insufficient information:

http://archives.postgresql.org/pgsql-bugs/2012-04/msg00010.php
0
 
LVL 4

Accepted Solution

by:
Alex Matzinger earned 0 total points
ID: 37953729
After submitting the problem to postgres, the problem has been solved.  This was due to postgres' inablilty to vacuum a database with long standing connections.  rolling back after every statement fixed the issue
0
 
LVL 61

Expert Comment

by:gheist
ID: 37954698
Here you did not mention you use a transaction that lasts eternity so no help came.
0
 
LVL 4

Author Comment

by:Alex Matzinger
ID: 37981501
I did not realize that was the case when i opened this question, while talking to the folks at postgres, it came to my attention that the connection was persistent.  I obviously would have included that information in the ticket.  I provided them with the same info i had here, they just prompted me for more details.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now