Re: Postgres upgrade 12 - issues with OIDs

Missed including pgsql-general group.

On Sat, 8 May 2021 at 11:06 am, Venkata B Nagothi <nag1010(at)gmail(dot)com> wrote:

>
>
> On Wed, 5 May 2021 at 9:22 am, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>> On Wed, May 5, 2021 at 07:49:29AM +1000, Venkata B Nagothi wrote:
>> > Hi There,
>> >
>> > We are attempting to upgrade our Postgres databases from 9.5 to 12.5
>> using
>> > pg_upgrade link mode and are facing issues with OIDs.
>> >
>> > ALTER TABLE... SET WITHOUT OIDs on the larger tables is taking very
>> long and is
>> > locking up the table as well. We do have tables of more than 1 TB of
>> size.
>> > Is there any way to make this complete faster ? Any suggestions would
>> be
>> > great.
>>
>> Uh, I see this on our code:
>>
>> pg_fatal("Your installation contains tables declared WITH OIDS,
>> which is not\n"
>> "supported anymore. Consider removing the oid column
>> using\n"
>> " ALTER TABLE ... SET WITHOUT OIDS;\n"
>> "A list of tables with the problem is in the file:\n"
>> " %s\n\n", output_path);
>>
>> Uh, I don't know of any way to speed that up, though it might be faster
>
>
> That’s a big challenge for us as we want to achieve this with 0 down time
> to our live database and very minimal downtime to our DR.
>
>>
>
>> if it was done while no one else was accessing the table. I see this
>> comment in our PG 11 code:
>>
>> /*
>> * If we dropped the OID column, must adjust pg_class.relhasoids and
>> tell
>> * Phase 3 to physically get rid of the column. We formerly left the
>> * column in place physically, but this caused subtle problems. See
>> * http://archives.postgresql.org/pgsql-hackers/2009-02/msg00363.php
>> */
>
>
> We are thinking to upgrade to PG 11 instead so that we can avoid doing
> ALTER TABLE.. SET WITHOUT OIDs. Does that makes sense ? Please advise if
> there are any gotchas !
>
>>
>
>>
>> --
>
> Regards,
>
> Venkata B N
> Database Consultant
>
>
--

Regards,

Venkata B N
Database Consultant