Database Errors

Troubleshoot PostgreSQL and database-related issues in OEC.sh. This guide covers connection problems, performance issues, migrations, and data recovery.

Connection Refused

Symptom

Odoo cannot connect to PostgreSQL with "connection refused" or "could not connect to server" errors.

Common Causes and Solutions

PostgreSQL Container Not Running

Symptom: Database container is stopped or crashed.

Solution:

1. Check container status: Environment → Overview (look for database status)
2. Restart the environment: Environment → ⋮ menu → Restart
3. If issue persists, check server resources

Incorrect Connection Parameters

Symptom: "password authentication failed" or "role does not exist" error.

Solution:

1. Verify database credentials in Environment → Settings → Environment Variables
2. Ensure DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, and DB_NAME are correct
3. Check that the database user has proper permissions

Network Issues

Symptom: Connection times out or intermittently fails.

Solution:

1. Check server network status
2. Verify firewall rules allow internal communication
3. For cross-server setups, ensure proper network connectivity

Performance Issues

Symptom

Database queries are slow, Odoo is unresponsive, or you see "lock wait timeout" errors.

Common Causes and Solutions

High Memory Usage

Symptom: PostgreSQL consuming excessive memory, causing slowdowns.

Solution:

1. Check database memory usage: Server → Metrics
2. Enable PgBouncer connection pooling (Pro/Agency plans)
3. Increase RAM allocation for the environment

Missing Indexes

Symptom: Specific queries are very slow.

Solution:

1. Access terminal: Environment → Terminal
2. Analyze slow queries using EXPLAIN ANALYZE
3. Add indexes for frequently queried columns

-- Example: Add index for partner name searches
CREATE INDEX idx_res_partner_name ON res_partner(name);

Long-Running Transactions

Symptom: "lock wait timeout exceeded" or transactions stuck.

Solution:

1. Identify blocked transactions via terminal
2. Kill long-running queries if necessary
3. Review application code for transaction issues

-- Find blocked queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
 
-- Terminate a stuck query (use with caution)
SELECT pg_terminate_backend(pid);

Migration Failures

Symptom

Odoo module upgrades fail during database migration.

Common Causes and Solutions

Schema Conflicts

Symptom: "relation already exists" or "column already exists" error.

Solution:

1. Check if a previous migration partially completed
2. Review the migration script for issues
3. Manually fix the schema if needed (use terminal access)

Data Type Mismatch

Symptom: "cannot cast" or "invalid input syntax" error.

Solution:

1. Identify the problematic column/data
2. Clean or transform data before migration
3. Update migration script to handle edge cases

-- Check table structure
\d table_name
 
-- Check data causing issues
SELECT * FROM table_name WHERE column_name IS NOT NULL LIMIT 10;

Odoo Version Upgrade Issues

Symptom: Major version upgrade fails (e.g., 16.0 to 17.0).

Solution:

1. Use the Clone with Sanitization feature to create a test copy
2. Run the upgrade on the clone first
3. Review and fix any module compatibility issues
4. Upgrade the production environment after successful testing

Database Corruption

Symptom

Data appears corrupted, queries return unexpected results, or PostgreSQL reports corruption errors.

Common Causes and Solutions

Disk Full During Write

Symptom: Corruption after disk space ran out.

Solution:

1. Free up disk space immediately
2. Stop all services
3. Run PostgreSQL recovery or restore from backup

Hardware Failure

Symptom: Sudden corruption without obvious cause.

Solution:

1. Restore from the most recent backup
2. Consider migrating to a different server
3. Contact support for assistance

Recovery Steps

-- Check for corruption
SELECT pg_catalog.pg_database_size(datname) FROM pg_database;

pg_resetwal -f /var/lib/postgresql/data  # Last resort, may lose data

Read Replica Issues

Available on Pro and Agency plans for Odoo 18.0+

Replica Lag

Symptom: Read replica shows outdated data.

Solution:

1. Check replica status: Environment → Database → Replica Health
2. Monitor lag metrics (bytes and seconds behind primary)
3. If lag persists, consider rebuilding the replica

Replica Offline

Symptom: Replica shows "offline" or "error" status.

Solution:

1. Check the replica error message in the dashboard
2. Verify network connectivity between primary and replica
3. Rebuild replica if needed: Environment → Database → Rebuild Replica

Prevention Tips

1. Enable Automated Backups - Configure daily backups to multiple storage providers
2. Monitor Disk Usage - Set up alerts before running out of space
3. Use Connection Pooling - Enable PgBouncer for better resource management
4. Test Upgrades - Always test major changes on a clone first
5. Review Queries - Optimize slow queries before they cause problems

Still Having Issues?

If the above solutions don't resolve your problem:

1. Collect Information:

   • Environment ID
   • Error messages from logs
   • Recent changes made
   • Database version and size

2. Contact Support: Email support@oec.sh