Troubleshooting Guide

Solutions to common problems and error messages.

Extension Installation Issues

Extension Files Not Found

Error: ERROR: could not open extension control file

Cause: Extension files not properly installed

Solution: ```bash

Verify installation

ls -la $(pg_config –sharedir)/extension/pg_ttl_index*

Reinstall if missing

sudo make install

Or via PGXN

pgxn install pg_ttl_index ```

Shared Library Not Loading

Error: ERROR: could not load library

Cause: Extension not in shared_preload_libraries

Solution: ```bash

Edit postgresql.conf

sudo nano /etc/postgresql/16/main/postgresql.conf

Add this line:

shared_preload_libraries = ‘pg_ttl_index’

Restart PostgreSQL (required!)

sudo systemctl restart postgresql ```

Verification: sql SHOW shared_preload_libraries; -- Should include 'pg_ttl_index'

Background Worker Issues

Worker Not Starting

Error: ttl_worker_status() returns no rows

Solution 1: Start the worker manually sql SELECT ttl_start_worker();

Solution 2: Check if enabled ```sql SHOW pg_ttl_index.enabled;

– If false, enable it ALTER SYSTEM SET pg_ttl_index.enabled = true; SELECT pg_reload_conf(); ```

Solution 3: Verify extension loaded sql \dx pg_ttl_index -- Should show version 2.0.0

Worker Crashes Immediately

Check logs: bash sudo tail -f /var/log/postgresql/postgresql-*.log | grep -i "ttl\|error"

Common causes: - Permission issues - Database doesn’t exist - Extension not properly initialized

Worker Stops After PostgreSQL Restart

This is normal behavior. Worker doesn’t auto-start.

Solution: Add to database startup script sql -- Run after every PostgreSQL restart SELECT ttl_start_worker();

Automate with cron (example): ```bash

/etc/cron.d/ttl-worker

@reboot postgres psql -d your_database -c “SELECT ttl_start_worker();” ```

Cleanup Not Working

Data Not Being Deleted

Diagnosis: ```sql – 1. Check worker status SELECT * FROM ttl_worker_status();

– 2. Check TTL configuration SELECT * FROM ttl_summary();

– 3. Check if TTL is active SELECT active FROM ttl_index_table WHERE table_name = ‘your_table’; ```

Solutions:

If worker not running: sql SELECT ttl_start_worker();

If TTL inactive: sql UPDATE ttl_index_table SET active = true WHERE table_name = 'your_table';

If cleanup hasn’t run recently: sql -- Manually trigger SELECT ttl_runner();

Wrong Column Being Used

Error: Cleanup doesn’t work as expected

Diagnosis: sql -- Check which column is configured SELECT table_name, column_name, expire_after_seconds FROM ttl_index_table WHERE table_name = 'your_table';

Solution: Recreate with correct column ```sql – Drop old TTL SELECT ttl_drop_index(‘your_table’, ‘wrong_column’);

– Create with correct column SELECT ttl_create_index(‘your_table’, ‘correct_column’, 3600); ```

Cleanup Runs But Doesn’t Delete

Check for expired data: sql SELECT COUNT(*) FROM your_table WHERE created_at < NOW() - INTERVAL '1 hour'; -- Should match expected deletions

Check timestamp column: sql -- Verify column data type SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'your_table' AND column_name = 'created_at'; -- Must be timestamp, timestamptz, or date

Permission Errors

Permission Denied to Create Extension

Error: ERROR: permission denied to create extension

Solution: Need superuser privileges ```sql – As superuser \c your_database postgres

CREATE EXTENSION pg_ttl_index;

– Or grant superuser temporarily ALTER USER your_user SUPERUSER; – Create extension ALTER USER your_user NOSUPERUSER; ```

Permission Denied on ttl_index_table

Error: ERROR: permission denied for table ttl_index_table

Solution: Grant necessary permissions sql GRANT SELECT, INSERT, UPDATE, DELETE ON ttl_index_table TO your_user; GRANT EXECUTE ON FUNCTION ttl_create_index TO your_user; GRANT EXECUTE ON FUNCTION ttl_drop_index TO your_user; GRANT EXECUTE ON FUNCTION ttl_runner TO your_user;

Configuration Issues

Settings Not Taking Effect

Diagnosis: ```sql – Check current settings SHOW pg_ttl_index.naptime; SHOW pg_ttl_index.enabled;

– Check configuration source SELECT name, setting, source FROM pg_settings WHERE name LIKE ‘pg_ttl_index%’; ```

Solution: Reload configuration ```sql – Reload PostgreSQL config SELECT pg_reload_conf();

– Restart worker to pick up changes SELECT ttl_stop_worker(); SELECT ttl_start_worker(); ```

