How to fix Cannot connect now in PostgreSQL

On a normal restart, the server typically accepts connections within a few seconds. For crash recovery with large transaction logs, this can take minutes depending on I/O speed and database size. Monitor PostgreSQL logs to confirm recovery is finished:

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

Look for the message "database system is ready to accept connections" which indicates the server is ready.

Configure your application or connection pool to retry connections with increasing delays rather than failing immediately. Most modern drivers support this:

Node.js with pg:

const client = new Client({ database: 'mydb' });
await client.connect().catch(err => {
  if (err.code === '57P03') {
    console.log('Server starting, retrying...');
    setTimeout(() => client.connect(), 2000);
  }
});

Python with psycopg2:

import time
import psycopg2

max_retries = 10
for i in range(max_retries):
  try:
    conn = psycopg2.connect("dbname=mydb user=postgres")
    break
  except psycopg2.OperationalError as e:
    if '57P03' in str(e):
      wait_time = 2 ** i  # Exponential backoff
      time.sleep(wait_time)

On replica servers in replication setups, enable hot standby to allow read-only connections during recovery:

sudo -u postgres psql -c "ALTER SYSTEM SET hot_standby = on;"
sudo systemctl restart postgresql

Verify the setting:

sudo -u postgres psql -c "SHOW hot_standby;"

This allows applications to connect with application_name parameters for read-only queries while the replica recovers.

Use pg_isready utility to verify server state without consuming a connection slot:

pg_isready -h localhost -p 5432 -U postgres

Output codes:
- 0: Server is accepting connections
- 1: Server is rejecting connections
- 2: Cannot establish connection
- 3: No attempt (invalid arguments)

In Kubernetes or container orchestration, use this in startup probes:

livenessProbe:
  exec:
    command: ["pg_isready", "-h", "localhost"]
  initialDelaySeconds: 30
  periodSeconds: 5

If crash recovery takes an unusually long time, optimize these settings in postgresql.conf:

sudo -u postgres psql -c "ALTER SYSTEM SET checkpoint_completion_target = 0.9;"
sudo -u postgres psql -c "ALTER SYSTEM SET max_wal_size = 4GB;"
sudo systemctl restart postgresql

These settings:
- checkpoint_completion_target: Increases time between checkpoints, reducing recovery time
- max_wal_size: Allows more WAL accumulation before checkpoint, reducing checkpoint frequency

Restart PostgreSQL to apply changes. Monitor recovery progress in logs.