From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Rearchitecting for storage |
Date: | 2019-07-19 19:39:56 |
Message-ID: | 20190719193956.uxcuxbsoyj2pzh26@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/>
From | Date | Subject | |
---|---|---|---|
Next Message | Alexandru Lazarev | 2019-07-19 19:52:05 | Re: pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)? |
Previous Message | Laurenz Albe | 2019-07-19 19:27:20 | Re: pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)? |