Обсуждение: Progress of ALTER TABLE on inheritance-partitioned table?
Pg 9.6.24 (Yes, I know; it's out of my control.) I'm ALTERing that table to change a column from INTEGER to BIGINT. Of course, all of the partitions are pretty big. Even though it's one giant transaction, is there any way for me to monitor it's progress? -- Born in Arizona, moved to Babylonia.
The short answer is don't do that if the table is pretty big unless you can afford prolonged outage. The better way is to add new column, add triggers to populate it on insert/update/delete, backfill, and swap
On Fri, Oct 27, 2023, 09:34 Ron <ronljohnsonjr@gmail.com> wrote:
Pg 9.6.24 (Yes, I know; it's out of my control.)
I'm ALTERing that table to change a column from INTEGER to BIGINT. Of
course, all of the partitions are pretty big.
Even though it's one giant transaction, is there any way for me to monitor
it's progress?
--
Born in Arizona, moved to Babylonia.
On Fri, 2023-10-27 at 08:34 -0500, Ron wrote: > Pg 9.6.24 (Yes, I know; it's out of my control.) > > I'm ALTERing that table to change a column from INTEGER to BIGINT. Of > course, all of the partitions are pretty big. > > Even though it's one giant transaction, is there any way for me to monitor > it's progress? No, there is no way to monitor progress. The best you can do is to follow the growth of the table on disk. You could consider logical replication ... oh, wait, 9.6. Yours, Laurenz Albe
On 10/27/23 09:04, Laurenz Albe wrote: > On Fri, 2023-10-27 at 08:34 -0500, Ron wrote: >> Pg 9.6.24 (Yes, I know; it's out of my control.) >> >> I'm ALTERing that table to change a column from INTEGER to BIGINT. Of >> course, all of the partitions are pretty big. >> >> Even though it's one giant transaction, is there any way for me to monitor >> it's progress? > No, there is no way to monitor progress. The best you can do is to follow > the growth of the table on disk. > > You could consider logical replication ... oh, wait, 9.6. Since it's really a bunch of "small" partitions, can I track the (non)existence of those table oid files under $PGDATA/<dboid>? -- Born in Arizona, moved to Babylonia.
On Fri, 2023-10-27 at 09:19 -0500, Ron wrote: > On 10/27/23 09:04, Laurenz Albe wrote: > > On Fri, 2023-10-27 at 08:34 -0500, Ron wrote: > > > Pg 9.6.24 (Yes, I know; it's out of my control.) > > > > > > I'm ALTERing that table to change a column from INTEGER to BIGINT. Of > > > course, all of the partitions are pretty big. > > > > > > Even though it's one giant transaction, is there any way for me to monitor > > > it's progress? > > No, there is no way to monitor progress. The best you can do is to follow > > the growth of the table on disk. > > > > You could consider logical replication ... oh, wait, 9.6. > > Since it's really a bunch of "small" partitions, can I track the > (non)existence of those table oid files under $PGDATA/<dboid>? Probably not. Perhaps your best bet is to watch the growth of the data directory. Yours, Laurenz Albe