Red Hat Insights can detect this issue
- Red Hat Enterprise Linux 5.8
- Red Hat Enterprise Linux 6
- Following messages are issued on /var/log/postgresql
CLSTWARNING: database "xxxx" must be vacuumed within 1769911 transactions CLSTHINT: To avoid a database shutdown, execute a full-database VACUUM in "xxxx". psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "xxxx" HINT: Stop the postmaster and use a standalone backend to vacuum database "xxxx".
Run a VACUUM FULL to the affected databases, notice that this procedure will effectively locked up the database where the VACUUM was issued, and no access will be granted until the task finish.
1) Firstly, stop postgresql daemon
# service postgresql stop
2) Switch to user postgres and bring the affected database up in standalone mode, after that execute a vacuum full:
# su - postgres
$ postgres <db-name> PostgreSQL stand-alone backend 8.1.23 backend> vacuum full; backend>
--single option is required, ie:
$ postgres --single <db-name> PostgreSQL stand-alone backend 8.4.20 backend> vacuum full; backend>
3) Exit with CTRL-D and newline and repeat the process for each affected database.
4) Once that all databases were vaccummed correctly restart the database service, take a full database backup and then restart the service again.
It is recommended to put in place a regular maintenance procedure, following link have more information on it.
A number of databases have exceeded the transaction limit, and they have shutdown, it is necessary to brought up them in standalone mode and perform a full vacuum.
- Red Hat Enterprise Linux
This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.