From: | Lok P <loknath(dot)73(at)gmail(dot)com> |
---|---|
To: | Alban Hertroys <haramrae(at)gmail(dot)com> |
Cc: | Greg Sabino Mullane <htamfids(at)gmail(dot)com>, sud <suds1434(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Column type modification in big tables |
Date: | 2024-08-15 20:41:03 |
Message-ID: | CAKna9VbVXcD7tR+1V08EJ0j8HVF+HwooZ3bPyiBKpxvg-ZQ3MQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Aug 16, 2024 at 2:04 AM Lok P <loknath(dot)73(at)gmail(dot)com> wrote:
>
> On Thu, Aug 15, 2024 at 9:18 PM Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>
>>
>> > On 15 Aug 2024, at 14:15, Lok P <loknath(dot)73(at)gmail(dot)com> wrote:
>>
>> (…)
>> Are all those partitions critical, or only a relative few?
>>
>> If that’s the case, you could:
>> 1) detach the non-critical partitions
>> 2) take the system down for maintenance
>> 3) update the critical partitions
>> 4) take the system up again
>> 5) update the non-critical partitions
>> 6) re-attach the non-critical partitions
>>
>> That could shave a significant amount of time off your down-time. I would
>> script the detach and re-attach processes first, to save some extra.
>>
>>
> Thank you so much.
>
> The partition table which we are planning to apply the ALTER script is a
> child table to another big partition table. And we have foreign key
> defined on table level but not partition to partition. So will detaching
> the partitions and then altering column of each detached partition and then
> re-attaching will revalidate the foreign key again? If that is the case
> then the re-attaching partition step might consume a lot of time. Is my
> understanding correct here?
>
Additionally , if we are okay with the 7.5hrs of down time , is my
calculation/extrapolation of total time consumption based on a sample
table, for direct alter, accurate? Because, in that case , I was thinking
it's less complex and also less error prone to just do it in a single alter
command rather than going for multiple steps of detach, alter, attach
partition.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2024-08-16 00:50:30 | Re: PostgreSQL Upgrade Issue - Undefined Symbol Error |
Previous Message | Lok P | 2024-08-15 20:34:47 | Re: Column type modification in big tables |