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.
The "Cannot assign requested address" part in the error message comes from the kernel TCP stack. When encountered intermittently, this typically means that the space of available sockets is exhausted because of too much sockets in wait state (TIME_WAIT
, or less probably FIN_WAIT_1
or FIN_WAIT_2
)
The range of socket ports can be output by cat /proc/sys/net/ipv4/ip_local_port_range
. The default value on a stock Linux kernel is generally 32768 61000
.
You may check the result of netstat -ton|grep WAIT
on the client(s) and on the pgBouncer's host when the system is busy. The -o
flag will show the timeout
counters related to wait states.
If the total number of TCP sockets is close to 61000-32768=28232
then exhaustion of this range is likely your problem. Since a closed socket spends 60 seconds in TIME_WAIT
state in normal condition, if a client host connects more than 28232 times in one minute, new connections will fail with the mentioned error until ports are freed.
As a first workaround, the TCP ports range may be extended:
# echo "1025 65535" >/proc/sys/net/ipv4/ip_local_port_range
If it's not satisfactory, check the tcp_tw_recycle
and tcp_tw_reuse
flags, also tunable through /proc/sys/net/ipv4
and sysctl
.
They're defined as (from man tcp
):
tcp_tw_recycle (Boolean; default: disabled; since Linux 2.4)
Enable fast recycling of TIME_WAIT sockets. Enabling this
option is not recommended since this causes problems when work‐
ing with NAT (Network Address Translation).
tcp_tw_reuse (Boolean; default: disabled; since Linux 2.4.19/2.6)
Allow to reuse TIME_WAIT sockets for new connections when it is
safe from protocol viewpoint. It should not be changed without
advice/request of technical experts.
Personally I had success with tcp_tw_recycle
when faced with this problem with a MySQL client app, but don't take this as a recommendation, my understanding of TCP being superficial at best.
Best Answer
OK simple solution. Use: