Solved

Postgres randomly disconnects users from a Hot Standby database

Posted on 2012-04-02
4
833 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

929 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

20 Experts available now in Live!

Get 1:1 Help Now