Re: pg_upgrade and wraparound

Hello again,

I've performed another test - I've migrated to the new cluster using dump
restore: pg_dumpall | psql. It went well, although it took 6 days while
pg_upgrade usually took a night.

Is there any hope the issue with pg_upgrade can be resolved? If not, could
you give me some hints as to how can I decrease time needed for pg_dumpall
| psql?

Thanks in advance!

2018-06-13 0:11 GMT+03:00 Alexander Shutyaev <shutyaev(at)gmail(dot)com>:

> Back again,
>
> >> Alexander, could you hack things up so autovacuum logging is enabled
> >> (log_autovacuum_min_duration=0), and see whether it's triggered?
>
> I've changed this config setting in both 9.6 and 10.4 postgresql.conf,
> then I've ran pg_upgrade once more.
>
> However I'm not sure how can I see whether autovacuum was triggered or
> not. I've tried grepping the logs for lines containing both 'vacuum' and
> 'auto' (case-insensitive) - there were none. If you can be more specific, I
> can look for anything else.
>
> I've googled on how can one see that the autovacuum is working, and found
> out this query, which I ran on the 10.4 cluster:
>
> select count(*) from pg_stat_all_tables where last_autovacuum is not null;
> count
> -------
> 0
> (1 row)
>
> So it seems autovacuum is indeed not working, just as you proposed.
>
> If I correctly summarized all your responses, the problem is that:
>
> 1) pg_restore (as part of pg_upgrade) inserts each large object in a
> different transaction
>
> That seems true to me given the log output - each time an object is
> inserted the wraparound warning decrements by 1
>
> 2) the autovacuum doesn't work while the database is restored
>
> That also seems true (see above)
>
> 3) the number of large objects is so big that as they are restored the
> transaction wraparound occurs
>
> Here's the number of large objects taken from the 9.6 cluster (spaces
> added manually for clarity):
>
> select count(*) from pg_largeobject_metadata ;
> count
> -----------
> 133 635 871
> (1 row)
>
> If I've googled correctly - the transaction number is a 32bit integer so
> it's limit is 2 147 483 647 which is a lot more. I guess I'm missing
> something.
>
> This is just my attempt to summarize our progress so far.
>
> I'm further open to your suggestions.
>
> 2018-06-12 14:32 GMT+03:00 Daniel Verite <daniel(at)manitou-mail(dot)org>:
>
>> Andres Freund wrote:
>>
>> > I'm not entirely clear why pg_restore appears to use a separate
>> > transaction for each large object, surely exascerbating the problem.
>>
>> To make sure that per-object locks don't fill up the shared
>> lock table?
>> There might be hundreds of thousands of large objects.
>> If it had to restore N objects per transaction, would it know
>> how to compute N that is large enough to be effective
>> and small enough not to exhaust the shared table?
>>
>> Best regards,
>> --
>> Daniel Vérité
>> PostgreSQL-powered mailer: http://www.manitou-mail.org
>> Twitter: @DanielVerite
>>
>
>