Postgres upgrade 12 - issues with OIDs

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
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
*/

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

Missed including pgsql-general group.

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

On Wed, 5 May 2021 at 9:22 am, Bruce Momjian <bruce@momjian.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

On Sat, 2021-05-08 at 13:37 +1000, Venkata B Nagothi wrote:

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 !

It makes sense, but it means that you will have to face the same problem later.
However, for upgrading from v11 with little down time you may be able to use
logical replication.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Sat, 8 May 2021 at 1:47 pm, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Sat, 2021-05-08 at 13:37 +1000, Venkata B Nagothi wrote:

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 !

It makes sense, but it means that you will have to face the same problem
later.
However, for upgrading from v11 with little down time you may be able to
use
logical replication.

Yes, we will have a lot of time to deal with the OID problem later, good
thing is we will be out of 9.5 with less trouble. Hopefully we will be on a
better replication architecture soon which will make it much easier for us.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

--

Regards,

Venkata B N
Database Consultant

On Mon, May 10, 2021 at 9:26 AM Venkata B Nagothi <nag1010@gmail.com> wrote:

On Sat, 8 May 2021 at 1:47 pm, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Sat, 2021-05-08 at 13:37 +1000, Venkata B Nagothi wrote:

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 !

It makes sense, but it means that you will have to face the same problem
later.
However, for upgrading from v11 with little down time you may be able to
use
logical replication.

Yes, we will have a lot of time to deal with the OID problem later, good
thing is we will be out of 9.5 with less trouble. Hopefully we will be on a
better replication architecture soon which will make it much easier for us.

We have upgraded our test Postgres-9.5 cluster to 11.11 and it went smooth
without complaining about OIDs which is great. However, we see the
following errors in our Postgres log files, not sure if its upgrade
related.

Error :

*ERROR: column c.relhaspkey does not exist at character 33*

Below is the query generating the error :

STATEMENT: SELECT c.relname AS table_name, c.relhaspkey AS has_primary_key
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relkind = 'r'
AND c.relnamespace = n.oid AND n.nspname = $1

Any advice on how bad it is and how to get rid of it would be great.

Regards,

Venkata B N
Database Consultant

On Saturday, May 15, 2021, Venkata B Nagothi <nag1010@gmail.com> wrote:

*ERROR: column c.relhaspkey does not exist at character 33*

Below is the query generating the error :

STATEMENT: SELECT c.relname AS table_name, c.relhaspkey AS
has_primary_key FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE
c.relkind = 'r' AND c.relnamespace = n.oid AND n.nspname = $1

Any advice on how bad it is and how to get rid of it would be great.

Figure out what is issuing the query and either fix it or upgrade to a
version that has been fixed.

David J.

On Sun, 16 May 2021 at 13:00, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Saturday, May 15, 2021, Venkata B Nagothi <nag1010@gmail.com> wrote:

Below is the query generating the error :

STATEMENT: SELECT c.relname AS table_name, c.relhaspkey AS has_primary_key FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relkind = 'r' AND c.relnamespace = n.oid AND n.nspname = $1

Any advice on how bad it is and how to get rid of it would be great.

Figure out what is issuing the query and either fix it or upgrade to a version that has been fixed.

Just in addition to that, the relhaspkey column was removed in v11.
There's mention of this and a hint at what you might want to do to
work around the removal of the column in the release notes for that
version: https://www.postgresql.org/docs/release/11.0/

David

Coming back to this thread after a while.. we have to remove OID on a 6 TB
(5 TB of indexes) table and ALTER TABLE is gonna block the table and is
gonna take hours...

We operate on pretty much 0 outage.... we do have a passive site on which
we will be doing the activity.. if not ALTER TABLE.. is there any other way
to achieve this ? I think logical replication does not allow replicating
from one table to another ?

Regards,
Ven

On Sun, 16 May 2021 at 11:00 am, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Saturday, May 15, 2021, Venkata B Nagothi <nag1010@gmail.com> wrote:

*ERROR: column c.relhaspkey does not exist at character 33*

Below is the query generating the error :

STATEMENT: SELECT c.relname AS table_name, c.relhaspkey AS
has_primary_key FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE
c.relkind = 'r' AND c.relnamespace = n.oid AND n.nspname = $1

Any advice on how bad it is and how to get rid of it would be great.

Figure out what is issuing the query and either fix it or upgrade to a
version that has been fixed.

Upon googling, I came to know that this error occurs when a connection to
higher version Postgres using lower version binaries. So, might be some
job/script in our environment is using 9.5 binaries against 11 version
which is generating this error. Will check that out.

On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi <nag1010@gmail.com> wrote:

Coming back to this thread after a while.. we have to remove OID on a 6 TB (5 TB of indexes) table and ALTER TABLE is gonna block the table and is gonna take hours...

You may want to look into exploiting table inheritance for this.
Something like:

create table tab (a int, b int) with oids; -- the existing table

begin; -- do make the following atomic
alter table tab rename to old_tab;
create table tab (a int, b int) without oids; -- new version of the
table, without oids
alter table old_tab inherit tab; -- make it so querying the new table
also gets rows from the old table.
commit;

-- do this a bunch of times over the course of a few days until
old_tab is empty.
with del as (delete from old_tab where a in (select a from old_tab
limit 1000) returning *) insert into tab select * from del;

you can then drop the old table.

You'll need to think carefully about unique constraints and any other
constraints which are on the table in question. You'll want to do a
lot of testing before committing to doing this too.

David

Thanks all for the ideas, we have chosen to resolve this using Logical
Replication as we cannot use any other methods due to various constraints.

Regards,

Venkata B N
Database Consultant

On Mon, Nov 28, 2022 at 11:16 AM David Rowley <dgrowleyml@gmail.com> wrote: