Postgresql ERROR: database is not accepting commands to avoid wraparound data loss in database

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.

You might also need to commit or roll back old prepared transactions

可以看到是一些临时表的age比较大，应该是由于事物没有及时提交，影响到了数据库autovacuum这些表.

mydb=> select b.nspname,a.relname,a.relfrozenxid,age(a.relfrozenxid) from pg_class a, pg_namespace b where a.relnamespace=b.oid and a.relkind='r' order by a.relfrozenxid::text::int8 limit 10;

   nspname   |             relname              | relfrozenxid |    age     

-------------+----------------------------------+--------------+------------

 pg_temp_51  | avcp_shower_online_log_tmp       |   1185660983 | 1903192163

 pg_temp_161 | tmp_avcp_mobile_phone_data       |   2811680917 |  277172229

 pg_temp_464 | avcp_shower_online_log_tmp       |   2854092324 |  234760822

mydb=> select datname,age(datfrozenxid) from pg_database where datname=current_database();

  datname  |    age     

-----------+------------

 avcp_work | 2146485849

这里由于我进入单用户模式进行过vacuum，所以最大age表的age不等于数据库的age了，vacuum之前是相等的.

通过以下语句可以查找出age年龄大于vacuum_freeze_table_age的表：

 select datname,age(datfrozenxid) from pg_database where datname not in ('postgres','template0','template1') and age(datfrozenxid)>(select setting::int from pg_settings where name='vacuum_freeze_table_age')order by age(datfrozenxid) desc;

xidWarnLimit = xidStopLimit - 10000000 //如果xid上限达到xidWarnLimit，数据库会发出warning，让你进行vacuum

 xidStopLimit = xidWrapLimit - 1000000 //如果xid上限达到xidStopLimit，数据库会不允许任何会话申请事物号，必须进入single mode，进行vacuum freeze

vi src/include/access/transam.h   //MaxTransactionId在这里定义最大值为2^32

#define MaxTransactionId                        ((TransactionId) 0xFFFFFFFF)

 xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1) //xid是一个32位值，所以2^32右移一位

 xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age //如果xid达到autovacuum_freeze_max_age这个值，那么就算没开启自动vacuum，数据库也会进行vacuum

从以下代码可以看到报错原因：

src/backend/access/transam/varsup.c

         if (TransactionIdFollowsOrEquals(xid, ShmemVariableCache->xidVacLimit))

        {

                /*

                 * For safety's sake, we release XidGenLock while sending signals,

                 * warnings, etc.  This is not so much because we care about

                 * preserving concurrency in this situation, as to avoid any

                 * possibility of deadlock while doing get_database_name(). First,

                 * copy all the shared values we'll need in this path.

                 */

                TransactionId xidWarnLimit = ShmemVariableCache->xidWarnLimit;

                TransactionId xidStopLimit = ShmemVariableCache->xidStopLimit;

                TransactionId xidWrapLimit = ShmemVariableCache->xidWrapLimit;

                Oid                     oldest_datoid = ShmemVariableCache->oldestXidDB;



                LWLockRelease(XidGenLock);



                /*

                 * To avoid swamping the postmaster with signals, we issue the autovac

                 * request only once per 64K transaction starts.  This still gives

                 * plenty of chances before we get into real trouble.

                 */

                if (IsUnderPostmaster && (xid % 65536) == 0)

                        SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);



                if (IsUnderPostmaster &&

                        TransactionIdFollowsOrEquals(xid, xidStopLimit))

                {

                        char       *oldest_datname = get_database_name(oldest_datoid);



                        /* complain even if that DB has disappeared */

                        if (oldest_datname)

                                ereport(ERROR,

                                                (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),

                                                 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",

                                                                oldest_datname),

                                                 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"

                                                                 "You might also need to commit or roll back old prepared transactions.")));

                        else

                                ereport(ERROR,

                                                (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),

                                                 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",

                                                                oldest_datoid),

                                                 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"

                                                                 "You might also need to commit or roll back old prepared transactions.")));

                }

                else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))

                {

                        char       *oldest_datname = get_database_name(oldest_datoid);



                        /* complain even if that DB has disappeared */

                        if (oldest_datname)

                                ereport(WARNING,

                                                (errmsg("database \"%s\" must be vacuumed within %u transactions",

                                                                oldest_datname,

                                                                xidWrapLimit - xid),

                                                 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"

                                                                 "You might also need to commit or roll back old prepared transactions.")));

                        else

                                ereport(WARNING,

                                                (errmsg("database with OID %u must be vacuumed within %u transactions",

                                                                oldest_datoid,

                                                                xidWrapLimit - xid),

                                                 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"

                                                                 "You might also need to commit or roll back old prepared transactions.")));

                }

解决方法：

postgres --single -D $PGDATA mydb

如果mydb比较大，可能会很慢，可以找出最大年龄的表vaccum，如下：

vacuum freeze tablename;

如果数据库不大，可以直接vacuum freeze;做整个库的vacuum.