Database Repair

​

Overview

• Analyzing maximum referenced XIDs in transactions table
• Advancing PostgreSQL’s XID counter to stay ahead of referenced XIDs
• Using safe batch processing to avoid performance impact

​

Usage

permify repair datastore [flags]

​

Required Flags

​

Optional Flags

​

Examples

​

Basic Usage

permify repair datastore \
  --database-uri "postgres://user:pass@localhost:5432/permify"

​

Dry Run

permify repair datastore \
  --database-uri "postgres://user:pass@localhost:5432/permify" \
  --dry-run

​

Custom Batch Size

permify repair datastore \
  --database-uri "postgres://user:pass@localhost:5432/permify" \
  --batch-size 500

​

How It Works

1. Current XID Check: Gets the current PostgreSQL transaction ID using pg_current_xact_id()
2. Reference Analysis: Finds the maximum transaction ID referenced in the transactions table
3. Gap Calculation: Calculates the difference and adds a safety buffer
4. Batch Processing: Advances the XID counter in configurable batches to minimize performance impact

​

When to Use

• After database migrations
• When PostgreSQL warns about XID wraparound
• As a preventive measure in high-transaction environments
• When encountering XID-related errors

​

Safety Features

• Dry Run Mode: Test changes before applying them
• Batch Processing: Avoids overwhelming the database
• Retry Logic: Handles temporary failures gracefully
• Verbose Logging: Provides detailed progress information
• Data Preservation: Never modifies existing application data

​

Output Example

INFO Starting PostgreSQL XID counter repair database_uri=postgres://*** batch_size=1000 dry_run=false max_retries=3
INFO Starting PostgreSQL transaction ID counter repair dry_run=false batch_size=1000
INFO Current PostgreSQL transaction ID current_xid=99902
INFO Maximum referenced transaction ID max_referenced_xid=125000
INFO Advancing transaction ID counter by delta counter_delta=26098
INFO Advanced XID counter batch batch_size=1000 remaining=25098
INFO Advanced XID counter batch batch_size=1000 remaining=24098
...
INFO Transaction ID counter advancement completed total_advanced=26098
INFO Repair completed successfully duration=2.3s created_tx_id_fixed=26098 errors=0
INFO XID counter repair completed successfully! Advanced XID counter to prevent wraparound issues.

​

Troubleshooting

​

Common Issues

failed to create PostgreSQL instance: connection failed

• Verify database URI is correct
• Ensure PostgreSQL is running and accessible

failed to advance XID counter: permission denied

• Ensure database user has sufficient privileges
• User needs ability to execute pg_current_xact_id() and transaction operations

​

Best Practices

• Always run with --dry-run first in production
• Use appropriate --batch-size for your environment
• Monitor PostgreSQL logs during execution
• Schedule regular maintenance to prevent XID buildup
• Keep database backups before running repair operations