how to upgrade with catalog change

From: ZongtianHou <zongtianhou(at)icloud(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: how to upgrade with catalog change
Date: 2020-09-10 09:15:07
Message-ID: 6D98D845-DD56-43B7-A563-DCAFE61D655E@icloud.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2020-09-10 14:16:17 Re: how to upgrade with catalog change
Previous Message Karen Jex 2020-09-09 13:32:18 Re: Actual meaning from pg_stat_replication.sync_state potential vs. sync and the usage of synchronous_standby_names