Fix PostgreSQL Restore Stuck at 47%

Common Symptoms:
• pg_restore process hanging indefinitely at 47%
• No progress for hours during database restore
• High CPU usage but no actual progress
• pg_restore appears frozen or unresponsive

What This Error Means

When pg_restore gets stuck at a specific percentage (commonly 47%), it usually indicates one of these issues:

Lock conflicts with other database sessions
Long-running transactions blocking the restore
Foreign key constraint violations during data restoration
Index rebuilding taking excessive time
Insufficient shared memory or work_mem settings

Immediate Fix

Step 1: Identify Active Sessions and Locks

First, check what's blocking the restore process:

-- Connect to PostgreSQL as superuser
psql -U postgres -d your_database

-- Check for blocking sessions
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
ORDER BY duration DESC;
        

Step 2: Check for Lock Conflicts

Identify specific locks that might be causing the hang:

-- Check for locks
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
        

Step 3: Kill Blocking Processes

If you found blocking processes, terminate them carefully:

-- First try gentle termination (replace PID with actual process ID)
SELECT pg_cancel_backend(PID);

-- If that doesn't work, force termination
SELECT pg_terminate_backend(PID);

-- Example:
SELECT pg_terminate_backend(12345);
        

Step 4: Resume or Restart the Restore

After clearing blocks, you have several options:

# Option 1: Use parallel restore for better performance
pg_restore -h localhost -U postgres -d target_db -j 4 --verbose backup.dump

# Option 2: Restore without triggers and constraints first
pg_restore -h localhost -U postgres -d target_db --disable-triggers --no-owner --no-privileges backup.dump

# Option 3: Restore data only (skip schema)
pg_restore -h localhost -U postgres -d target_db --data-only backup.dump
        

Root Cause

1. Lock Conflicts

The most common cause is lock conflicts. These occur when:

Other applications are actively using the database during restore
Long-running transactions haven't been committed
Autovacuum processes are holding locks

2. Foreign Key Constraints

Foreign key validation during restore can cause significant delays:

-- Check foreign key constraints
SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint
WHERE contype = 'f'
AND conrelid = 'your_table_name'::regclass;
        

3. Insufficient Memory Configuration

Check and adjust PostgreSQL memory settings:

-- Check current settings
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;

-- Recommended adjustments for restore (add to postgresql.conf):
shared_buffers = 256MB
work_mem = 50MB
maintenance_work_mem = 1GB
checkpoint_segments = 32
wal_buffers = 16MB
        

How to Prevent This

1. Use Proper Restore Strategy

# Best practice restore sequence:
# 1. Create database
createdb target_database

# 2. Restore schema only first
pg_restore -h localhost -U postgres -d target_database --schema-only backup.dump

# 3. Disable triggers and constraints
psql -d target_database -c "SET session_replication_role = replica;"

# 4. Restore data with parallel jobs
pg_restore -h localhost -U postgres -d target_database --data-only -j 4 backup.dump

# 5. Re-enable triggers and constraints
psql -d target_database -c "SET session_replication_role = DEFAULT;"
            

2. Monitor Long-Running Queries

Set up monitoring to catch long-running operations:

-- Add to postgresql.conf
log_min_duration_statement = 30000  # Log queries longer than 30 seconds
log_lock_waits = on                 # Log lock waits
deadlock_timeout = 1s               # Check for deadlocks every second
            

3. Optimize for Large Restores

# Temporarily adjust settings for large restores
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET checkpoint_segments = 32;
SELECT pg_reload_conf();

# Don't forget to reset after restore!
            

Warning: Always ensure you have exclusive access to the target database during restore. Close all application connections and stop any scheduled jobs that might interfere with the restore process.

Avoid PostgreSQL Restore Issues

Backuro handles PostgreSQL backups and restores with intelligent scheduling and conflict detection. Never get stuck at 47% again with our automated solution.

Get Reliable Backup Solution