which db caused postgres to stop serving due to wraparound prevention?

From: Steve Kehlet <steve(dot)kehlet(at)gmail(dot)com>
To: Forums postgresql <pgsql-general(at)postgresql(dot)org>
Subject: which db caused postgres to stop serving due to wraparound prevention?
Date: 2016-03-17 18:47:40
Message-ID: CA+bfosHOAPn9yKcCL-tQ1_1PiqC_7j7cy==swd+t2Wo0jdLBGQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry, seems like such a noob problem, but I'm stumped. This is postgres
9.4.5. I'll post my custom settings if desired but I don't think they're
needed.

We recently had an issue where the autovacuumer wasn't starting because
postgres couldn't resolve the hostname 'localhost' (we had bad perms on
/etc/hosts). We're still working on getting that fixed on all affected
boxes.

In the meantime: today, one particular database unexpectedly stopped
serving with this error:

2016-03-17 12:31:52 EDT [5395]: [787-1] ERROR: database is not accepting
commands to avoid wraparound data loss in database with OID 0
2016-03-17 12:31:52 EDT [5395]: [788-1] HINT: Stop the postmaster and
vacuum that database in single-user mode.
You might also need to commit or roll back old prepared
transactions.

What has me confused is I ran the following command to keep an eye on this,
and it seemed fine, the max(age(datfrozenxid)) was only about 330 million:

postgres=# select datname,age(datfrozenxid) from pg_database;
datname | age
-----------+-----------
mydb | 330688846
postgres | 215500760
template1 | 198965879
template0 | 146483694
mydb2 | 175585538
(5 rows)

We shutdown postgres, started it in single user mode, and VACUUMed each
database. Then postgres started up fine, and the crisis is averted, for now.

However my understanding must be wrong: I thought we could just look around
for max(age(datfrozenxid)), make sure it's "low" (<2 billion), and be sure
that this wouldn't happen. What am I misunderstanding?

And then, I don't know which db has OID 0?

postgres=# SELECT oid,datname from pg_database;
oid | datname
------------+-----------
16422 | mydb
12921 | postgres
1 | template1
12916 | template0
1575433129 | mydb2
(5 rows)

Thank you for your help!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Kehlet 2016-03-17 18:59:17 Re: which db caused postgres to stop serving due to wraparound prevention?
Previous Message Chris Travers 2016-03-17 17:01:57 Re: vacuum - reclaiming disk space.