Spaces:
Running
Running
File size: 5,190 Bytes
3f2d4aa | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 | # 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:
```bash
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.
|