Skip to content

TimescaleDB Database Restore Guide

Problem Description

After restoring a PostgreSQL database backup, inserting data into the location_reports hypertable fails with the error:

ERROR: invalid INSERT on the root table of hypertable "_hyper_24_6674_chunk"
HINT: Make sure the TimescaleDB extension has been preloaded.

This error occurs even though:

  • TimescaleDB extension is installed and loaded
  • The extension appears in \dx
  • shared_preload_libraries includes timescaledb

Root Cause

The issue occurs when:

  1. A database backup is created with one version of TimescaleDB (e.g., 2.18.2)
  2. The backup is restored to a database running a different version (e.g., 2.22.1)
  3. The backup contains internal TimescaleDB metadata that is incompatible with the newer version
  4. The hypertable's internal routing triggers (ts_insert_blocker) become corrupted
  5. Inserts to the hypertable fail because the routing mechanism can't determine which chunk to use

Technical Details

  • Corrupted Metadata: The backup contains _timescaledb_catalog tables with version-specific metadata
  • Incompatible Chunks: Hypertable chunks (_hyper_*_*_chunk) have metadata that doesn't match the installed version
  • Broken Triggers: The ts_insert_blocker trigger prevents direct inserts to the root table, but the trigger logic is corrupted

Solutions

Use the restore_data_only.sh script to restore the database while rebuilding the hypertable structure with clean metadata.

When to use: When your backup contains corrupted TimescaleDB metadata and you need to preserve all data.

Steps:

# Stop services that use the database
docker compose stop dev tracker-fetcher-worker-dev

# Run the restore script
./scripts/restore_data_only.sh ~/path/to/backup.dump.gz

# If successful, restart services
docker compose up -d dev tracker-fetcher-worker-dev

What it does:

  1. Exports current location_reports data to CSV
  2. Filters the backup to exclude corrupted TimescaleDB structures
  3. Restores all other tables and data
  4. Recreates location_reports as a fresh hypertable with clean metadata
  5. Imports the location reports data
  6. Tests that inserts work correctly

Script location: scripts/restore_data_only.sh

Solution 2: Proper Restore with Pre/Post Functions

Use the restore_database_properly.sh script if you have a backup from the same TimescaleDB version.

When to use: When restoring a backup from the same TimescaleDB version, or when you want to follow the official TimescaleDB restore procedure.

Steps:

# Stop services
docker compose stop dev tracker-fetcher-worker-dev

# Run the restore script
./scripts/restore_database_properly.sh ~/path/to/backup.dump.gz

# Restart services
docker compose up -d dev tracker-fetcher-worker-dev

What it does:

  1. Runs timescaledb_pre_restore() to prepare the database
  2. Restores the complete backup
  3. Runs timescaledb_post_restore() to rebuild hypertable metadata
  4. Updates the catalog version if needed

Script location: scripts/restore_database_properly.sh

Note: This may not work if the backup has corrupted metadata. Use Solution 1 if this fails.

Prevention

To avoid this issue in future backups:

Use TimescaleDB-Aware Backup Procedure

When creating backups:

# Before backup - run pre-restore
psql -d tracker -c "SELECT timescaledb_pre_restore();"

# Create backup
pg_dump -Fc tracker > backup.dump

# After backup - run post-restore on the original database
psql -d tracker -c "SELECT timescaledb_post_restore();"

Document TimescaleDB Version in Backups

Add the TimescaleDB version to your backup filename:

VERSION=$(psql -d tracker -t -c "SELECT extversion FROM pg_extension WHERE extname='timescaledb';")
pg_dump -Fc tracker > "backup_timescaledb_${VERSION}_$(date +%Y%m%d).dump"

Test Restores Regularly

Always test restoring your backups in a test environment to catch issues early.

Keep TimescaleDB Versions Consistent

Try to keep the same TimescaleDB version between source and target databases. If upgrading:

  1. Upgrade TimescaleDB on the source database first
  2. Test the upgrade thoroughly
  3. Create a new backup
  4. Then restore to the target with the same version

Verification

After restoring, verify the fix worked:

psql -U tracker -h 192.168.100.1 -p 5432 -d tracker <<EOF
-- Check hypertable configuration
SELECT * FROM timescaledb_information.hypertables
WHERE hypertable_name = 'location_reports';

-- Check TimescaleDB version
SELECT value FROM _timescaledb_catalog.metadata
WHERE key = 'timescaledb_version';

-- Test insert
INSERT INTO location_reports
(hashed_adv_key, timestamp, location, confidence, horizontal_accuracy)
VALUES ('test', NOW(), ST_GeomFromText('POINT(0 0)', 4326), 3, 100);

-- Clean up test
DELETE FROM location_reports WHERE hashed_adv_key = 'test';
EOF

Troubleshooting

Error: "catalog version mismatch"

This confirms the root cause. Use the data-only restore script (Solution 1).

Error: "table location_reports already exists"

The filtered backup still contains location_reports definitions. The script handles this by dropping with CASCADE.

Insert still fails after restore

  1. Check that TimescaleDB is loaded: SHOW shared_preload_libraries;
  2. Verify the hypertable was created: SELECT * FROM timescaledb_information.hypertables;
  3. Check for error messages in the database logs
  4. Try restarting the database container: docker compose restart db

References

Script Maintenance

Both restore scripts are located in the scripts/ directory:

  • scripts/restore_data_only.sh - Recommended for corrupted metadata
  • scripts/restore_database_properly.sh - For same-version restores

These scripts include:

  • Error checking with set -e and explicit exit codes
  • Proper cleanup with trap
  • Detailed progress output
  • Verification tests

Issue History

Date: 2025-10-29

Original Error:

ERROR: invalid INSERT on the root table of hypertable "_hyper_24_6674_chunk"
HINT: Make sure the TimescaleDB extension has been preloaded.

Root Cause: Database backup contained TimescaleDB 2.18.2 metadata, restored to TimescaleDB 2.22.1

Resolution: Created data-only restore script that rebuilds hypertable with clean metadata while preserving all data (1,406,465 location reports)

Result: ✓ All inserts working correctly after restore