Solved

Postgres randomly disconnects users from a Hot Standby database

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

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 62

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 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