Skip to content

TimescaleDB Troubleshooting

This guide covers common issues and solutions for TimescaleDB in the PRS production environment.

Overview

This troubleshooting guide addresses: - Installation and setup issues - Extension and migration problems - Performance problems - Slow queries and resource issues - Storage issues - Disk space and compression problems - Connection problems - Database connectivity issues - Backup and recovery issues - Data protection problems

Common Issues and Solutions

1. Installation and Setup Issues

TimescaleDB Extension Not Available

Symptoms: - Error: "extension 'timescaledb' is not available" - Migration fails with extension errors

Diagnosis:

Bash
1
2
3
4
5
6
7
# Check if TimescaleDB image is being used
docker ps | grep timescale

# Check available extensions
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT name FROM pg_available_extensions WHERE name = 'timescaledb';"

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 1. Verify Docker image
docker images | grep timescale

# 2. Update docker-compose.yml to use TimescaleDB image
# Change: postgres:15-alpine
# To: timescale/timescaledb:latest-pg15

# 3. Restart services
./scripts/deploy-ec2.sh restart

# 4. Re-run initialization
./scripts/deploy-ec2.sh init-db

Migration Fails with SSL Errors

Symptoms: - "ERROR: self-signed certificate" - "SSL connection failed"

Diagnosis:

Bash
1
2
# Check SSL configuration
grep -i ssl prs-production-deployment/ec2-graviton-setup/.env

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# Update .env file SSL settings
DISABLE_SSL=true
POSTGRES_SSL_REQUIRE=false
POSTGRES_SSL_REJECT_UNAUTHORIZED=false

# Restart services
./scripts/deploy-ec2.sh restart

# Re-run initialization
./scripts/deploy-ec2.sh init-db

Hypertables Not Created

Symptoms: - Migration completes but no hypertables exist - TimescaleDB queries fail

Diagnosis:

Bash
1
2
3
4
5
6
7
# Check if hypertables exist
./scripts/deploy-ec2.sh timescaledb-status

# Check migration status
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT name FROM \"SequelizeMeta\" WHERE name LIKE '%timescaledb%';"

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# 1. Check if TimescaleDB extension is enabled
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

# 2. Re-run specific migration
cd /path/to/prs-backend-a
npx sequelize-cli db:migrate --env production

# 3. Manual hypertable creation (if needed)
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT create_hypertable('audit_logs', 'created_at');"

2. Performance Issues

Slow Query Performance

Symptoms: - Queries taking longer than expected - High CPU usage - Application timeouts

Diagnosis:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Check slow queries
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT query, mean_time, calls FROM pg_stat_statements
        WHERE mean_time > 1000 ORDER BY mean_time DESC LIMIT 5;"

# Check active queries
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT pid, now() - query_start as duration, query
        FROM pg_stat_activity WHERE state = 'active';"

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# 1. Run optimization
./scripts/deploy-ec2.sh timescaledb-optimize

# 2. Check for missing time constraints in queries
# Ensure queries include WHERE created_at >= ...

# 3. Verify chunk exclusion is working
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM audit_logs
WHERE created_at >= NOW() - INTERVAL '30 days';

# 4. Add appropriate indexes
CREATE INDEX CONCURRENTLY idx_table_time_column
ON table_name (time_column DESC);

High Memory Usage

Symptoms: - Out of memory errors - Container restarts - System slowdown

Diagnosis:

Bash
1
2
3
4
5
6
7
8
9
# Check memory usage
free -h
docker stats prs-ec2-postgres-timescale --no-stream

# Check PostgreSQL memory settings
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT name, setting, unit FROM pg_settings
        WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');"

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# 1. Adjust memory settings in .env
POSTGRES_SHARED_BUFFERS=128MB
POSTGRES_WORK_MEM=4MB
POSTGRES_MAINTENANCE_WORK_MEM=32MB

# 2. Restart services
./scripts/deploy-ec2.sh restart

# 3. Kill long-running queries if needed
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
        WHERE state = 'active' AND now() - query_start > interval '10 minutes';"

3. Storage Issues

High Disk Usage

Symptoms: - Disk space warnings - "No space left on device" errors - Slow I/O performance

Diagnosis:

Bash
1
2
3
4
5
6
7
8
9
# Check disk usage
df -h
du -sh /var/lib/docker/

# Check database size
./scripts/deploy-ec2.sh timescaledb-maintenance storage

# Check compression status
./scripts/deploy-ec2.sh timescaledb-maintenance status

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 1. Force compression of eligible chunks
./scripts/deploy-ec2.sh timescaledb-maintenance compress

# 2. Clean up old backups
find ./backups -name "*.dump" -mtime +30 -delete
find ./backups -name "*.sql.gz" -mtime +30 -delete

# 3. Clean up Docker resources
docker system prune -f
docker volume prune -f

# 4. Check for uncompressed old chunks
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT chunk_name, range_end, is_compressed
        FROM timescaledb_information.chunks
        WHERE NOT is_compressed AND range_end < NOW() - INTERVAL '7 days';"

Compression Not Working

Symptoms: - Low compression ratios - Storage not decreasing as expected - Compression policies not running

Diagnosis:

Bash
1
2
3
4
5
6
7
8
# Check compression policies
./scripts/deploy-ec2.sh timescaledb-maintenance status

# Check compression jobs
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT * FROM timescaledb_information.jobs
        WHERE proc_name = 'policy_compression';"

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# 1. Re-setup compression policies
./scripts/deploy-ec2.sh timescaledb-compression

# 2. Manual compression
./scripts/deploy-ec2.sh timescaledb-maintenance compress

