Home / Database / PostgreSQL Transaction Wraparound Prevention

Database

PostgreSQL Transaction Wraparound Prevention

Prevent PostgreSQL transaction wraparound disasters by understanding freeze limits and managing transaction IDs.

Published: Nov 15, 202511 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

# PostgreSQL Transaction Wraparound Prevention

The most critical PostgreSQL error you can encounter:

bash

ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

Transaction wraparound is a database-ending event if not handled properly. Understanding and preventing it is essential for any PostgreSQL administrator.

Introduction

This article covers troubleshooting steps and solutions for PostgreSQL Transaction Wraparound Prevention. The error typically occurs in production environments and can cause service disruptions if not addressed promptly.

Symptoms

Common error messages include:

bash

ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

sql

-- View current thresholds
SELECT name, setting, unit, context
FROM pg_settings
WHERE name LIKE '%freeze%' OR name = 'autovacuum_freeze_max_age'
ORDER BY name;

sql

-- Check transaction age for all databases
SELECT
    datname AS database,
    age(datfrozenxid) AS current_age,
    current_setting('autovacuum_freeze_max_age')::bigint AS max_age,
    current_setting('autovacuum_freeze_max_age')::bigint - age(datfrozenxid) AS transactions_remaining,
    CASE
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint THEN 'STOPPED'
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.95 THEN 'CRITICAL'
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.8 THEN 'WARNING'
        ELSE 'OK'
    END AS status
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Common Causes

Configuration misconfiguration
Missing or incorrect credentials
Network connectivity issues
Version compatibility problems
Resource exhaustion or limits
Permission or access denied

Step-by-Step Fix

1.Check logs for specific error messages
2.Verify configuration settings
3.Test network connectivity
4.Review recent changes
5.Apply corrective action
6.Verify the fix

Understanding Transaction IDs

PostgreSQL uses 32-bit transaction IDs (XIDs), creating a circular range from 0 to about 4.2 billion. Without management, transactions would eventually "wrap around" and data could become invisible or corrupted.

How PostgreSQL Handles Wraparound

1.Freezing: Old tuples are marked as "frozen" - visible to all transactions
2.Vacuum: The VACUUM process performs freezing and advances the freeze horizon
3.Autovacuum: Automatic process that triggers freezing when needed

Critical Thresholds

sql

-- View current thresholds
SELECT name, setting, unit, context
FROM pg_settings
WHERE name LIKE '%freeze%' OR name = 'autovacuum_freeze_max_age'
ORDER BY name;

Setting	Default	Purpose
`autovacuum_freeze_max_age`	200 million	Maximum age before forced vacuum
`vacuum_freeze_table_age`	150 million	Age at which VACUUM scans whole table
`vacuum_freeze_min_age`	50 million	Minimum age before freezing tuples

Check Current Wraparound Status

Database-Level Status

sql

-- Check transaction age for all databases
SELECT
    datname AS database,
    age(datfrozenxid) AS current_age,
    current_setting('autovacuum_freeze_max_age')::bigint AS max_age,
    current_setting('autovacuum_freeze_max_age')::bigint - age(datfrozenxid) AS transactions_remaining,
    CASE
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint THEN 'STOPPED'
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.95 THEN 'CRITICAL'
        WHEN age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.8 THEN 'WARNING'
        ELSE 'OK'
    END AS status
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Table-Level Status

sql

-- Check tables closest to wraparound
SELECT
    n.nspname AS schema,
    c.relname AS table,
    age(c.relfrozenxid) AS xid_age,
    current_setting('autovacuum_freeze_max_age')::bigint - age(c.relfrozenxid) AS transactions_to_emergency,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
    CASE
        WHEN age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.95 THEN 'CRITICAL'
        WHEN age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.8 THEN 'WARNING'
        WHEN age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.6 THEN 'ELEVATED'
        ELSE 'OK'
    END AS status,
    s.last_vacuum,
    s.last_autovacuum
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
  AND n.nspname NOT LIKE 'pg_%'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

View Running Autovacuum Processes

sql

