Re: Rearchitecting for storage

On 2019-07-19 11:37:52 -0400, Matthew Pounsett wrote:
> On Fri, 19 Jul 2019 at 11:25, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
>
> On 2019-07-19 10:41:31 -0400, Matthew Pounsett wrote:
> > Okay.  So I guess the short answer is no, nobody really knows how to
> > judge how much space is required for an upgrade?  :)
>
> As I understand it, a pg_upgrade --link uses only negligible extra
> space. It duplicates a bit of householding information, but not your
> data tables or indexes. Your 18 TB table will definitely not be duplicated
> during the upgrade if you can use --link.
>
>
> The documentation for pg_upgrade --link says that the old copy is no longer
> usable, which means it's modifying files that are linked.  If it were only
> modifying small housekeeping files, then it would be most efficient not to link
> those, which would keep both copies of the db usable.

This was discussed recently: The old database is made intentionally
unusable to prevent accidentally starting both (which would result in
data corruption).

> That seems incompatible with your suggestion that it doesn't need to
> modify the data files.  Depending on how it goes about doing that, it
> could mean a significant short-term increase in storage requirements
> while the data is being converted.  
>
> Going back to our recent 'reindex database' attempt, pgsql does not
> necessarily do these things in the most storage-efficient manner; it
> seems entirely likely that it would choose to use links to duplicate
> the data directory, then create copies of each data file as it
> converts them over, then link that back to the original for an atomic
> replacement.  That could eat up a HUGE amount of storage during the
> conversion process without the start and end sizes being very
> different at all.  

I can't really think of a scenario in which this would be the best
(or even a good) strategy to convert the database. I am quite confident
that pg_upgrade doesn't do that at present and reasonably confident that
it won't do it in the future.

> Sorry, but I can't reconcile your use of "as I understand it" with
> your use of "definitely".  It sounds like you're guessing, rather than
> speaking from direct knowledge of how the internals of pg_upgrade.

I don't have direct knowledge of the internals of pg_upgrade, but I
have upgraded a database of about 1 TB at least twice with --link. Since
I had much less than 1 TB of free space and the upgrade completed very
quickly, I am very confident that no user defined tables are copied. I
have also been on this mailing list for a few years and read quite a few
discussions about the usage of pg_upgrade in that time (though I may not
always have paid much attention to them).

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>