pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to upgrade with catalog change
3+ messages / 2 participants
[nested] [flat]
* how to upgrade with catalog change
@ 2020-09-10 09:15 ZongtianHou <zongtianhou@icloud.com>
2020-09-10 14:16 ` Re: how to upgrade with catalog change Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: ZongtianHou @ 2020-09-10 09:15 UTC (permalink / raw)
To: pgsql-admin
Hi, everyone
I have a new version which merely add a new column (indnkeyatts int2) in pg_index and can not be upgraded
1. if I add this column in middle by altering pg_index add column and drop column in old version to match every column position in new version, then can not started with new version, because there is dropped column info in old heap tuple header and it is accessed by hardcoded macro like Anum_pg_index_indkeynatts which cause column mismatch. And this can not be handled by deleting and inserting data again because database will be obsolete once the original data is deleted.
2. if this column is added to the end of pg_index( change macro in pg_index.h and pg_attribute.h), in this way I just need to add the new column and will not break old tuple, however,it can not be inited, does this column have to be added after column indnatts because fixed and variable length thing?
is there a simple way to just add this column and upgrade? and how does you implement it in pg_upgrade?
Best Regards,
Zongtian
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to upgrade with catalog change
2020-09-10 09:15 how to upgrade with catalog change ZongtianHou <zongtianhou@icloud.com>
@ 2020-09-10 14:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-11 03:11 ` Re: how to upgrade with catalog change ZongtianHou <zongtianhou@icloud.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2020-09-10 14:16 UTC (permalink / raw)
To: ZongtianHou <zongtianhou@icloud.com>; +Cc: pgsql-admin
ZongtianHou <zongtianhou@icloud.com> writes:
> I have a new version which merely add a new column (indnkeyatts int2) in pg_index and can not be upgraded
> 1. if I add this column in middle by altering pg_index add column and drop column in old version to match every column position in new version, then can not started with new version, because there is dropped column info in old heap tuple header and it is accessed by hardcoded macro like Anum_pg_index_indkeynatts which cause column mismatch. And this can not be handled by deleting and inserting data again because database will be obsolete once the original data is deleted.
> 2. if this column is added to the end of pg_index( change macro in pg_index.h and pg_attribute.h), in this way I just need to add the new column and will not break old tuple, however,it can not be inited, does this column have to be added after column indnatts because fixed and variable length thing?
> is there a simple way to just add this column and upgrade? and how does you implement it in pg_upgrade?
pg_upgrade never attempts to do direct surgery on catalogs. The catalog
contents are transferred via pg_dump and reload, and only user tables are
moved physically. Even if we cared to take the risk of doing something
like your suggestions above, it could only work for very trivial
cross-version catalog changes ... but such changes are frequently
far from trivial. The SQL script generated by pg_dump is much
more portable.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to upgrade with catalog change
2020-09-10 09:15 how to upgrade with catalog change ZongtianHou <zongtianhou@icloud.com>
2020-09-10 14:16 ` Re: how to upgrade with catalog change Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-09-11 03:11 ` ZongtianHou <zongtianhou@icloud.com>
0 siblings, 0 replies; 3+ messages in thread
From: ZongtianHou @ 2020-09-11 03:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks very much, it seems that using pg_dump and reload is the standard and most safe way. Happy to get it.
Best Regards,
Zongtian
> On Sep 10, 2020, at 10:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> ZongtianHou <zongtianhou@icloud.com> writes:
>> I have a new version which merely add a new column (indnkeyatts int2) in pg_index and can not be upgraded
>> 1. if I add this column in middle by altering pg_index add column and drop column in old version to match every column position in new version, then can not started with new version, because there is dropped column info in old heap tuple header and it is accessed by hardcoded macro like Anum_pg_index_indkeynatts which cause column mismatch. And this can not be handled by deleting and inserting data again because database will be obsolete once the original data is deleted.
>> 2. if this column is added to the end of pg_index( change macro in pg_index.h and pg_attribute.h), in this way I just need to add the new column and will not break old tuple, however,it can not be inited, does this column have to be added after column indnatts because fixed and variable length thing?
>
>> is there a simple way to just add this column and upgrade? and how does you implement it in pg_upgrade?
>
> pg_upgrade never attempts to do direct surgery on catalogs. The catalog
> contents are transferred via pg_dump and reload, and only user tables are
> moved physically. Even if we cared to take the risk of doing something
> like your suggestions above, it could only work for very trivial
> cross-version catalog changes ... but such changes are frequently
> far from trivial. The SQL script generated by pg_dump is much
> more portable.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-09-11 03:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-10 09:15 how to upgrade with catalog change ZongtianHou <zongtianhou@icloud.com>
2020-09-10 14:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-11 03:11 ` ZongtianHou <zongtianhou@icloud.com>
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