Fwd: Getting error while upgrading

[hector vass]

On Sat, Mar 9, 2024 at 12:18 PM omkar narkar 
wrote:

> Hello Team,
>
> I am trying to upgrade my edb 10.5 community version to postgres 15.6
> version and while doing this i am getting error regarding OIDS are not
> stable across Postgresql version (sys.callback_queue_table.user_data).
> Kindly help me to get the solution of this issue.
>
> Thanks and regards,
> Omkar Narkar
>

Usually get this error if there are composite data types or data types that
cannot be translated between 10.5 and 15.6.
The clue may be in the error message just before it says 'OIDS are not
stable across Postgresql version'
You state edb 10.5 community guessing you are using pg_upgrade and going
from windows to linux ? I am impressed if you can do that, would you not
end up with collation issues?
If you are using pg_upgrade what does pg_upgrade --check say
I would dump the schema to a sql file
pg_dump -s >dumped.sql
Then run the sql one command at a time to track down where you are going to
have a problem.

Getting error while upgrading

[omkar narkar]

Hello Team,

I am trying to upgrade my edb 10.5 community version to postgres 15.6
version and while doing this i am getting error regarding OIDS are not
stable across Postgresql version (sys.callback_queue_table.user_data).
Kindly help me to get the solution of this issue.

Thanks and regards,
Omkar Narkar

Re: Re[2]: Getting error while upgrading postgres from version 12 to 13

[Geoff Winkless]

On Tue, 21 Mar 2023 at 16:06, Geoff Winkless  wrote:

> On Tue, 21 Mar 2023 at 10:29, shashidhar Reddy <
> shashidharreddy...@gmail.com> wrote:
>
>> Actually I was using the below command to check the compatibility, it
>> worked without any issue with 12.6 but it is not working with 1version 2.14
>> time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
>> /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
>> --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 --link
>> --check
>>
>
> Just in case I'm _not_ misreading this... are you saying you already ran
> this command once with postgres v12.6?
>
> If so, since you've used --link the original folder will no longer be
> version12 data.
>

No, I'm an idiot. Ignore me, I missed "--check".

Geoff

Re: Re[2]: Getting error while upgrading postgres from version 12 to 13

[Geoff Winkless]

On Tue, 21 Mar 2023 at 10:29, shashidhar Reddy 
wrote:

> Actually I was using the below command to check the compatibility, it
> worked without any issue with 12.6 but it is not working with 1version 2.14
> time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
> /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
> --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 --link
> --check
>

Just in case I'm _not_ misreading this... are you saying you already ran
this command once with postgres v12.6?

If so, since you've used --link the original folder will no longer be
version12 data.

Geoff

>

Re: Getting error while upgrading postgres from version 12 to 13

[Adrian Klaver]

On 3/21/23 08:13, shashidhar Reddy wrote:

Both shows different versions as 13 shows version 13.10 and 12 shows 12.

The question asked was:

"Check the value that is the PG_VERSION file in each data directory. It
should be 12 in /usr/pgdata/pg_data  and 13 in /usr/pgdata/pg_data2."

So indicate which directory path has which PG_VERSION value.

While you are at it do:

/usr/lib/postgresql/13/bin/pg_upgrade -V

and post the version returned.



On Tue, 21 Mar, 2023, 8:09 pm Adrian Klaver, > wrote:


On 3/21/23 03:29, shashidhar Reddy wrote:
 > Actually I was using the below command to check the
compatibility, it
 > worked without any issue with 12.6 but it is not working with
1version 2.14
 > time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
 > /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
 > --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2
 > --link --check
 >
 > I have used a different directory for postgres13 from postgres 12.

The issue is not that they are the same directory it is:

FATAL:  database files are incompatible with server
   DETAIL:  The data directory was initialized by PostgreSQL version 13
, which is not compatible with this version 12.14 (Ubuntu
12.14-1.pgdg18.04+1)

Check the value that is the PG_VERSION file in each data directory. It
should be 12 in /usr/pgdata/pg_data  and 13 in /usr/pgdata/pg_data2.

 >
 > On Mon, Mar 20, 2023 at 11:07 AM mailto:abdulsayee...@gmail.com>
 > >> wrote:
 >
 >     Hello,
 >
 >     As per error it seems you have initiated source data with PG13
 >     binaries.
 >
 >     Could you please connect to both source and target psql and share
 >     out of select versions();
 >
 >     Also share pg_upgrade complete command which you are using.
 >
 >
 >
 >     Regards,
 >     Abdul Sayeed
 >
 >
 >
 >     Sent from myMail for iOS
 >
 >
 >     Sunday, 19 March 2023 at 11:29 AM +0530 from
 > david.g.johns...@gmail.com 
>
 >     mailto:david.g.johns...@gmail.com>
