Any way to keep connection alive in pgAdmin without setting it on the server?

Question

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.

Answer 1

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.

Answer 2

Just execute following command on database and all done.

ALTER ROLE user_name_here IN DATABASE database_name_here SET tcp_keepalives_idle TO '30';