-- Check if autovacuum is working on freeze
SELECT
    pid,
    datname,
    relid::regclass AS table,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    index_vacuum_count
FROM pg_stat_progress_vacuum
WHERE phase IS NOT NULL;

Immediate Prevention Steps

1. Run Aggressive Vacuum

```sql -- Run vacuum freeze on all tables VACUUM FREEZE VERBOSE ANALYZE;

-- Target specific high-age tables VACUUM FREEZE VERBOSE ANALYZE my_schema.large_table; ```

2. Kill Blocking Transactions

```sql -- Find long-running transactions SELECT pid, usename, datname, now() - xact_start AS duration, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

-- Kill transactions running over 1 hour SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE xact_start < now() - interval '1 hour' AND pid <> pg_backend_pid(); ```

3. Handle Prepared Transactions

```sql -- Find prepared transactions SELECT transaction, gid, prepared, now() - prepared AS age, owner, database FROM pg_prepared_xacts;

-- Commit or rollback old prepared transactions COMMIT PREPARED 'old_transaction_gid'; -- or ROLLBACK PREPARED 'old_transaction_gid'; ```

4. Check Replication Slots

```sql -- Replication slots can prevent freeze advancement SELECT slot_name, slot_type, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_size FROM pg_replication_slots WHERE NOT active;

-- Drop inactive slots SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE NOT active; ```

Emergency Recovery

If the database has already stopped accepting commands:

Step 1: Enter Single-User Mode

```bash # Stop PostgreSQL sudo systemctl stop postgresql

# Start in single-user mode sudo -u postgres postgres --single -D /var/lib/postgresql/16/main mydb

# At the PostgreSQL prompt, run: VACUUM FREEZE VERBOSE ANALYZE;

# Exit with Ctrl+D ```

Step 2: If Single-User Mode Fails

```bash # Check for corruption pg_resetwal -D /var/lib/postgresql/16/main --latest

# WARNING: This resets the transaction log # Only use as last resort pg_resetwal -D /var/lib/postgresql/16/main

# Restart PostgreSQL sudo systemctl start postgresql ```

Step 3: Immediate Vacuum After Recovery

```sql -- After restarting, immediately vacuum VACUUM FREEZE VERBOSE ANALYZE;

-- Verify status SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC; ```

Configure Proper Autovacuum

System-Wide Settings

```conf # postgresql.conf

# Enable autovacuum (must be on) autovacuum = on track_counts = on

# Freeze thresholds autovacuum_freeze_max_age = 200000000 # 200M transactions vacuum_freeze_table_age = 150000000 # Start freezing at 150M vacuum_freeze_min_age = 50000000 # Freeze tuples older than 50M

# Autovacuum tuning for performance autovacuum_vacuum_cost_delay = 2ms autovacuum_vacuum_cost_limit = 200 autovacuum_work_mem = 1GB

# Logging for monitoring log_autovacuum_min_duration = 0 ```

Per-Table Tuning

For high-transaction tables:

```sql -- More aggressive freezing for high-churn tables ALTER TABLE my_busy_table SET ( autovacuum_freeze_max_age = 150000000, autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 1000 );

-- Less aggressive for large, static tables ALTER TABLE archive_table SET ( autovacuum_freeze_max_age = 500000000, autovacuum_vacuum_scale_factor = 0.1 ); ```

Monitor and Alert

Create Monitoring View