>>:
 >
 >         On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy
 >         mailto:shashidharreddy...@gmail.com>
 >         >> wrote:
 >
 >             Can someone please help me with this?
 >
 >
 >
 >         You haven't given others enough information to help you.
 >         Present to the fullest extent possible your current
environment
 >         and the commands you are running.  Showing that psql itself
 >         works on both running clusters would be a good start.  Show
 >         inputs, not just outputs those inputs creates.
 >
 >         David J.
 >
 >
 >
 > --
 > Shashidhar

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Getting error while upgrading postgres from version 12 to 13

[shashidhar Reddy]

Both shows different versions as 13 shows version 13.10 and 12 shows 12.14

On Tue, 21 Mar, 2023, 8:09 pm Adrian Klaver, 
wrote:

> On 3/21/23 03:29, shashidhar Reddy wrote:
> > Actually I was using the below command to check the compatibility, it
> > worked without any issue with 12.6 but it is not working with 1version
> 2.14
> > time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
> > /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
> > --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2
> > --link --check
> >
> > I have used a different directory for postgres13 from postgres 12.
>
> The issue is not that they are the same directory it is:
>
> FATAL:  database files are incompatible with server
>   DETAIL:  The data directory was initialized by PostgreSQL version 13
> , which is not compatible with this version 12.14 (Ubuntu
> 12.14-1.pgdg18.04+1)
>
> Check the value that is the PG_VERSION file in each data directory. It
> should be 12 in /usr/pgdata/pg_data  and 13 in /usr/pgdata/pg_data2.
>
> >
> > On Mon, Mar 20, 2023 at 11:07 AM  > > wrote:
> >
> > Hello,
> >
> > As per error it seems you have initiated source data with PG13
> > binaries.
> >
> > Could you please connect to both source and target psql and share
> > out of select versions();
> >
> > Also share pg_upgrade complete command which you are using.
> >
> >
> >
> > Regards,
> > Abdul Sayeed
> >
> >
> >
> > Sent from myMail for iOS
> >
> >
> > Sunday, 19 March 2023 at 11:29 AM +0530 from
> > david.g.johns...@gmail.com 
> > mailto:david.g.johns...@gmail.com>>:
> >
> > On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy
> >  > > wrote:
> >
> > Can someone please help me with this?
> >
> >
> >
> > You haven't given others enough information to help you.
> > Present to the fullest extent possible your current environment
> > and the commands you are running.  Showing that psql itself
> > works on both running clusters would be a good start.  Show
> > inputs, not just outputs those inputs creates.
> >
> > David J.
> >
> >
> >
> > --
> > Shashidhar
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Getting error while upgrading postgres from version 12 to 13

[Adrian Klaver]

On 3/21/23 03:29, shashidhar Reddy wrote:
Actually I was using the below command to check the compatibility, it 
worked without any issue with 12.6 but it is not working with 1version 2.14
time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir 
/usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin 
--old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 
--link --check


I have used a different directory for postgres13 from postgres 12.


The issue is not that they are the same directory it is:

FATAL:  database files are incompatible with server
 DETAIL:  The data directory was initialized by PostgreSQL version 13
, which is not compatible with this version 12.14 (Ubuntu
12.14-1.pgdg18.04+1)

Check the value that is the PG_VERSION file in each data directory. It 
should be 12 in /usr/pgdata/pg_data  and 13 in /usr/pgdata/pg_data2.




On Mon, Mar 20, 2023 at 11:07 AM > wrote:


Hello,

As per error it seems you have initiated source data with PG13
binaries.

Could you please connect to both source and target psql and share
out of select versions();

Also share pg_upgrade complete command which you are using.



Regards,
Abdul Sayeed



Sent from myMail for iOS


Sunday, 19 March 2023 at 11:29 AM +0530 from
david.g.johns...@gmail.com 
mailto:david.g.johns...@gmail.com>>:

On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy
mailto:shashidharreddy...@gmail.com>> wrote:

Can someone please help me with this?



You haven't given others enough information to help you. 
Present to the fullest extent possible your current environment

and the commands you are running.  Showing that psql itself
works on both running clusters would be a good start.  Show
inputs, not just outputs those inputs creates.

David J.



--
Shashidhar


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Re[2]: Getting error while upgrading postgres from version 12 to 13

[shashidhar Reddy]

Actually I was using the below command to check the compatibility, it
worked without any issue with 12.6 but it is not working with 1version 2.14
time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
/usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
--old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 --link
--check

I have used a different directory for postgres13 from postgres 12.

On Mon, Mar 20, 2023 at 11:07 AM  wrote:

