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:
and when connecting from PgAdmin-III enter
myherokudb in the
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:
- 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
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.