```sql CREATE OR REPLACE VIEW wraparound_status AS WITH db_status AS ( SELECT datname, age(datfrozenxid) AS db_age, current_setting('autovacuum_freeze_max_age')::bigint AS max_age FROM pg_database WHERE datname NOT IN ('template0', 'template1') ), table_status AS ( SELECT n.nspname AS schema_name, c.relname AS table_name, age(c.relfrozenxid) AS table_age, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT LIKE 'pg_%' ) SELECT 'DATABASE' AS level, datname AS name, db_age AS xid_age, max_age - db_age AS transactions_remaining, ROUND(db_age::numeric / max_age * 100, 2) AS percent_used, CASE WHEN db_age > max_age THEN 'CRITICAL - STOPPED' WHEN db_age > max_age * 0.95 THEN 'CRITICAL' WHEN db_age > max_age * 0.8 THEN 'WARNING' ELSE 'OK' END AS status FROM db_status UNION ALL SELECT 'TABLE' AS level, schema_name || '.' || table_name AS name, table_age AS xid_age, (SELECT max_age FROM db_status LIMIT 1) - table_age AS transactions_remaining, ROUND(table_age::numeric / (SELECT max_age FROM db_status LIMIT 1) * 100, 2) AS percent_used, CASE WHEN table_age > (SELECT max_age FROM db_status LIMIT 1) * 0.95 THEN 'CRITICAL' WHEN table_age > (SELECT max_age FROM db_status LIMIT 1) * 0.8 THEN 'WARNING' WHEN table_age > (SELECT max_age FROM db_status LIMIT 1) * 0.6 THEN 'ELEVATED' ELSE 'OK' END AS status FROM table_status WHERE table_age > (SELECT max_age FROM db_status LIMIT 1) * 0.5 ORDER BY xid_age DESC;

-- Use it SELECT * FROM wraparound_status WHERE status != 'OK'; ```

Create Alert Function

```sql CREATE OR REPLACE FUNCTION check_wraparound_risk() RETURNS TABLE(alert_level text, details text) LANGUAGE plpgsql AS $$ DECLARE threshold_warning bigint; threshold_critical bigint; max_age bigint; BEGIN max_age := current_setting('autovacuum_freeze_max_age')::bigint; threshold_warning := (max_age * 0.8)::bigint; threshold_critical := (max_age * 0.95)::bigint;

-- Check databases RETURN QUERY SELECT CASE WHEN age(datfrozenxid) > threshold_critical THEN 'CRITICAL' ELSE 'WARNING' END, format('Database %s at %s transactions (%.1f%% of max)', datname, age(datfrozenxid), age(datfrozenxid)::numeric / max_age * 100) FROM pg_database WHERE age(datfrozenxid) > threshold_warning AND datname NOT IN ('template0', 'template1');

-- Check tables RETURN QUERY SELECT CASE WHEN age(c.relfrozenxid) > threshold_critical THEN 'CRITICAL' ELSE 'WARNING' END, format('Table %s.%s at %s transactions (%.1f%% of max)', n.nspname, c.relname, age(c.relfrozenxid), age(c.relfrozenxid)::numeric / max_age * 100) FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT LIKE 'pg_%' AND age(c.relfrozenxid) > threshold_warning;

RETURN; END; $$;

-- Run check SELECT * FROM check_wraparound_risk(); ```

Calculate Time to Wraparound

sql

-- Estimate time until wraparound
WITH transaction_rate AS (
    SELECT
        stat_reset,
        now() - stat_reset AS duration,
        xact_commit + xact_rollback AS total_transactions
    FROM pg_stat_database
    WHERE datname = current_database()
),
current_age AS (
    SELECT age(datfrozenxid) AS current_age
    FROM pg_database
    WHERE datname = current_database()
)
SELECT
    (SELECT current_age FROM current_age) AS current_xid_age,
    current_setting('autovacuum_freeze_max_age')::bigint AS max_age,
    current_setting('autovacuum_freeze_max_age')::bigint - (SELECT current_age FROM current_age) AS remaining_transactions,
    CASE
        WHEN total_transactions > 0 AND (SELECT duration FROM transaction_rate) > interval '0'
        THEN (current_setting('autovacuum_freeze_max_age')::bigint - (SELECT current_age FROM current_age))
             * EXTRACT(EPOCH FROM (SELECT duration FROM transaction_rate))
             / total_transactions / 86400
        ELSE NULL
    END AS estimated_days_until_emergency
FROM transaction_rate;

Transaction Rate Monitoring

