PG> When creating an index on a table containing approx. 100M records PG> the psql client never returns when the index is eventually created PG> on the server. This happens both in a psql client (9.6.6) on mac PG> osx and using the libpq client via the ruby pg gem when using PG> active records migrations.
PG> When running the command directly op the postgres database server PG> using the psql client it works as expected.
PG> The logs from the server: PG> 2018-02-16 16:11:05 CET:82.161.205.254(59584):REDACTED@REDACTED_DB:[23141]: PG> LOG: duration: 999618.165 ms PG> 2018-02-16 16:11:05 CET:82.161.205.254(59584):REDACTED@REDACTED_DB:[23141]: PG> LOG: could not receive data from client: Connection reset by peer
This looks like a network issue; most likely you have a NAT or stateful firewall in between the client and server which is dropping idle connections, with a timeout set shorter than your keepalive interval on either client or server.
The server gets "connection reset" because it tried to send the result of the statement, plus the "ready for query" message, and the NAT/firewall sent back a reset because it no longer recognized the connection as valid. The psql client itself continues to wait for input (and will do so until the keepalive timeout is reached if keepalive is enabled, otherwise indefinitely) because the NAT/firewall doesn't typically send any proactive reset messages.