# 3. Check if tables have compression enabled
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT hypertable_name, compression_enabled
        FROM timescaledb_information.hypertables;"

# 4. Enable compression on specific table
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "ALTER TABLE table_name SET (timescaledb.compress);"

4. Connection Issues

Connection Refused

Symptoms: - "Connection refused" errors - Application cannot connect to database - Database appears down

Diagnosis:

Bash
1
2
3
4
5
6
7
8
# Check container status
docker ps | grep timescale

# Check container logs
docker logs prs-ec2-postgres-timescale

# Check database readiness
docker exec prs-ec2-postgres-timescale pg_isready -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 1. Restart database container
docker restart prs-ec2-postgres-timescale

# 2. Check if container is running
./scripts/deploy-ec2.sh status

# 3. Restart all services
./scripts/deploy-ec2.sh restart

# 4. Check for port conflicts
netstat -tlnp | grep 5432

Too Many Connections

Symptoms: - "too many connections" errors - Connection pool exhausted - Application timeouts

Diagnosis:

Bash
1
2
3
4
5
6
7
8
9
# Check current connections
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT count(*) as connections, state FROM pg_stat_activity GROUP BY state;"

# Check connection limit
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SHOW max_connections;"

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 1. Kill idle connections
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
        WHERE state = 'idle' AND now() - state_change > interval '1 hour';"

# 2. Adjust connection pool settings in application
# Reduce max connections in Sequelize config

# 3. Increase max_connections (if needed)
# In .env: POSTGRES_MAX_CONNECTIONS=50
./scripts/deploy-ec2.sh restart

5. Backup and Recovery Issues

Backup Failures

Symptoms: - Backup scripts fail - Empty or corrupted backup files - Backup process hangs

Diagnosis:

Bash
1
2
3
4
5
6
7
8
# Check backup logs
tail -f /var/log/timescaledb-backup.log

# Test backup manually
./scripts/deploy-ec2.sh timescaledb-backup

# Check backup directory
ls -la ./backups/

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# 1. Check disk space for backups
df -h ./backups/

# 2. Test database connectivity
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    pg_isready -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"

# 3. Manual backup
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    pg_dump -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" -Fc > manual_backup.dump

# 4. Check backup script permissions
chmod +x ./scripts/timescaledb-production-backup.sh

Recovery Failures

Symptoms: - Restore process fails - Data corruption after restore - Missing data after recovery

Diagnosis:

Bash
1
2
3
4
5
6
# Test backup integrity
docker exec prs-ec2-postgres-timescale \
    pg_restore --list /tmp/backup.dump > /dev/null

# Check backup file
file ./backups/backup.dump

Solutions:

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 1. Verify backup integrity before restore
pg_restore --list backup.dump

# 2. Use different restore method
# Try SQL backup instead of binary
gunzip -c backup.sql.gz | docker exec -i prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"

# 3. Restore to test database first
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "postgres" \
    -c "CREATE DATABASE test_restore;"

Diagnostic Commands

System Health Check

Bash
1
2
3
4
5
6
7
8
# Comprehensive health check
./scripts/timescaledb-alerts.sh

# Quick status check
./scripts/deploy-ec2.sh timescaledb-status

# Resource monitoring
./scripts/deploy-ec2.sh monitor

Database Diagnostics

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- Check TimescaleDB version
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';

-- Check hypertables
SELECT * FROM timescaledb_information.hypertables;

-- Check chunks
SELECT count(*) as total_chunks,
       count(*) FILTER (WHERE is_compressed) as compressed_chunks
FROM timescaledb_information.chunks;

-- Check active queries
SELECT pid, now() - query_start as duration, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%';

Performance Diagnostics

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# Check slow queries
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT query, mean_time, calls FROM pg_stat_statements
        ORDER BY mean_time DESC LIMIT 10;"

# Check table sizes
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT schemaname, tablename,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
        FROM pg_tables WHERE schemaname = 'public'
        ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;"

Emergency Procedures

Database Recovery

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# 1. Stop application
./scripts/deploy-ec2.sh stop

# 2. Backup current state (if possible)
./scripts/deploy-ec2.sh timescaledb-backup

# 3. Restore from latest backup
./scripts/restore-latest-backup.sh

# 4. Verify restoration
./scripts/deploy-ec2.sh timescaledb-status

# 5. Start application
./scripts/deploy-ec2.sh start

Performance Emergency

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# 1. Kill long-running queries
docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" prs-ec2-postgres-timescale \
    psql -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" \
    -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
        WHERE state = 'active' AND now() - query_start > interval '5 minutes';"

# 2. Run immediate optimization
./scripts/deploy-ec2.sh timescaledb-optimize

# 3. Check resource usage
./scripts/deploy-ec2.sh monitor

# 4. Restart if necessary
./scripts/deploy-ec2.sh restart

Getting Help

Log Files to Check

  • /var/log/timescaledb-alerts.log - Health monitoring alerts
  • /var/log/timescaledb-backup.log - Backup operation logs
  • /var/log/timescaledb-maintenance.log - Maintenance operation logs
  • docker logs prs-ec2-postgres-timescale - Database container logs

Useful Commands for Support

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# System information
uname -a
docker --version
docker-compose --version

# TimescaleDB information
./scripts/deploy-ec2.sh timescaledb-status
./scripts/timescaledb-alerts.sh
./scripts/deploy-ec2.sh monitor

# Configuration
cat prs-production-deployment/ec2-graviton-setup/.env | grep -v PASSWORD

When in doubt, check the logs, run health checks, and don't hesitate to restart services if needed!