山东现场反应postgresql数据库无法提交事务,但是可以查询。通过查看报错信息,发现以下报错
ERROR: database is not accepting commands to avoid wraparound data loss in database "pepsidb"
Hint: Stop the postmaster and vacuum that database in single-user mode.
问题原因
当新老事务id差超过21亿的时候,事务号会发生回卷,此时数据库会报错并拒绝所有连接,必须进入单用户模式执行vacuum freeze操作。
解决方案
1.查看数据库年龄
postgres=# select datname, age(datfrozenxid) from pg_database;
经查看,大部分数据库年龄已经达到了21亿,事务id发生回卷,导致数据库无法使用。需要进行vacuum 操作来降低数据库年龄,恢复数据库
2.查看数据库大小
以pepsidb为例
postgres=# select pg_database_size('pepsidb');
pg_database_size
------------------
12855206767
3.查看磁盘空间大小
数据库的数据目录空闲空间充足,可以进行vacuum操作
4.对整库进行vacuum操作
Postgres用户下:
[postgres@pg01 ~]$ vacuumdb -d pepsidb -F -f -z -p 15432
注:-F --freeze freeze row transaction information
-f --full do full vacuuming
-z, --analyze update optimizer statistics
-p, --port=PORT database server port
5.对每个数据库进行vacuum操作
[postgres@pg01 ~]$ vacuumdb -d pepsidb -F -f -z -p 15432
[postgres@pg01 ~]$ vacuumdb -d testdb -F -f -z -p 15432
[postgres@pg01 ~]$ vacuumdb -d nbdb -F -f -z -p 15432
。。。。。。
6.再次查看数据库年龄
postgres=# select datname, age(datfrozenxid) FROM pg_database;
数据库年龄已降低,数据库已经恢复正常。
7.可能会出现的报错
[postgres@pg01 ~]$ vacuumdb -d pepsidb -F -f -z -p 15432
vacuumdb: vacuuming database "pepsidb"
vacuumdb: error: vacuuming of table "pepsi.t_test" in database "pepsidb" failed:ERROR: database is not accepting commands to avoid wraparound data loss in database "pepsidb"
HINT: Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
此时进入单用户模式,对表进行vacuum操作
停库:
pg_ctl stop -D /pgdata/data/pgsql/data
单用户模式:
/usr/pgsql-12/bin/postgres --single -D /pgdata/data/pgsql/data pepsidb
PostgreSQL stand-alone backend 12.4
backend> vacuum full pepsi.t_test;
backend>