When working with PostgreSQL's command-line interface, you might encounter situations where psql hangs indefinitely when trying to connect to an invalid host. The default behavior can be particularly frustrating during automation scripts or when testing network configurations.

PGPASSWORD=mypass psql -h invalid.host.com -U myuser -c "select 1" -d mydb
# This command will hang for a long time before failing

While psql doesn't have a direct connection timeout parameter, PostgreSQL provides several related options through environment variables and connection parameters:

# Set connection timeout in seconds (via PGOPTIONS)
PGOPTIONS="-c connect_timeout=3" PGPASSWORD=mypass psql -h 10.0.0.144 -U myuser -d mydb

# Alternative using connection string
psql "host=10.0.0.144 user=myuser dbname=mydb connect_timeout=3 password=mypass"

When the native PostgreSQL options aren't sufficient, consider these system-level approaches:

# Linux/MacOS: Using timeout command
timeout 3 psql -h 10.0.0.144 -U myuser -d mydb

# Windows alternative using PowerShell
Start-Process -NoNewWindow -Wait -Timeout 3 psql -ArgumentList "-h 10.0.0.144 -U myuser -d mydb"

For frequent use, create a reusable bash script:

#!/bin/bash
TIMEOUT=3
PGHOST=$1
PGUSER=$2
PGDB=$3
PGPASSWORD=$4

PGOPTIONS="-c connect_timeout=$TIMEOUT" \
psql -h "$PGHOST" -U "$PGUSER" -d "$PGDB" << EOF
select 1;
EOF

EXIT_CODE=$?
if [ $EXIT_CODE -eq 2 ]; then
  echo "Connection failed (timeout after ${TIMEOUT}s)"
  exit 1
fi
exit $EXIT_CODE

If you're still experiencing delays, consider these factors:

• DNS resolution time (try using IP addresses instead of hostnames)
• Network firewall configurations
• PostgreSQL server's postgresql.conf settings
• TCP/IP stack settings on your local machine

For production environments, implement proper connection pooling and health checks rather than relying solely on timeouts.

When working with PostgreSQL's command-line interface psql, you might encounter situations where the client hangs indefinitely when trying to connect to an invalid host. The default behavior lacks proper timeout controls, which can be problematic in automated scripts or when troubleshooting connectivity issues.

While psql doesn't have a direct --timeout parameter, PostgreSQL provides several connection-related options that can help:

PGPASSWORD=passwordhere psql \
  -h 10.0.0.144 \
  -U myuser \
  -d mydatabase \
  -c "select 1" \
  --no-password \
  --set=sslmode=prefer \
  --connect-timeout=3

The most effective solution is to use the --connect-timeout parameter, which specifies the maximum wait for connection establishment in seconds:

--connect-timeout=3  # Sets 3-second timeout

This works with both the libpq connection library (used by psql) and most PostgreSQL client applications.

Here's a robust implementation that includes proper error handling:

#!/bin/bash

TIMEOUT=3
PGHOST="10.0.0.144"
PGUSER="myuser"
PGDATABASE="mydatabase"
PGPASSWORD="passwordhere"

if ! output=$(psql \
  -h "$PGHOST" \
  -U "$PGUSER" \
  -d "$PGDATABASE" \
  -c "SELECT 1" \
  --connect-timeout=$TIMEOUT \
  2>&1); then
  echo "Connection failed (timeout: ${TIMEOUT}s)"
  echo "Error details: $output"
  exit 1
fi

echo "Connection successful: $output"

For environments where you can't modify the psql command directly:

1. Set environment variable: PGCONNECT_TIMEOUT=3
2. Use connection string: postgresql://user@host/db?connect_timeout=3
3. Combine with TCP timeout: /proc/sys/net/ipv4/tcp_syn_retries

To verify your timeout setting works, try connecting to an invalid IP:

psql -h 192.0.2.1 --connect-timeout=3 -c "select 1"

This should fail after exactly 3 seconds if the host is unreachable.