Pivotal Knowledge Base

Follow

How to solve the "database is not accepting commands to avoid wraparound data loss in database XXX"

Environment

  • Pivotal Greenplum(GPDB) 4.2.x
  • Pivotal Greenplum(GPDB) 4.3.x

Symptom

GPDB stop accepting connections and client receiving the following error

Database is not accepting commands to avoid wraparound data loss in database "dbname"

You may also see the following warning

Server: host.localdomain, database: pgdb WARNING: database "pgdb" must be vacuumed within 1461689882 transactions (seg65 slice2 myhost:16727 pid=156528) HINT: To avoid a database shutdown, execute a full-database VACUUM in "pgdb"

Cause

PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction's XID is ""in the future"" and should not be visible to the current transaction. But since transaction IDs have limited size (32 bits at this writing) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future which means their outputs become invisible. Refer to http://www.postgresql.org/docs/9.4/static/routine-vacuuming.html

There are 2 configuration settings in GPDB that effect this:  xid_warn_limit and xid_stop_limit.  xid_warn_limit defaults to 500 million and impacts when the warnings are generated and xid_stop_limit defaults to 1 billion and impacts when the database stops accepting connections.  These are both hidden configuration parameters are not recommended to be modified in most cases

Fix

Please remember to vacuum every table in every database on the GPDB Cluster at least once every two billion transactions. Some table won't be vacuumed using the default vacuum command in psql . Instead, they could be: Tables under schema/namespace pg_temp_xxxx, where xxxx isa number Some external table generated by gpload .

To find out the remaining tables and databases which need to be vacuumed you can run the following queries.  There is also a script attached that can be used to generate the VACUUM commands:

    SQL_DB_AGE = "SELECT -1, datname, age(datfrozenxid) " +
        "FROM pg_database " +
        "UNION ALL " +
        "SELECT gp_segment_id, datname, age(datfrozenxid) " +
        "FROM gp_dist_random('pg_database') " +
        "ORDER BY 3 DESC " +
        "%s " +
        ";"
 
    SQL_TAB_AGE = "SELECT coalesce(n.nspname, '<Missing schema>'), relname, relkind, relstorage, age(relfrozenxid) " +
        "FROM pg_class c " +
        "     LEFT JOIN pg_namespace n ON c.relnamespace = n.oid " +
        "WHERE relkind = 'r' AND relstorage NOT IN ('x') " +
        "ORDER BY 5 DESC " +
        "%s " +
        ";"

Comments

  • Avatar
    Brendan Stephens

    Might be helpful to show how the queries tie into the exiting script.

Powered by Zendesk