Re: Rearchitecting for storage

On Thu, Jul 18, 2019 at 10:09 PM Matthew Pounsett <matt(at)conundrum(dot)com> wrote:
> That would likely keep the extra storage requirements small, but still non-zero. Presumably the upgrade would be unnecessary if it could be done without rewriting files. Is there any rule of thumb for making sure one has enough space available for the upgrade? I suppose that would come down to what exactly needs to get rewritten, in what order, etc., but the pg_upgrade docs don't seem to have that detail. For example, since we've got an ~18TB table (including its indices), if that needs to be rewritten then we're still looking at requiring significant extra storage. Recent experience suggests postgres won't necessarily do things in the most storage-efficient way.. we just had a reindex on that database fail (in --single-user) because 17TB was insufficient free storage for the db to grow into.
>

I've done a test on a virtual machine of mine, with the following
three databases: one 0f 4.9 GB, one of 500 MB, one of 50 MB. I know
this is not even close to your environment, however upgrading with
pg_upgrade from 10.9 to 11.4 _without_ the link option ask for 85% of
space.

On a machine with a single database of 8.9 GB and a space occupation,
as reported by df, of 64% (mean 46% available) I was able to upgrade
from 10.9 to 11.4 without the link option. Space occupation increased
of 90%.
Using the link option on the same cluster required 1.1% of extra space
(around 100 MB).
Of course, these are poor-man results, but give you an advice on the
space required by pg_ugprade (which seems to be less than 100% or 2x).

Hope this helps.
Luca