How to fix I/O error in PostgreSQL

Check if your PostgreSQL data directory or WAL partition is full:

df -h /var/lib/postgresql/data
df -h /var/lib/postgresql/data/pg_wal

If usage is at or near 100%, this is likely the cause. You must free up disk space before PostgreSQL can resume normal operations. Common actions:
- Remove old backup files or temporary data
- Archive log files to external storage
- Truncate large tables no longer needed

If you cannot free space, consider adding a new disk or expanding the volume via your cloud provider's management console.

Review PostgreSQL server logs to identify the exact I/O error:

tail -f /var/log/postgresql/postgresql.log

Or query the system logs for recent PostgreSQL errors:

grep -i "i/o error\|no space left\|cannot extend" /var/log/postgresql/postgresql.log

Look for patterns like:
- "ERROR: could not extend file" → disk full
- "ERROR: cannot execute" with "no space left on device" → WAL or data directory full
- Connection reset errors → network issue

Test TCP connectivity from your client to the PostgreSQL server:

telnet postgres_host 5432

Or using nc (netcat):

nc -zv postgres_host 5432

For firewall verification:
- Ensure the PostgreSQL port (default 5432) is not blocked
- Check stateful firewalls for connection drop rules (often drop idle connections after 15-30 minutes)
- Verify no VPN or proxy is dropping idle connections

If the connection fails, check your postgresql.conf settings:

grep "^listen_addresses\|^port" /etc/postgresql/*/main/postgresql.conf

Configure TCP keepalive on both client and server to prevent firewalls from dropping idle connections.

On the PostgreSQL server, edit postgresql.conf:

tcp_keepalives_idle = 300      # seconds before first keepalive probe
tcp_keepalives_interval = 30   # interval between probes
tcp_keepalives_count = 5       # number of probes before drop

Then reload the configuration:

postgresql-ctl reload
# or
sudo systemctl reload postgresql

For JDBC connections in Java applications, add socket timeout and TCP keepalive parameters to your connection string:

jdbc:postgresql://localhost:5432/database?socketTimeout=30&tcpKeepAlives=true

If queries exceed 30 seconds, increase the socket timeout to prevent premature disconnection.

In JDBC (Java), add to connection properties:

jdbc:postgresql://host:5432/db?socketTimeout=60000

The value is in milliseconds. For very long operations (analytical queries), use higher values:

socketTimeout=300000  # 5 minutes

For other client libraries:

Python (psycopg2):

import psycopg2
conn = psycopg2.connect(
    host="localhost",
    connect_timeout=10,
    options="-c statement_timeout=300000"
)

Node.js (pg):

const client = new Client({
  connectionTimeoutMillis: 10000,
  query_timeout: 300000,
});

If you suspect database corruption, run maintenance operations:

sudo -u postgres vacuumdb -d your_database -f

For a more thorough check:

sudo -u postgres vacuumdb -d your_database -fz
sudo -u postgres analyzedb -d your_database

Caution: VACUUM FULL is resource-intensive and locks tables. Run during maintenance windows.

If you experience persistent I/O errors even after these steps, consider:
- Backing up your database
- Checking the PostgreSQL cluster with pg_check_cluster or initdb --check
- Moving the database to different hardware if a hardware fault is suspected

If using Java applications, ensure you're running the latest PostgreSQL JDBC driver, which contains bug fixes for I/O error handling.

In your pom.xml (Maven):

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.0</version>
</dependency>

Or in build.gradle (Gradle):

dependencies {
    implementation 'org.postgresql:postgresql:42.7.0'
}

The latest version is available at https://jdbc.postgresql.org/. Older drivers may have issues with connection timeouts or large query handling.