14

I am using postgres.heroku.com to host my databases. This means that I have no way of changing server settings. Therefore, the answer to this question does not help me. Heroku is not willing to change their settings (I have contacted them).

I am wondering what the best way would be to hack PgAdmin III to keep the connection alive. I am thinking of things like creating an Autohotkey macro to automate user interface actions while PgAdmin is in the background, or maybe using some kind of network tool to force sending network messages on PgAdmins behalf.

I have also gotten an offer of 500$ for someone to change to code of PgAmin III. The developer of PgAdmin, will not modify the code, just because of Heroku.

What should I do? PgAdmin is superior in so many ways, it just has this drawback.

2
  • FYI, Heroku is not the only one with this problem. We are running into this issue on a Linux VM hosted on Azure and I think the problem is with the Service Provider closing idle TCP connections. It would be very nice if PgAdmin could expose the keep alive option – Jonas Stawski Aug 31 '17 at 20:45
  • 1
    I've had this issue with pgadmin since 2002! Surely pgadmin could be coded to auto reconnect once it times out rather than making you close the app, open it again, and reopen the entire tree? – Matthew Lock Nov 8 '17 at 8:47
18

libpq, the underlying PostgreSQL client library, has the keepalives option to enable TCP keepalives.

It looks like PgAdmin-III doesn't allow you to specify arbitrary connection parameters directly, but there's a workaround.

When you look at the connection configuration in PgAdmin-III, you will see a "service" option. This refers to the connection service file. To use it, create a ~/.pg_service.conf with contents like:

[myherokudb]
user=myusername
keepalives=1
connect_timeout=20
keepalives_idle=10

and when connecting from PgAdmin-III enter myherokudb in the service field.

This will cause PgAdmin-III to use the connection parameters specified in the service file, including enabling keepalives.

(If you're on Windows, the service file may be in another location; see the documentation).

There's no environment variable in libpq to control keepalives, so you can't set it that way, you'll have to use a service file.

Adding support for additional connection parameters to PgAdmin-III, or a checkbox in the connection options to control the keepalives parameter, should be pretty trivial. I wonder if Dave understood what you were asking for re your offer to fund the work.


Update: The service file is looked up at the location specified in the PGSYSCONFDIR environment variable. If unset, it defaults to a platform-specific location, which doesn't seem to be documented properly for Windows. I'll submit a documentation patch. The documentation for .pgpass shows its path as %APPDATA%\postgresql\pgpass.conf though, so ~/.pg_service.conf should be %APPDATA%\postgresql\pg_service.conf ... but it doesn't seem to be.

In fact, the correct path is:

%APPDATA%\postgresql\.pg_service.conf

So:

  • Start->Run
  • `%APPDATA%
  • create directory "postgresql" if it does not exist
  • create file ".pg_service.conf" as a text file with the contents given above (see note below re file naming)
  • In PgAdmin-III, enter "localhost" in the Host name, and the service name in the service field.

I tested on Windows, and found that you can't leave the host field in PgAdmin-III blank on Windows. PgAdmin-III seems to override any host specified in the service file with what's specified in the connection dialog. So you should not include a host key in the service file. (I'll report a bug).

Make "hide file extensions for known file types" is turned off in Windows, so you don't accidentally call it .pg_service.conf.txt instead. If you're unsure whether it's named right or not, check the "Type" column in Windows Explorer in list view; it will read "Text Document" if it's incorrectly named .pg_service.conf.txt, and CONF File if it's correctly named .pg_service.conf. If you have problems renaming it, turn off "hide file extensions for known file types", or use a sensible text editor like notepad++ that'll let you create files named however you like.

Note the leading period (dot) in the filename. Yes, that's different to pgpass.conf, and yes, that's annoying, bordering on a bug.

7
  • Thanks a lot for this! I have now spent 45 minutes trying this on my Windows 7 computer. The first thing is that I cannot leave the "host" field empty. It will disable the "OK" button in that dialog. I attempted to utilize "-" as the host, which got me to the second problem. I do not know where to place the pg_service.conf file. I attempted to do trial and error and placed it in all sub directories related to PgAdmin III and also in C:\Users\pc\AppData\Roaming\postgresql. I always got the error: "Definition of service "myherokudb" not found. – David Mar 16 '14 at 14:46
  • 1
    I also attempted to read about pg_service.conf, but I seem to only find documentation referring to it in a server-side context. – David Mar 16 '14 at 14:51
  • I don't know where you got "server-side context" given that the documentation I linked to refers to the client. Unless you assume "unix/linux" means "server", which it doesn't. Since you didn't originally bother to mention what OS you were on I couldn't be more specific. I've now updated the answer with Windows-specific information (and frankly, it's fair enough that you were confused). Also, on Windows PgAdmin-III seems to override the host specified in the service file with the one specified in the UI, so leave host in the service file blank. – Craig Ringer Mar 17 '14 at 1:30
  • Thank you so much for your help! Your solution just works! It was the leading dot in the file name that confused me, I thought it was a Linux thing. Thank you again! – David Mar 17 '14 at 13:01
  • 2
    Actually, this solution does not work after all. It took me a long time to come to that conclusion, thinking that it was caused my network instability etc., but the connection does indeed still time out. I am confident that I have followed your description. It could be a case, were heroku has put something in place to avoid having thousands of connections to their free test servers hosting hundreds of free databases. – David Mar 28 '14 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.