> Hello,
>
> As per error it seems you have initiated source data with PG13 binaries.
>
> Could you please connect to both source and target psql and share out of
> select versions();
>
> Also share pg_upgrade complete command which you are using.
>
>
>
> Regards,
> Abdul Sayeed
>
>
>
> Sent from myMail for iOS
>
>
> Sunday, 19 March 2023 at 11:29 AM +0530 from david.g.johns...@gmail.com <
> david.g.johns...@gmail.com>:
>
> On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy <
> shashidharreddy...@gmail.com> wrote:
>
> Can someone please help me with this?
>
>
>
> You haven't given others enough information to help you.  Present to the
> fullest extent possible your current environment and the commands you are
> running.  Showing that psql itself works on both running clusters would be
> a good start.  Show inputs, not just outputs those inputs creates.
>
> David J.
>
>

-- 
Shashidhar

Re[2]: Getting error while upgrading postgres from version 12 to 13

[abdulsayeed24]

Hello,

As per error it seems you have initiated source data with PG13 binaries. 

Could you please connect to both source and target psql and share out of select 
versions();

Also share pg_upgrade complete command which you are using. 



Regards,
Abdul Sayeed



Sent from myMail for iOS


Sunday, 19 March 2023 at 11:29 AM +0530 from david.g.johns...@gmail.com  
:
>On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy < shashidharreddy...@gmail.com 
>> wrote:
>>Can someone please help me with this?
>>>
>
>You haven't given others enough information to help you.  Present to the 
>fullest extent possible your current environment and the commands you are 
>running.  Showing that psql itself works on both running clusters would be a 
>good start.  Show inputs, not just outputs those inputs creates.
>
>David J.

Re: Getting error while upgrading postgres from version 12 to 13

[David G. Johnston]

On Sat, Mar 18, 2023 at 7:27 PM shashidhar Reddy <
shashidharreddy...@gmail.com> wrote:

> Can someone please help me with this?
>
>>
>>
You haven't given others enough information to help you.  Present to the
fullest extent possible your current environment and the commands you are
running.  Showing that psql itself works on both running clusters would be
a good start.  Show inputs, not just outputs those inputs creates.

David J.

Re: Getting error while upgrading postgres from version 12 to 13

[Adrian Klaver]

On 3/18/23 19:26, shashidhar Reddy wrote:

Can someone please help me with this?


You have already been helped:

https://www.postgresql.org/message-id/ba59dd9b-6a87-ebff-c041-2dbcffe74a89%40sourcepole.ch

"You have configured the postgres v12 server to use a directory where
there are data from a postgres v13 server. Evidently that can not work..."



On Sun, 19 Mar, 2023, 2:54 am shashidhar Reddy, 
mailto:shashidharreddy...@gmail.com>> wrote:


and adding to this the following is also showing in the error, we
tried changing the file permissions but still no luck
FATAL:  could not load private key file
"/etc/ssl/private/ssl-cert-snakeoil.key": key values mismatch

On Sun, Mar 19, 2023 at 2:09 AM shashidhar Reddy
mailto:shashidharreddy...@gmail.com>>
wrote:

Hello,

I am in a process of upgrading postgresql from version 12 to 13
using pg_upgrdrade but I am getting error stating

  FATAL:  database files are incompatible with server
  DETAIL:  The data directory was initialized by PostgreSQL
version 13
, which is not compatible with this version 12.14 (Ubuntu
12.14-1.pgdg18.04+1)

Please help me on this issue.
-- 
Shashidhar




-- 
Shashidhar




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Getting error while upgrading postgres from version 12 to 13

[shashidhar Reddy]

Can someone please help me with this?

On Sun, 19 Mar, 2023, 2:54 am shashidhar Reddy, <
shashidharreddy...@gmail.com> wrote:

> and adding to this the following is also showing in the error, we tried
> changing the file permissions but still no luck
> FATAL:  could not load private key file
> "/etc/ssl/private/ssl-cert-snakeoil.key": key values mismatch
>
> On Sun, Mar 19, 2023 at 2:09 AM shashidhar Reddy <
> shashidharreddy...@gmail.com> wrote:
>
>> Hello,
>>
>> I am in a process of upgrading postgresql from version 12 to 13 using
>> pg_upgrdrade but I am getting error stating
>>
>>  FATAL:  database files are incompatible with server
>>  DETAIL:  The data directory was initialized by PostgreSQL version 13
>> , which is not compatible with this version 12.14 (Ubuntu
>> 12.14-1.pgdg18.04+1)
>>
>> Please help me on this issue.
>> --
>> Shashidhar
>>
>
>
> --
> Shashidhar
>

Re: Getting error while upgrading postgres from version 12 to 13

[Tomas Pospisek]

On Sun, Mar 19, 2023 at 2:09 AM shashidhar Reddy 
mailto:shashidharreddy...@gmail.com>> wrote:


Hello,

I am in a process of upgrading postgresql from version 12 to 13
using pg_upgrdrade but I am getting error stating

  FATAL:  database files are incompatible with server
  DETAIL:  The data directory was initialized by PostgreSQL version 13
, which is not compatible with this version 12.14 (Ubuntu
12.14-1.pgdg18.04+1)

Please help me on this issue.


