docs/database/POSTGRES_LOGGING_GUIDE.md

PostgreSQL Connection Logging Enhancement

Changes Made

Enhanced PostgreSQL connection logging in app/sql.py to provide detailed diagnostic information during application startup and connection attempts.

New Logging Features

1. Connection Configuration Logging

When the application starts, it now logs:

[POSTGRES] Connection String: postgresql+asyncpg://trans_owner:***@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
[POSTGRES] Host: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[POSTGRES] Port: 5432
[POSTGRES] Database: cuatrolabs
[POSTGRES] SSL Mode: require

Key Features:

• ✅ Password is automatically masked (***) for security
• ✅ Shows parsed connection details (host, port, database)
• ✅ Displays SSL mode configuration
• ✅ Logs to both logger and console (stdout) for visibility in deployment logs

2. Connection Attempt Logging

During connection attempts, detailed progress is logged:

[POSTGRES] Attempting to connect (max retries: 30)...
[POSTGRES] ✅ Connection successful after 1 attempt(s)

On Failure:

[POSTGRES] ❌ Connection attempt 1/30 failed
[POSTGRES] Error: TimeoutError: Connection timeout
[POSTGRES] Retrying in 0.80s...

3. Final Error Reporting

If all connection attempts fail, comprehensive error information is provided:

[POSTGRES] ❌ FATAL: Failed to connect after 30 attempts
[POSTGRES] Last error: TimeoutError: Connection timeout after 60s
[POSTGRES] Please check:
[POSTGRES]   1. Database host is reachable: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[POSTGRES]   2. Database credentials are correct
[POSTGRES]   3. SSL mode is appropriate: require
[POSTGRES]   4. Firewall allows connections to port 5432

Security Considerations

Password Masking

The logging implementation includes two helper functions to ensure passwords are never exposed:

1. mask_connection_string(uri: str)

   • Parses the connection URI
   • Replaces password with ***
   • Handles various URI formats safely

2. parse_connection_details(uri: str)

   • Extracts host, port, and database name
   • Does not expose credentials
   • Provides structured data for logging

Example Masking

Original URI:

postgresql+asyncpg://trans_owner:BookMyService7@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs

Logged URI:

postgresql+asyncpg://trans_owner:***@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs

Deployment Benefits

1. Easier Troubleshooting

When deployment fails with connection errors, you can now immediately see:

• Which database host is being used
• What port is being attempted
• SSL configuration
• Exact error messages
• Number of retry attempts

2. Configuration Verification

The logs confirm:

• Whether POSTGRES_URI was provided or built from components
• All connection parameters being used
• SSL/TLS settings

3. Error Diagnosis

Detailed error logging helps identify:

• Network connectivity issues
• Authentication problems
• SSL/TLS configuration errors
• Timeout issues
• DNS resolution failures

Usage in Deployment

Viewing Logs

In your deployment platform, you'll now see clear connection information:

INFO:     Waiting for application startup.
[CONFIG] Using provided POSTGRES_URI
[POSTGRES] Connection String: postgresql+asyncpg://trans_owner:***@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
[POSTGRES] Host: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[POSTGRES] Port: 5432
[POSTGRES] Database: cuatrolabs
[POSTGRES] SSL Mode: require
[POSTGRES] Attempting to connect (max retries: 30)...
[POSTGRES] ✅ Connection successful after 1 attempt(s)
INFO:     Application startup complete.

Debugging Connection Failures

If you see repeated connection failures:

1. Check the logged host - Ensure it matches your database
2. Verify SSL mode - Some databases require specific SSL settings
3. Review error messages - Look for authentication, timeout, or network errors
4. Check retry count - If it's hitting max retries, there's a persistent issue

Testing

Run the diagnostic script to test logging locally:

source venv/bin/activate
python diagnose_postgres.py

This will show you exactly what will be logged in your deployment environment.

Configuration Reference

The logging respects these environment variables:

Variable	Purpose	Default
POSTGRES_URI	Full connection string	Built from components
POSTGRES_CONNECT_MAX_RETRIES	Max connection attempts	30
POSTGRES_CONNECT_INITIAL_DELAY_MS	Initial retry delay	800ms
POSTGRES_CONNECT_BACKOFF_MULTIPLIER	Delay increase factor	1.8
POSTGRES_SSL_MODE	SSL/TLS mode	require

Next Steps

1. Deploy the changes to your environment
2. Monitor startup logs to verify connection details
3. Add POSTGRES_URI to your deployment environment variables
4. Verify successful connection in logs

The enhanced logging will make it much easier to diagnose and fix any connection issues in your deployment.