Re: Postgres upgrade 12 - issues with OIDs
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 wrote: > On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi 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 >
Question on Logical Replication
I have got a question on Logical Replication - we are using PostgreSQL version 11. We are operating in a high transaction environment - so, would like to know if disabling and enabling subscription is gonna miss any transactions ? Meaning - if a subscription is disabled and enabled after a few hours, the replication is gonna resume from the time when subscription was disabled ? We just wanna make sure transactions do not go missing. Regards, Venkata B N Database Consultant
Re: Postgres upgrade 12 - issues with OIDs
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.johns...@gmail.com> wrote: > On Saturday, May 15, 2021, Venkata B Nagothi 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. >
Re: Postgres upgrade 12 - issues with OIDs
On Mon, May 10, 2021 at 9:26 AM Venkata B Nagothi wrote: > > > On Sat, 8 May 2021 at 1:47 pm, Laurenz Albe > 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 >
Re: Postgres upgrade 12 - issues with OIDs
On Sat, 8 May 2021 at 1:47 pm, Laurenz Albe 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
Re: Postgres upgrade 12 - issues with OIDs
Missed including pgsql-general group.
On Sat, 8 May 2021 at 11:06 am, Venkata B Nagothi wrote:
>
>
> On Wed, 5 May 2021 at 9:22 am, Bruce Momjian 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
Postgres upgrade 12 - issues with OIDs
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. Regards, Venkata B N Database Consultant
Re: oracle to postgresql conversion tool
On Thu, Feb 22, 2018 at 3:59 PM, Marcin Giedz wrote: > Hi, there are at least 5 tools I found on the PG list but could you > recommend well tested, free one ? we need to migrate production 30GB oracle > 11 db to postgres 9 and are looking for best approach. Of course if there > is no free/open solution any commercial & recommended tool is always > welcome. > Try using ora2pg which is a quite popular tool and a well tested one. Regards, Venkata B N Database Consultant
Re: oracle_fdw Question
On Tue, 13 Feb 2018 at 5:55 am, chiru r wrote: > Hi, > > I am trying to install and configure the oracle_fdw on PostgreSQL DB > servers. > What we are trying is, inorder to install (make, make install) oracle_fdw > on all DB servers,we want to compile on one server and want build a > package/tar file to copy the library files and required SQL scripts into > all other servers. > So that it will reduce the installation efforts. > > can we copy the oracle_fdw.so file into "/9.5/lib/postgresql" > And oracle_fdw.control,oracle_fdw--1.1.sql and oracle_fdw--1.0--1.1.sql > files into "/9.5/share/postgresql/extension" directory. > Is this the right approach? > > Is there any process to create re-locatable binary files for oracle_fdw? > If you are installing same Postgres versions across all the DB servers, then, I would suggest build re-locatable Postgres binaries which includes oracle_fdw as well. Thanks, Venkata B > > Thanks, > chiru > -- Regards, Venkata B N Database Consultant
Re: Can postgresql ignore DST ?
On Fri, Dec 15, 2017 at 6:21 PM, Laurenz Albe wrote: > Venkata B Nagothi wrote: > > > > We have Timezone configured to Australia/Sydney, we can change that > to 11 and do we need to foresee any issues ? > > > > > > That configuration parameter defines how the client will format > > > timestamps to strings and vice versa. > > > > > > It should not have any other effects. > > > > I think what I see is some dates might have changed - is that a > possibility ? > > > > If yes, is there any way I can revert back ? > > I don't understand - you'd have to explain what you mean. > > The setting doesn't change any data, it changes how a timestamp with time > zone > is displayed. > > It also sets the default time zone to use when a timestamp without timezone > is to be converted to a timestamp with time zone, so maybe that's what you > are observing. > My bad. It was my mis-understanding, the data did not change as it was supposed to be. We had some bad data pushed from the application which led to mis-understanding. All good now. Regards, Ven
Re: Can postgresql ignore DST ?
On Fri, Dec 15, 2017 at 3:23 PM, Ben Madin wrote: > I'd be a little worried that if you set timezone = 11 for Australia/Sydney > you are embedding the daylight savings value, not the standard time value > (UTC+10) > Totally agree. We have a weird situation where-in i had to do this and i would like to learn the impact on the data, i hope it would not fiddle the existing data. We are currently experimenting this. Regards, Ven
Re: Can postgresql ignore DST ?
On Thu, 14 Dec 2017 at 7:28 pm, Laurenz Albe wrote: > Venkata B Nagothi wrote: > > On Thu, 14 Dec 2017 at 4:04 pm, Tom Lane wrote: > > > Venkata B Nagothi writes: > > > > To rule out any application issues, is it possible to get postgresql > to > > > > ignore DST and render all the timestamps with timezone offsets of > +11 ? > > > > > > set timezone = 11 > > > > We have Timezone configured to Australia/Sydney, we can change that to > 11 and do we need to foresee any issues ? > > That configuration parameter defines how the client will format > timestamps to strings and vice versa. > > It should not have any other effects. I think what I see is some dates might have changed - is that a possibility ? If yes, is there any way I can revert back ? Regards, Ven > -- Regards, Venkata B N Database Consultant
Re: Can postgresql ignore DST ?
. Do Regards, Ven On Thu, 14 Dec 2017 at 4:04 pm, Tom Lane wrote: > Venkata B Nagothi writes: > > To rule out any application issues, is it possible to get postgresql to > > ignore DST and render all the timestamps with timezone offsets of +11 ? > > set timezone = 11 Thanks a lot Tom. We have Timezone configured to Australia/Sydney, we can change that to 11 and do we need to foresee any issues ? Regards, Ven > > regards, tom lane > -- Regards, Venkata B N Database Consultant
Can postgresql ignore DST ?
Hi All, I have a question regarding PostgreSQL time zones and daylight savings - We have been migrating Oracle databases to PostgreSQL and the database we are migrating from does not seem to follow daylight savings and it is good that postgresql does. When i query the date columns i get the timezone offsets as +10 and +11 depending on the date, time etc. Upon querying Oracle, i get all the timezone offsets as +11, adding to this, making the application code compatible to this will be utterly complex. To rule out any application issues, is it possible to get postgresql to ignore DST and render all the timestamps with timezone offsets of +11 ? I know it is weird question and it is not the right thing to do on the database, i am just asking in-case we may bump into this requirement in the near future. Any advise is appreciated ! Regards, Venkata B N
