Re: BUG #15840: Vacuum does not work after database stopped for wraparound protection. Database seems unrepearable.

Hi,

On 2019-06-07 18:22:20 +0000, PG Bug reporting form wrote:
> I was doing tables COPY between my old server with PG10.8 and the new one
> with 12Beta1. After each table is done, I make a vacuum of it.
> However PG12 has stopped working for wraparound protection. I was doing it
> on around 10 cpu, 1 table by cpu.

That was a new postgres 12 cluster, not a pg_upgraded one? And you just
did a bunch of COPYs? How many?

I'm not clear as to how the cluster got to wraparound if that's the
scenario. We use one xid per transaction, and copy doesn't use multiple
transactions internally. Any chance you have triggers on these tables
that use savepoints internally?

> postgres --single emet_zhen
> VACUUM FREEZE VERBOSE;

Don't FREEZE in wraparound cases, that just makes it take longer.

> It worked a few hours and when I was thinking it was done as nothing was
> loggin anymore, I made a ctrl-\ and restarted the DB.
> I was still getting wraparound protection messages so I shutdown the DB
> again & redo the vacuum command but it didn't work anymore:

> zhen:semt700 $ postgres --single emet_zhen
> 2019-06-07 17:23:36 UTC 7251 WARNING: database with OID 16394 must be
> vacuumed within 999995 transactions
> 2019-06-07 17:23:36 UTC 7251 HINT: To avoid a database shutdown, execute a
> database-wide VACUUM in that database.
> You might also need to commit or roll back old prepared
> transactions, or drop stale replication slots.
> PostgreSQL stand-alone backend 12beta1
> backend> VACUUM VERBOSE;
> 2019-06-07 17:23:59 UTC 7251 WARNING: database "emet_zhen" must be
> vacuumed within 999995 transactions
> 2019-06-07 17:23:59 UTC 7251 HINT: To avoid a database shutdown, execute a
> database-wide VACUUM in that database.
> You might also need to commit or roll back old prepared
> transactions, or drop stale replication slots.
> 2019-06-07 17:23:59 UTC 7251 LOG: duration: 2417.639 ms statement: VACUUM
> VERBOSE;

What do you mean by "didn't work anymore"? As far as I can tell the
VACUUM here succeeded?

> HINT: To avoid a database shutdown, execute a database-wide VACUUM in that
> database.
> You might also need to commit or roll back old prepared transactions, or
> drop stale replication slots.

Did you check whether any of these are the case?

SELECT * FROM pg_replication_slots;
SELECT * FROM pg_prepared_xacts;

Could you also show

SELECT oid, datname, datfrozenxid, age(datfrozenxid), datminmxid, mxid_age(datminmxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;
SELECT * FROM pg_control_checkpoint();

Greetings,

Andres Freund