You have configured the postgres v12 server to use a directory where 
there are data from a postgres v13 server. Evidently that can not work...

*t

Re: Getting error while upgrading postgres from version 12 to 13

[Tomas Pospisek]

On 18.03.23 22:24, shashidhar Reddy wrote:
and adding to this the following is also showing in the error, we tried 
changing the file permissions but still no luck
FATAL:  could not load private key file 
"/etc/ssl/private/ssl-cert-snakeoil.key": key values mismatch


This sounds like the private SSL key you have configured is not the 
private key for the public key you have configured - they do not match.

*t

Re: Getting error while upgrading postgres from version 12 to 13

[shashidhar Reddy]

and adding to this the following is also showing in the error, we tried
changing the file permissions but still no luck
FATAL:  could not load private key file
"/etc/ssl/private/ssl-cert-snakeoil.key": key values mismatch

On Sun, Mar 19, 2023 at 2:09 AM shashidhar Reddy <
shashidharreddy...@gmail.com> wrote:

> Hello,
>
> I am in a process of upgrading postgresql from version 12 to 13 using
> pg_upgrdrade but I am getting error stating
>
>  FATAL:  database files are incompatible with server
>  DETAIL:  The data directory was initialized by PostgreSQL version 13
> , which is not compatible with this version 12.14 (Ubuntu
> 12.14-1.pgdg18.04+1)
>
> Please help me on this issue.
> --
> Shashidhar
>


-- 
Shashidhar

Re: Getting error while upgrading postgres from version 12 to 13

[Ron]

On 3/18/23 15:39, shashidhar Reddy wrote:

Hello,

I am in a process of upgrading postgresql from version 12 to 13 using 
pg_upgrdrade but I am getting error stating


 FATAL:  database files are incompatible with server
 DETAIL:  The data directory was initialized by PostgreSQL version 13
, which is not compatible with this version 12.14 (Ubuntu 12.14-1.pgdg18.04+1)

Please help me on this issue.


Show us the complete command you ran, pus all output.

--
Born in Arizona, moved to Babylonia.

Getting error while upgrading postgres from version 12 to 13

[shashidhar Reddy]

Hello,

I am in a process of upgrading postgresql from version 12 to 13 using
pg_upgrdrade but I am getting error stating

 FATAL:  database files are incompatible with server
 DETAIL:  The data directory was initialized by PostgreSQL version 13
, which is not compatible with this version 12.14 (Ubuntu
12.14-1.pgdg18.04+1)

Please help me on this issue.
-- 
Shashidhar

Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

[Adrian Klaver]

On 7/17/21 10:13 AM, David Gauthier wrote:
Please reply to list also.
Ccing list.


That was it.  I added...

   if (proj_csv is null) then
     return(1);
   end if;

... and it works fine.

Back in earlier versions of PG (I'm talking v8), existing column values 
weren't checked.  I remember you had to manually run a query using your 
stored procedure to see if there would be any existing violations before 
creating the constraint.  The way it is now is an improvement, much better.


Glad it worked. Just be aware that using a function as a check 
constraint is skirting the rules for CHECK as explained here:


https://www.postgresql.org/docs/current/sql-createtable.html

There have been multiple posts to --general where that has come back to 
bite someone. Generally because the function reaches out to some other 
object in the database, which may or may not be there later. To me your 
particular function looks fairly benign, still you are standing close to 
the edge:)




Thanks Adrian.

On Fri, Jul 16, 2021 at 6:30 PM Adrian Klaver > wrote:


On 7/16/21 3:26 PM, David Gauthier wrote:
 > This stored procedure ...

 > dvdb=# alter table projects add constraint
validate_sibling_project_csv
 > check (validate_proj_csv(sibling_project_csv) = 0);
 > ERROR:  upper bound of FOR loop cannot be null
 > CONTEXT:  PL/pgSQL function validate_proj_csv(character varying)
line 14
 > at FOR with integer loop variable
 >
 > What's going on ?

I'm going to say you have a NULL value in sibling_project_csv in the
table.

 > How to get this to work ?
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

[Alban Hertroys]

> On 17 Jul 2021, at 0:26, David Gauthier  wrote:

(…)

> dvdb=# select validate_proj_csv('sabin,strix2,ipu1.0');
>  validate_proj_csv 
> ---
>  1
> (1 row)
> 
> dvdb=# select validate_proj_csv('sabin,strix2,ipu1.00');
> NOTICE:  Project "ipu1.00" in project csv "sabin,strix2,ipu1.00" is not a 
> valid project.
>  validate_proj_csv 
> ---
>  0
> (1 row)
> 
> 
> But when I try to use it in a check constraint
> 
> dvdb=# alter table projects add constraint validate_sibling_project_csv check 
> (validate_proj_csv(sibling_project_csv) = 0);
> ERROR:  upper bound of FOR loop cannot be null
> CONTEXT:  PL/pgSQL function validate_proj_csv(character varying) line 14 at 
> FOR with integer loop variable
> 
> What's going on ?
> How to get this to work ?

As people advised you previously, you would probably be better off normalising 
your table.

For example, add a table for the links:

create table project_sibling (
project text not null
references public.projects(project)
on update cascade
on delete cascade
,   sibling text not null
references public.projects(project)
on update cascade
on delete cascade
,   primary key (project, sibling)
);

-- Populate it from public.projects initially
insert into project_sibling(project, sibling)
select p.project, s.sibling
  from public.projects p
 cross join lateral regex_split_to_table(project_csv, ',') s(sibling)
;

I had to make a few guesses there, as I don’t have your original table 
structure, but that’s the gist of it.

If that project_csv column gets populated by some external application, you 
could keep the link-table updated with insert/update/delete triggers.

Alternatively, a writable view replacing public.projects may be a possibility.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

[David G. Johnston]

On Fri, Jul 16, 2021 at 3:26 PM David Gauthier 
wrote:

> This stored procedure ...
>

> create or replace function validate_proj_csv (proj_csv varchar)
>

It is a function - I don't think you can used stored procedures in check
constraints...

>
> dvdb=# alter table projects add constraint validate_sibling_project_csv
> check (validate_proj_csv(sibling_project_csv) = 0);
>
ERROR:  upper bound of FOR loop cannot be null
> CONTEXT:  PL/pgSQL function validate_proj_csv(character varying) line 14
> at FOR with integer loop variable
>

I'm actually surprised this alter command worked at all since you are
violating a requirement for check constraints - namely that the expression
be immutable.  Your function, regardless of its declaration (which is
default volatile), is not immutable.

How to get this to work ?
>

You really need to re-write this as a trigger function.

David J.

Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

[Adrian Klaver]

On 7/16/21 3:26 PM, David Gauthier wrote:

This stored procedure ...


dvdb=# alter table projects add constraint validate_sibling_project_csv 
check (validate_proj_csv(sibling_project_csv) = 0);

ERROR:  upper bound of FOR loop cannot be null
CONTEXT:  PL/pgSQL function validate_proj_csv(character varying) line 14 
at FOR with integer loop variable


What's going on ?


I'm going to say you have a NULL value in sibling_project_csv in the table.


How to get this to work ?




--
Adrian Klaver
adrian.kla...@aklaver.com

Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

[David Gauthier]

This stored procedure ...

create or replace function validate_proj_csv (proj_csv varchar)
returns int
language plpgsql
as
$$

-- This function used in a check constraint in the public.projects table to
ensure that
-- all projects in column sibling_project_csv are valid projects.

DECLARE
  proj_arr varchar[];
  see_prj int;

BEGIN

  proj_arr := regexp_split_to_array(proj_csv,',');

  for x in 1 .. array_upper(proj_arr,1)
  loop
select 1 into see_prj from public.projects where project = proj_arr[x];
if (see_prj is null) then
  raise notice 'Project "%" in project csv "%" is not a valid
project.', proj_arr[x],proj_csv;
  return 0;
end if;

  end loop;

  return 1;

END;
$$ ;

... works fine...

dvdb=# select validate_proj_csv('sabin,strix2,ipu1.0');
 validate_proj_csv
---
 1
(1 row)

dvdb=# select validate_proj_csv('sabin,strix2,ipu1.00');
NOTICE:  Project "ipu1.00" in project csv "sabin,strix2,ipu1.00" is not a
valid project.
 validate_proj_csv
---
 0
(1 row)


But when I try to use it in a check constraint

dvdb=# alter table projects add constraint validate_sibling_project_csv
check (validate_proj_csv(sibling_project_csv) = 0);
ERROR:  upper bound of FOR loop cannot be null
CONTEXT:  PL/pgSQL function validate_proj_csv(character varying) line 14 at
FOR with integer loop variable

What's going on ?
How to get this to work ?

RE: Getting error on

[M Tarkeshwar Rao]

Thanks for reply.

Can you please suggest why this error occur?
Any resource crunch on postgres server?

Regards
Tarkeshwar
From: Ron 
Sent: Monday, June 15, 2020 7:17 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Getting error on

On 6/15/20 8:43 AM, M Tarkeshwar Rao wrote:

Hi all,

I have the c++ postgres client. When I restarted my client process I am getting 
following error on insertion.

Can you please suggest why this error is coming and how to resolve it?

The error hint says how to resolve it.

2020-04-17 06:30:04.498 PRINT_DEFAULT_ERROR: Processor-DefaultCollectorGroup-0 
:DRViewerPoint.cc :notify --> Failed to insert the data
ERROR:  index "1_rasciistruct_thefields_2_pkey" contains unexpected zero page 
at block 291
HINT:  Please REINDEX it.

REINDEX INDEX 1_rasciistruct_thefields_2_pkey;
--
Angular momentum makes the world go 'round.

Re: Getting error on

[Ron]

On 6/15/20 8:43 AM, M Tarkeshwar Rao wrote:


Hi all,

I have the c++ postgres client. When I restarted my client process I am 
getting following error on insertion.


Can you please suggest why this error is coming and how to resolve it?



The error hint says how to resolve it.


2020-04-17 06:30:04.498 PRINT_DEFAULT_ERROR: 
Processor-DefaultCollectorGroup-0 :DRViewerPoint.cc :notify --> Failed to 
insert the data


ERROR:  index "1_rasciistruct_thefields_2_pkey" contains unexpected zero 
page at block 291


HINT:  Please REINDEX it.



REINDEX INDEX 1_rasciistruct_thefields_2_pkey;

--
Angular momentum makes the world go 'round.

Getting error on

[M Tarkeshwar Rao]

Hi all,

I have the c++ postgres client. When I restarted my client process I am getting 
following error on insertion.

Can you please suggest why this error is coming and how to resolve it?

2020-04-17 06:30:04.498 PRINT_DEFAULT_ERROR: Processor-DefaultCollectorGroup-0 
:DRViewerPoint.cc :notify --> Failed to insert the data
ERROR:  index "1_rasciistruct_thefields_2_pkey" contains unexpected zero page 
at block 291
HINT:  Please REINDEX it.

Regards
Tarkeshwar

Re: Getting error while running the pg_basebackup through PGBOUNCER

[Adrian Klaver]

On 4/8/19 7:19 AM, Raghavendra Rao J S V wrote:

Thank you very much for your prompt response.

Could you explain other admin type operations, which are not supported 
by pgbouncer?


I would say anything you could not run through psql.



Regards,
Raghavendra Rao.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Getting error while running the pg_basebackup through PGBOUNCER

[Raghavendra Rao J S V]

Thank you very much for your prompt response.

Could you explain other admin type operations, which are not supported by
pgbouncer?

Regards,
Raghavendra Rao.

On Mon, 8 Apr 2019 at 19:16, Scot Kreienkamp 
wrote:

> Replication and several other admin type operations must connect directly
> to PG.  They are not supported through PGBouncer.
>
>
>
> *From:* Raghavendra Rao J S V [mailto:raghavendra...@gmail.com]
> *Sent:* Monday, April 8, 2019 9:21 AM
> *To:* pgsql-general@lists.postgresql.org
> *Subject:* Getting error while running the pg_basebackup through PGBOUNCER
>
>
>
>
> *ATTENTION:   This email was sent to La-Z-Boy from an external source.
> Be vigilant when opening attachments or clicking links.*
>
> Hi All,
>
>
>
> We are using PGBOUNCER(connection pool mechanisam). PGBOUNCER uses port
> 5433.
>
>
>
> Postgres database port number is 6433. By using port 5433 PGBOUNCER is
> connecting to postgres port 6433 database.
>
>
>
> Now PGBOUNCER is establishing the connections properly but when I try to
> run the pg_basebackup through port 5433(PGBOUNCER port) we are receiving
> below error. Please guide me.
>
>
>
>
>
>  /opt/postgres/9.2/bin/pg_basebackup -p 5433 -U postgres -P -v -x
> --format=tar --gzip --compress=1 --pgdata=- -D /opt/rao
>
>
>
> *pg_basebackup: could not connect to server: ERROR:  Unsupported startup
> parameter: replication*
>
>
>
>
>
> --
>
> Regards,
> Raghavendra Rao J S V
>


-- 
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

RE: Getting error while running the pg_basebackup through PGBOUNCER

[Scot Kreienkamp]

Basically anything that is not written as a sql query should be connected 
directly to PG.  PGBouncer is really only meant for SQL query type connections.

From: Raghavendra Rao J S V [mailto:raghavendra...@gmail.com]
Sent: Monday, April 8, 2019 10:19 AM
To: Scot Kreienkamp 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Getting error while running the pg_basebackup through PGBOUNCER


ATTENTION:   This email was sent to La-Z-Boy from an external source. Be 
vigilant when opening attachments or clicking links.
Thank you very much for your prompt response.

Could you explain other admin type operations, which are not supported by 
pgbouncer?

Regards,
Raghavendra Rao.

On Mon, 8 Apr 2019 at 19:16, Scot Kreienkamp 
mailto:scot.kreienk...@la-z-boy.com>> wrote:
Replication and several other admin type operations must connect directly to 
PG.  They are not supported through PGBouncer.

From: Raghavendra Rao J S V 
[mailto:raghavendra...@gmail.com<mailto:raghavendra...@gmail.com>]
Sent: Monday, April 8, 2019 9:21 AM
To: 
pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Subject: Getting error while running the pg_basebackup through PGBOUNCER


ATTENTION:   This email was sent to La-Z-Boy from an external source. Be 
vigilant when opening attachments or clicking links.
Hi All,