```sql -- Create table to track transaction rates CREATE TABLE IF NOT EXISTS transaction_tracking ( sample_time timestamptz PRIMARY KEY, xid_age bigint, transactions_used bigint );

-- Insert sample INSERT INTO transaction_tracking (sample_time, xid_age, transactions_used) SELECT now(), age(datfrozenxid), 0 FROM pg_database WHERE datname = current_database();

-- Or update with transaction count INSERT INTO transaction_tracking (sample_time, xid_age, transactions_used) SELECT now(), (SELECT age(datfrozenxid) FROM pg_database WHERE datname = current_database()), xact_commit + xact_rollback FROM pg_stat_database WHERE datname = current_database();

-- Calculate rate SELECT t1.sample_time, t2.sample_time - t1.sample_time AS time_diff, t2.xid_age - t1.xid_age AS xid_increase, EXTRACT(EPOCH FROM (t2.sample_time - t1.sample_time)) AS seconds, (t2.xid_age - t1.xid_age) / NULLIF(EXTRACT(EPOCH FROM (t2.sample_time - t1.sample_time)), 0) AS xids_per_second FROM transaction_tracking t1 JOIN transaction_tracking t2 ON t2.sample_time > t1.sample_time ORDER BY t2.sample_time DESC LIMIT 10; ```

Verification

After implementing prevention measures:

```sql -- 1. Verify autovacuum is running SELECT datname, state, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%';

-- 2. Check current ages are healthy SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;

-- 3. Verify tables are being frozen SELECT schemaname, relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE autovacuum_count > 0 ORDER BY last_autovacuum DESC;

-- 4. Check no tables near wraparound SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' AND age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.7; ```

Verification

Check	Command
Database XID age	`SELECT age(datfrozenxid) FROM pg_database;`
Table XID age	`SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind='r';`
Max age setting	`SHOW autovacuum_freeze_max_age;`
Run freeze	`VACUUM FREEZE VERBOSE ANALYZE;`
Single-user mode	`postgres --single -D /data/dbname`
Check autovacuum	`SELECT * FROM pg_stat_progress_vacuum;`
Active vacuum	`SELECT * FROM pg_stat_activity WHERE query LIKE '%vacuum%';`
Preventive freeze	`ALTER TABLE t SET (autovacuum_freeze_max_age = 150000000);`

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

PostgreSQL Transaction Wraparound Prevention PostgreSQL Transaction Wraparound Prevention Database PostgreSQL Transaction Wraparound Prevention troubleshooting PostgreSQL Transaction Wraparound Prevention fix Prevent PostgreSQL transaction wraparound disasters by understanding freeze limits and managing transaction IDs Database Prevent PostgreSQL transaction wraparound disasters by understanding freeze limits and managing transaction IDs

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Nov 15, 2025

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

PostgreSQL Transaction Wraparound Prevention

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Understanding Transaction IDs

How PostgreSQL Handles Wraparound

Critical Thresholds

Check Current Wraparound Status

Database-Level Status

Table-Level Status

View Running Autovacuum Processes

Immediate Prevention Steps

1. Run Aggressive Vacuum

2. Kill Blocking Transactions

3. Handle Prepared Transactions

4. Check Replication Slots

Emergency Recovery

Step 1: Enter Single-User Mode

Step 2: If Single-User Mode Fails

Step 3: Immediate Vacuum After Recovery

Configure Proper Autovacuum

System-Wide Settings

Per-Table Tuning

Monitor and Alert

Create Monitoring View

Create Alert Function

Calculate Time to Wraparound

Transaction Rate Monitoring

Verification

Verification

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

PostgreSQL Transaction Wraparound Prevention

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Understanding Transaction IDs

How PostgreSQL Handles Wraparound

Critical Thresholds

Check Current Wraparound Status

Database-Level Status

Table-Level Status

View Running Autovacuum Processes

Immediate Prevention Steps

1. Run Aggressive Vacuum

2. Kill Blocking Transactions

3. Handle Prepared Transactions

4. Check Replication Slots

Emergency Recovery

Step 1: Enter Single-User Mode

Step 2: If Single-User Mode Fails

Step 3: Immediate Vacuum After Recovery

Configure Proper Autovacuum

System-Wide Settings

Per-Table Tuning

Monitor and Alert

Create Monitoring View

Create Alert Function

Calculate Time to Wraparound

Transaction Rate Monitoring

Verification

Verification

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading