Re: pg_upgrade and materialized views

On 2018-02-20 17:29:01 -0500, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > I wonder if there's a scenario in which a schema restore uses enough
> > xids to get close to anti-wraparound territory?
>
> Interesting question ... you'd need one heck of a lot of objects in
> the cluster, but we've certainly heard of people with lots of objects.
>
> We could stave that problem off by running the restore steps in
> --single-transaction mode, if it weren't that pg_restore will reject the
> combination of --create and --single-transaction. I wonder if we should
> allow that, specifying that it means that the single transaction starts
> after we reconnect to the new DB.

One problem is that we need to restore some types of objects that can be
quite voluminous (e.g. users) during the restore of global objects. But
we IIRC can't make all of that use a single transaction as some commands
like CREATE DATABASE / TABLESPACE etc don't like that.

I suspect one of the more realistic ways to use up huge amounts of xids
would be to have lots of large objects with individual permissions?

A quick and dirt option would be to add a few hand-scheduled transaction
commands at the right places in a pg_upgrade mode :/

Greetings,

Andres Freund