We are using PGBOUNCER(connection pool mechanisam). PGBOUNCER uses port 5433.

Postgres database port number is 6433. By using port 5433 PGBOUNCER is 
connecting to postgres port 6433 database.

Now PGBOUNCER is establishing the connections properly but when I try to run 
the pg_basebackup through port 5433(PGBOUNCER port) we are receiving below 
error. Please guide me.


 /opt/postgres/9.2/bin/pg_basebackup -p 5433 -U postgres -P -v -x --format=tar 
--gzip --compress=1 --pgdata=- -D /opt/rao

pg_basebackup: could not connect to server: ERROR:  Unsupported startup 
parameter: replication


--
Regards,
Raghavendra Rao J S V


--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

Re: Getting error while running the pg_basebackup through PGBOUNCER

[Pavel Stehule]

po 8. 4. 2019 v 15:42 odesílatel Raghavendra Rao J S V <
raghavendra...@gmail.com> napsal:

> Hi All,
>
> We are using PGBOUNCER(connection pool mechanisam). PGBOUNCER uses port
> 5433.
>
> Postgres database port number is 6433. By using port 5433 PGBOUNCER is
> connecting to postgres port 6433 database.
>
> Now PGBOUNCER is establishing the connections properly but when I try to
> run the pg_basebackup through port 5433(PGBOUNCER port) we are receiving
> below error. Please guide me.
>
>
>  /opt/postgres/9.2/bin/pg_basebackup -p 5433 -U postgres -P -v -x
> --format=tar --gzip --compress=1 --pgdata=- -D /opt/rao
>
> *pg_basebackup: could not connect to server: ERROR:  Unsupported startup
> parameter: replication*
>

looks like pgbouncer cannot to forward connection parameter "replication"

Why you need pgbouncer for pg_basebackup? It has not too mach sense.

Pavel


>
> --
> Regards,
> Raghavendra Rao J S V
>
>

RE: Getting error while running the pg_basebackup through PGBOUNCER

[Scot Kreienkamp]

Replication and several other admin type operations must connect directly to 
PG.  They are not supported through PGBouncer.

From: Raghavendra Rao J S V [mailto:raghavendra...@gmail.com]
Sent: Monday, April 8, 2019 9:21 AM
To: pgsql-general@lists.postgresql.org
Subject: Getting error while running the pg_basebackup through PGBOUNCER


ATTENTION:   This email was sent to La-Z-Boy from an external source. Be 
vigilant when opening attachments or clicking links.
Hi All,

We are using PGBOUNCER(connection pool mechanisam). PGBOUNCER uses port 5433.

Postgres database port number is 6433. By using port 5433 PGBOUNCER is 
connecting to postgres port 6433 database.

Now PGBOUNCER is establishing the connections properly but when I try to run 
the pg_basebackup through port 5433(PGBOUNCER port) we are receiving below 
error. Please guide me.


 /opt/postgres/9.2/bin/pg_basebackup -p 5433 -U postgres -P -v -x --format=tar 
--gzip --compress=1 --pgdata=- -D /opt/rao

pg_basebackup: could not connect to server: ERROR:  Unsupported startup 
parameter: replication


--
Regards,
Raghavendra Rao J S V

Getting error while running the pg_basebackup through PGBOUNCER

[Raghavendra Rao J S V]

Hi All,

We are using PGBOUNCER(connection pool mechanisam). PGBOUNCER uses port
5433.

Postgres database port number is 6433. By using port 5433 PGBOUNCER is
connecting to postgres port 6433 database.

Now PGBOUNCER is establishing the connections properly but when I try to
run the pg_basebackup through port 5433(PGBOUNCER port) we are receiving
below error. Please guide me.


 /opt/postgres/9.2/bin/pg_basebackup -p 5433 -U postgres -P -v -x
--format=tar --gzip --compress=1 --pgdata=- -D /opt/rao

*pg_basebackup: could not connect to server: ERROR:  Unsupported startup
parameter: replication*


-- 
Regards,
Raghavendra Rao J S V

Re: pg_rewind success even though getting error 'record with incorrect prev-link'

[Abdullah Al Maruf]

> The only *error* I see is when you apparently manually kill the process.

You mean walreceiver process?? 'FATAL: terminating walreceiver process due
to administrator command' ?
Actually, I didn't kill the receiver. It is done by postgres itself, as far
as I understand.

I restart this node using a replica script. You can see the script here.
https://github.com/kubedb/postgres/blob/pg-db/hack/docker/postgres/9.6.7/scripts/replica/run.sh
It then gives the above error, then stops streaming from primary (or, you
can see the wal receiver is terminated by itself).

If I restart this node again, It throws following error:

