Error: TOAST Table Data Corruption Detected

Issue

While syncing your data, Fivetran encounters corrupted data in one or more TOAST tables. The following error message appears in your sync logs or tasks:

ERROR: unexpected chunk number X for toast value Y in pg_toast_XXXXXX

Environment

Connector: PostgreSQL (all deployment types)

Cause

PostgreSQL uses TOAST (The Oversized-Attribute Storage Technique) to store large column values that exceed the page size limit (typically 8KB). When a table contains large text, binary data, or other oversized values, PostgreSQL automatically creates a separate TOAST table (named pg_toast_XXXXXX) to store these values. Each TOAST table corresponds to one of your actual database tables.

This error indicates that the data in the TOAST table has been corrupted at the database level.

Resolution

Identify the affected tables

Execute the following query on your PostgreSQL database to find the actual tables affected by the corrupted TOAST tables:

SELECT c.relname AS table_name, n.nspname AS schema_name
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.reltoastrelid = 'pg_toast_XXXXXX'::regclass;

Choose a resolution approach

You can follow one of the following approaches to fix the issue:

• Fix the corruption (Recommended)
• Exclude corrupted tables from sync (Temporary)

Fix the corruption

Identify corrupted rows

1. Execute the following query on the affected table to find which rows are corrupted (the query will fail when it encounters a corrupted row):

   SELECT ctid, * FROM your_schema.your_table_name;
   

2. Make a note of the row IDs (ctid) where the query fails.

Choose a recovery method

Select the approach that best fits your situation:

(Recommended) If you have a recent backup, do one of the following:

• Restore the affected table from your backup
• Restore only the specific corrupted rows if you can identify them

If data loss is acceptable, do the following:

• Delete the corrupted rows (replace ctid values with the ones that failed)

  DELETE FROM your_table_name WHERE ctid = '(0,1)';
  

• After deleting corrupted rows, clean up the table

  VACUUM FULL your_table_name;
  

If corruption is extensive, do the following:

• Export non-corrupted data. Execute the following command: pg_dump --data-only --exclude-table=your_table_name.
• Drop and recreate the table.
• Reload the data.

Verify the fix

Execute the following:

SELECT COUNT(*) FROM your_table_name;

The command should complete without errors after fixing.

Exclude corrupted tables from sync

If you cannot immediately fix the corruption, you can temporarily exclude the affected tables from syncing:

1. In your Fivetran dashboard, go to the Schema tab of your connection.
2. Find the tables you identified.
3. Exclude the tables.
4. Click Save & Test.

Prevention

To prevent TOAST corruption in the future, ensure the following:

• Maintain regular database backups
• Monitor disk health and ensure sufficient disk space
• Use proper database shutdown procedures
• Monitor PostgreSQL logs
• Perform timely upgrades to the latest PostgreSQL minor releases