[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 926
  • Last Modified:

Postgres randomly disconnects users from a Hot Standby database

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
Alex Matzinger
Asked:
Alex Matzinger
  • 2
  • 2
1 Solution
 
gheistCommented:
You get enough help in postgresql forums. And here you post insufficient information:

http://archives.postgresql.org/pgsql-bugs/2012-04/msg00010.php
0
 
Alex MatzingerAuthor Commented:
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
 
gheistCommented:
Here you did not mention you use a transaction that lasts eternity so no help came.
0
 
Alex MatzingerAuthor Commented:
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now