pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProgress of ALTER TABLE on inheritance-partitioned table?
5+ messages / 3 participants
[nested] [flat]
* Progress of ALTER TABLE on inheritance-partitioned table?
@ 2023-10-27 13:34 Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ron @ 2023-10-27 13:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Progress of ALTER TABLE on inheritance-partitioned table?
@ 2023-10-27 13:41 Michael Guissine <mguissine@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Guissine @ 2023-10-27 13:41 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Progress of ALTER TABLE on inheritance-partitioned table?
@ 2023-10-27 14:04 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-10-27 14:04 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Progress of ALTER TABLE on inheritance-partitioned table?
@ 2023-10-27 14:19 Ron <ronljohnsonjr@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Ron @ 2023-10-27 14:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Progress of ALTER TABLE on inheritance-partitioned table?
@ 2023-10-27 15:55 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2023-10-27 15:55 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-10-27 15:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-27 13:34 Progress of ALTER TABLE on inheritance-partitioned table? Ron <ronljohnsonjr@gmail.com>
2023-10-27 13:41 ` Michael Guissine <mguissine@gmail.com>
2023-10-27 14:04 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-27 14:19 ` Ron <ronljohnsonjr@gmail.com>
2023-10-27 15:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox