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

Maybe my custom settings are relevant. Here they are in a gist:

https://gist.github.com/skehlet/08aeed3d06f1c35bc780

On Thu, Mar 17, 2016 at 11:47 AM Steve Kehlet <steve(dot)kehlet(at)gmail(dot)com>
wrote:

> 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!
>
>