One database of postgresql is down and not accepting any command

Solution Unverified - Updated -

Red Hat Insights can detect this issue

Proactively detect and remediate issues impacting your systems.
View matching systems and remediation

Environment

  • Red Hat Enterprise Linux 5.8
  • Red Hat Enterprise Linux 6

Issue

  • 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".

Resolution

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> 

RHEL6, the --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.

Routine Database Maintenance Tasks

Root Cause

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.

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.

1 Comments

Postgres 9.x may require --single as the first argument for example:

postgres --single -D /var/lib/pgsql/data <dbname>
vacuum (freeze)