Can’t Set Configuration Parameter

Error: ERROR: unrecognized configuration parameter

Cause: Extension not in shared_preload_libraries

Solution: ```bash

Edit postgresql.conf

shared_preload_libraries = ‘pg_ttl_index’

Restart PostgreSQL

sudo systemctl restart postgresql ```

Performance Issues

Cleanup Takes Too Long

Diagnosis: sql -- Check deletion volumes SELECT table_name, rows_deleted_last_run, batch_size FROM ttl_summary() ORDER BY rows_deleted_last_run DESC;

Solutions:

Increase batch size: sql UPDATE ttl_index_table SET batch_size = 50000 WHERE table_name = 'high_volume_table';

Verify index exists: ```sql SELECT index_name FROM ttl_index_table WHERE table_name = ‘your_table’;

– Verify index is actually there \di+ idx_ttl_your_table_* ```

Run VACUUM: sql VACUUM ANALYZE your_table;

High CPU Usage

Diagnosis: ```sql – Check cleanup frequency SHOW pg_ttl_index.naptime;

– Check recent activity SELECT * FROM pg_stat_activity WHERE application_name LIKE ‘TTL Worker%’; ```

Solutions:

Reduce cleanup frequency: sql ALTER SYSTEM SET pg_ttl_index.naptime = 300; -- 5 minutes SELECT pg_reload_conf();

Smaller batch sizes: sql UPDATE ttl_index_table SET batch_size = 5000 WHERE table_name = 'your_table';

Table Bloat

Diagnosis: ```sql – Check table size SELECT pg_size_pretty(pg_total_relation_size(‘your_table’));

– Check bloat (requires pgstattuple extension) CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple(‘your_table’); ```

Solution: ```sql – More aggressive autovacuum ALTER TABLE your_table SET ( autovacuum_vacuum_scale_factor = 0.05 );

– Manual vacuum (doesn’t lock table) VACUUM ANALYZE your_table;

– Full vacuum (locks table - use with caution!) VACUUM FULL your_table; ```

Error Messages

“Another instance is already running”

Message: NOTICE: TTL runner: Another instance is already running, skipping

This is normal: Advisory lock prevents overlapping runs

Action: No action needed unless it happens frequently

If frequent: - Increase batch size (cleanup finishes faster) - Increase naptime (runs less often)

“Column must be a date/timestamp type”

Error: During ttl_create_index()

Cause: Specified column is not a timestamp type

Solution: ```sql – Check column type SELECT column_name, data_type FROM information_schema.columns WHERE table_name = ‘your_table’ AND column_name = ‘your_column’;

– Must be one of: timestamp, timestamptz, date ```

Fix: Use correct column or change column type ```sql – Change column type ALTER TABLE your_table ALTER COLUMN your_column TYPE TIMESTAMPTZ;

– Then create TTL SELECT ttl_create_index(‘your_table’, ‘your_column’, 3600); ```

“Failed to cleanup table”

Warning: In PostgreSQL logs

Cause: Error during cleanup (e.g., table dropped, column changed)

Diagnosis: ```bash

Check logs for full error

sudo tail -f /var/log/postgresql/postgresql-*.log | grep -A 5 “Failed to cleanup” ```

Solution: Fix the underlying issue or drop TTL ```sql – If table no longer exists SELECT ttl_drop_index(‘old_table’, ‘created_at’);

– If column was renamed SELECT ttl_drop_index(‘table’, ‘old_column’); SELECT ttl_create_index(‘table’, ‘new_column’, 3600); ```

Debugging Tips

Enable Debug Logging

-- Enable detailed logging
ALTER SYSTEM SET log_min_messages = 'debug1';
ALTER SYSTEM SET log_error_verbosity = 'verbose';
SELECT pg_reload_conf();

-- Watch logs
-- tail -f /var/log/postgresql/postgresql-*.log

Manual Testing

-- Test cleanup manually
SELECT ttl_runner();

-- Check what would be deleted (doesn't actually delete)
SELECT COUNT(*)
FROM your_table
WHERE created_at < NOW() - INTERVAL '1 hour';

Check Extension Version

-- Verify extension version
SELECT * FROM pg_available_extensions 
WHERE name = 'pg_ttl_index';

-- Check installed version
\dx pg_ttl_index

Getting Help

If you’re still stuck:

  1. Check logs: /var/log/postgresql/postgresql-*.log
  2. Gather diagnostics: sql -- Run this and share output SELECT * FROM ttl_worker_status(); SELECT * FROM ttl_summary(); SHOW pg_ttl_index.naptime; SHOW pg_ttl_index.enabled; \dx pg_ttl_index
  3. GitHub Issues: Report a bug
  4. Discussions: Ask questions

See Also