Postgresql – .psqlrc – setting keepalives_idle connection parameter

postgresqlpsql

Is it possible to set keepalives_idle parameter in .psqlrc so that every connection I open uses it?

I've finally found this option and it's a remedy for my connection dropping problems.

I would really like to set it globally for all sessions I open with psql.

Best Answer

With TCP Keepalives, it doesn't matter who sends it and who receives it. It just matters that it happened so the route isn't lost by a NAT device or closed at either end. I don't know why you'd ever want to set this on the client, as libpq offers, both methods accomplish the same thing.

How to set keepalive transmissions from the server

This only sets it for your session

To set the tcp_keepalives_idle from the server to the client, use

SET tcp_keepalives_idle = 42;

You can put anything you want in your ~/.psqlrc and it gets executed by psql (except under -c and -x). So append this command to the end if you want it to occur for ever psql session.

echo "SET tcp_keepalives_idle = 42;" >> ~/.psqlrc

How do set keepalive transmissions from the client

According to this mailing list post, you can set it on the client by typing

psql "host=127.0.0.1 dbname=myDB keepalives=1"

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:

#!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

Note that you can't simply do echo "sql" >$psql_pipe since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.

Postgresql – How to execute a query from psql without waiting for the result

Since you state that you are open to other solutions I might suggest looking at terminal multiplexers such as screen or tmux. In my opinion tmux is a better choice due to its unique name (easier to get relevant hits in search engines).

Essentially this kind of software allows you to detach from a shell and later resume the session.