LOG:  skipping missing configuration file "/etc/config/user.conf"
LOG:  skipping missing configuration file "/etc/config/user.conf"
LOG:  0: database system was shut down in recovery at 2019-01-23
05:12:17 UTC
LOG:  0: entering standby mode
LOG:  0: invalid resource manager ID in primary checkpoint record
PANIC:  XX000: could not locate a valid checkpoint record
LOG:  0: startup process (PID 33) was terminated by signal 6
LOG:  0: aborting startup due to startup process failure
LOG:  0: database system is shut down

On Wed, Jan 30, 2019 at 12:02 PM Ron  wrote:

> The only *error* I see is when you apparently manually kill the process.
> The LOG messages aren't actually errors.
>
> --
> Angular momentum makes the world go 'round.
>

Re: pg_rewind success even though getting error 'record with incorrect prev-link'

[Abdullah Al Maruf]

Hi Michael

> This is pointing out to the end of WAL for the current timeline.  You
> may face it after reading a WAL segment in an area which has been used
> in the past for a recycled segment.

Are you talking about error ` LOG:  invalid record length at 0/B98:
wanted 24, got 0` ?
or,
 `LOG:  record with incorrect prev-link 1/21B at 0/B98`

Actually, the 1st error is not making any issue. This node starts to
streaming from primary successfully.
But when the second error comes, It appears every 5 seconds. And, the node
is not streaming from master.

pg_rewind still resolves timeline conflict, but it's not fixing this second
error.

Any work around??

My scenario, in short, I have 1 master nodes (0th node) and three standby
nodes (1st,

2nd & 3rd node). When I make the 3rd node as master (by trigger file) and
restarts 0th node as a replica, It shows no problem.

But when both nodes are offline and our leader selection chooses the 0th
node
as a master, and tries to reattach the 3rd node as Replica, It throws an
error similar to:

```
LOG: invalid record length at 0/B98: wanted 24, got 0
LOG: started streaming WAL from primary at 0/B00 on timeline 2
LOG: record with incorrect prev-link 1/21B at 0/B98
FATAL: terminating walreceiver process due to administrator command
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
```

If I disable archive_mode, I never faced this error with the same script. It
only appears when archive is on, and also not all the times it happens but
most of the time it does.
The error message appears after every 5 seconds.

Scenario In details:

I have two folders for scripts.

├── primary
│ ├── postgresql.conf
│ ├── restore.sh
│ ├── run.sh
│ └── start.sh
└── replica
├── recovery.conf
└── run.sh

I have a system that will choose the leader. If the current pod is the
leader,
it will run `primary/run.sh`, and If it is a replica, it will run
`replica/run.sh`.

The files can be found here.

https://github.com/kubedb/postgres/tree/pg-db/hack/docker/postgres/9.6.7/scripts

On Wed, Jan 30, 2019 at 7:44 AM Michael Paquier  wrote:

> On Tue, Jan 29, 2019 at 07:13:11PM +0600, Abdullah Al Maruf wrote:
> > When I try to attach an old master with 'archiving set to on` as a new
> > standby, `pg_rewind` doesn't throw any error, But, when the database
> > starts, The following error appears:
> >
> > ```
> > LOG:  invalid record length at 0/B98: wanted 24, got 0
> > LOG:  started streaming WAL from primary at 0/B00 on timeline 2
> > LOG:  record with incorrect prev-link 1/21B at 0/B98
> > FATAL:  terminating walreceiver process due to administrator command
>
> This is pointing out to the end of WAL for the current timeline.  You
> may face it after reading a WAL segment in an area which has been used
> in the past for a recycled segment.
> --
> Michael
>

Re: pg_rewind success even though getting error 'record with incorrect prev-link'

[Michael Paquier]

On Tue, Jan 29, 2019 at 07:13:11PM +0600, Abdullah Al Maruf wrote:
> When I try to attach an old master with 'archiving set to on` as a new
> standby, `pg_rewind` doesn't throw any error, But, when the database
> starts, The following error appears:
> 
> ```
> LOG:  invalid record length at 0/B98: wanted 24, got 0
> LOG:  started streaming WAL from primary at 0/B00 on timeline 2
> LOG:  record with incorrect prev-link 1/21B at 0/B98
> FATAL:  terminating walreceiver process due to administrator command

This is pointing out to the end of WAL for the current timeline.  You
may face it after reading a WAL segment in an area which has been used
in the past for a recycled segment.
--
Michael


signature.asc
Description: PGP signature

pg_rewind success even though getting error 'record with incorrect prev-link'

[Abdullah Al Maruf]

When I try to attach an old master with 'archiving set to on` as a new
standby, `pg_rewind` doesn't throw any error, But, when the database
starts, The following error appears:

```
LOG:  invalid record length at 0/B98: wanted 24, got 0
LOG:  started streaming WAL from primary at 0/B00 on timeline 2
LOG:  record with incorrect prev-link 1/21B at 0/B98
FATAL:  terminating walreceiver process due to administrator command
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
```