Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

[Yogesh Sharma]

>>What you could do is copying its contents to a large disk, and then allow
it to recover from the crash.
 I will copy the PGDATA into large disk. After that it is require to
execute some specific command or automatically recovery will start?
If any command is require to execute please let me know.
Regards,
Yogesh

On Thursday, September 14, 2017, Michael Paquier 
wrote:

> On Thu, Sep 14, 2017 at 12:44 PM, John R Pierce  > wrote:
> > prior to that error, something else catastrophic must have happened to
> the
> > system, that error is more of a side effect. recovering a database server
> > that far gone which is running such an obsolete version will likely be an
> > expensive proposition. before doing anything, you should make a complete
> > backup of the $PGDATA directory (and other tablespace directories, if you
> > use any).
>
> Definitely take a backup of PGDATA before doing anything! What you
> could do is copying its contents to a large disk, and then allow it to
> recover from the crash. You are going to need more space at the end.
> And yes, upgrade as well. Lagging 7 major releases behind cannot be an
> excuse.
> --
> Michael
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

[Yogesh Sharma]

Dear all,

As current situation, i can not upgrade on higher version.

Is there any recovery command available?

Regards,
Yogesh

On Thursday, September 14, 2017, Gavin Flower <gavinflo...@archidevsys.co.nz>
wrote:

> On 14/09/17 15:29, Yogesh Sharma wrote:
>
>> Dear All,
>>
>> Thanks in advance.
>>
>> We are using Postgres 8.1.18 version.
>> In Postgres log, we found below logs.
>> –-
>> CONTEXT:writing block 0 of relation 1664/0/1260
>> ERROR: could not write block 0 of relation 1664/0/1260: Bad address
>>
>> 
>> Due to this pglog_Xlog directory has been continuously increased and
>> directory has been full and Postgres is stopped.
>> Please let me know how to recover this issue.
>>
>> Regards.
>> Yogesh
>>
>
> When you have recovered from that - I strong suggest that you consider
> upgrading to a supported version of Postgres like 9.6.5, as Postgres 8 is
> no longer supported and later versions have many bug fixes and
> enhancements!  Note that Postgres will be released in a few weeks.
>
>
> Cheers,
> Gavin
>
>
>

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

[Yogesh Sharma]

Dear All,

Thanks in advance.

We are using Postgres 8.1.18 version.
In Postgres log, we found below logs.
–-
CONTEXT:writing block 0 of relation 1664/0/1260
ERROR: could not write block 0 of relation 1664/0/1260: Bad address


Due to this pglog_Xlog directory has been continuously increased and
directory has been full and Postgres is stopped.
Please let me know how to recover this issue.

Regards.
Yogesh

Re: [GENERAL] Perl script is killed by SIGPIPE

[Yogesh Sharma]

Dear Daniel,
Yes , we are using DBI for connection.

Basically we observed that after dbh->do() return nothing if we are trying
to insert duplicate entry.
But it is not occurred always.
It return exit 1 if try to insert duplicate entry into Db.
But sometime it return nothing and child script is killed.

On Tuesday, September 12, 2017, Daniel Verite <dan...@manitou-mail.org>
wrote:

>     Yogesh Sharma wrote:
>
> > We have found child script is killed by signal 13 SIGPIPE. When duplicate
> > key violates error occured, script is killed but not all time.
>
> "child script" and this kind of error suggests that a forked process
> inherits a database connection opened by a parent process.
>
> When the database handle goes out of scope, it might
> close the connection to the database, affecting the
> parent process too, since it's the same connection.
>
> If you're using DBI, it has a setting to avoid that issue:
> https://metacpan.org/pod/DBI#InactiveDestroy
>
> Aside from that, inherited connections can't be used
> simultaneously by parent and child process.
> In general, a child process should open and close
> its own connection.
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>

[GENERAL] Perl script is killed by SIGPIPE

[Yogesh Sharma]

>
> Dear All,


We have one perl script that is opening DB connection and performaing
insert DB operation.When duplicate entry Comming in query,Postgres
forecfully killing process itself and in Postgres log "unexpected EOF on
client connection" error is Comming.
This issue is not Comming every time.
We have found child script is killed by signal 13 SIGPIPE. When duplicate
key violates error occured, script is killed but not all time.

Thanks in advance.

Yogesh

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear Albe,

Thanks for your support.

I observed there is some problem in REINDEX operation in older PostgreSQL
versions.
That why i want to add explicitly lock.

Regards,
Yogesh

On Wednesday, March 8, 2017, Albe Laurenz <laurenz.a...@wien.gv.at> wrote:

> Yogesh Sharma wrote:
> >  I want to apply explicitly lock mechanism once inset operation is in
> progress then REINDEX will wait.
> > And vice versa.
> > So, please let me know this type of handling is possible.
>
> Maybe I misunderstand something, but you don't need to do that because it
> happens automatically.
>
> If you run REINDEX, it will take an ACCESS EXCLUSIVE lock on the index
> before it starts its work.
>
> That means that it has to wait until all earlier INSERTing transactions are
> ended, and all INSERTs that come after the REINDEX will have to wait until
> the REINDEX is done.
>
> The database takes care that the data are consistent, so why would you
> want to do that explicitly?
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear Scott,

How to handle table with token?
How to implement this?

Regards,
Yogesh

On Wednesday, March 8, 2017, Scott Marlowe <scott.marl...@gmail.com> wrote:

> On Tue, Mar 7, 2017 at 11:55 PM, Scott Marlowe <scott.marl...@gmail.com
> <javascript:;>> wrote:
> > On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma <yogeshra...@gmail.com
> <javascript:;>> wrote:
> >> Dear David,
> >>
> >>  I want to apply explicitly lock mechanism once inset operation is in
> >> progress then REINDEX will wait.
> >> And vice versa.
> >> So, please let me know this type of handling is possible.
> >> Regrds,
> >> Yogesh
> >
> > Create two roles grant / revoke permissions as needed. maybe
> > pg_stat_activity for locks etc
>
> Also you could have a table with a simple token in it etc. active
> process gets token, all other processes wait on it.
>

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear David,

 I want to apply explicitly lock mechanism once inset operation is in
progress then REINDEX will wait.
And vice versa.
So, please let me know this type of handling is possible.
Regrds,
Yogesh

On Wednesday, March 8, 2017, David G. Johnston <david.g.johns...@gmail.com>
wrote:

> On Tuesday, March 7, 2017, Yogesh Sharma <yogeshra...@gmail.com
> <javascript:_e(%7B%7D,'cvml','yogeshra...@gmail.com');>> wrote:
>
>> Dear all,
>>
>> Thanks for your support.
>>
>> I need to perfrom INSERT and REINDEX operation exclusively.
>> For example:
>> If REINDEX operation is in progress then INSERT operation will wait  and
>> vice versa.
>>
>> Please let me know if any approach is available.
>>
>>
> The docs describe REINDEX locking mechanics.
>
> https://www.postgresql.org/docs/current/static/sql-reindex.html
>
> That a reindex blocks writes implies the reverse, any writes in progress
> will prevent the reindex from starting.
>
> David J.
>

[GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear all,

Thanks for your support.

I need to perfrom INSERT and REINDEX operation exclusively.
For example:
If REINDEX operation is in progress then INSERT operation will wait  and
vice versa.

Please let me know if any approach is available.

Regards,
Yogesh sharma

Re: [GENERAL] Request to share approach during REINDEX operation

[Yogesh Sharma]

Dear Steven,
Thanks for your reply.

Yes. You are correct.

Regards,
Yogesh

On Wednesday, December 21, 2016, Steven Winfield <
steven.winfi...@cantabcapital.com> wrote:

> > In my production system, there are lot of read write operation
> performed every hour.
>
>
>
> Apologies if this sounds patronising but I just wanted to check - you know
> that indexes are updated automatically when write operations occur, right?
>
>
>
> --
> *This email is confidential. If you are not the intended recipient, please
> advise us immediately and delete this message. The registered name of
> Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See -
> http://www.gam.com/en/Legal/Email+disclosures+EU
>  for further information
> on confidentiality, the risks of non-secure electronic communication, and
> certain disclosures which we are required to make in accordance with
> applicable legislation and regulations. If you cannot access this link,
> please notify us by reply message and we will send the contents to
> you.-- *
>

Re: [GENERAL] Request to share approach during REINDEX operation

[Yogesh Sharma]

Dear Vick,

I have responded on previously mail.

In my production system, there are lot of read write operation performed
every hour.

Regards,
yogesh
On Wednesday, December 21, 2016, Vick Khera <vi...@khera.org> wrote:

> On Wed, Dec 21, 2016 at 4:02 AM, Yogesh Sharma <yogeshra...@gmail.com
> <javascript:;>> wrote:
> > Also, every hour,i am performing VACUUM and REINDEX operation on table.
>
> Why?
>

Re: [GENERAL]

[Yogesh Sharma]

Dear All,

I understood below point and i will do the same on my system.

Can i close db session before REINDEX operation and again start db session
after REINDEX completed?


Regards,
Yogesh

On Wednesday, December 21, 2016, Jaime Soler <jaime.so...@gmail.com> wrote:

> if you want to reduce the impact of reindex in your inserts and updates
> operations why don't you try drop index and  CREATE INDEX CONCURRENTLY.
> As Alban said you I don't recommend you to execute reindex every hour, at
> least try to reduce the scope of the reindex to the minimal object.
>
>
>
>
>
> 2016-12-21 12:51 GMT+01:00 Yogesh Sharma <yogeshra...@gmail.com
> <javascript:_e(%7B%7D,'cvml','yogeshra...@gmail.com');>>:
>
>> Dear Alban,
>>
>> In my production system, there are lot of read write operation performed
>> every hour.
>> So, i am thinking, if i can add check during REINDEX operation nothing
>> update and insert operation performed.
>> Is it possible?
>>
>> Regards,
>> Yogesh
>>
>> On Wednesday, December 21, 2016, Alban Hertroys <haram...@gmail.com
>> <javascript:_e(%7B%7D,'cvml','haram...@gmail.com');>> wrote:
>>
>>> On 21 December 2016 at 09:59, Yogesh Sharma <yogeshra...@gmail.com>
>>> wrote:
>>> > Also, every hour,i am performing VACUUM and REINDEX operation on table.
>>>
>>> Why are you running REINDEX every hour? That's a very unusual thing to
>>> do, you'd need a pretty good reason for that.
>>>
>>> --
>>> If you can't see the forest for the trees,
>>> Cut the trees and you'll see there is no forest.
>>>
>>
>

Re: [GENERAL]

[Yogesh Sharma]

Dear Alban,

In my production system, there are lot of read write operation performed
every hour.
So, i am thinking, if i can add check during REINDEX operation nothing
update and insert operation performed.
Is it possible?

Regards,
Yogesh

On Wednesday, December 21, 2016, Alban Hertroys <haram...@gmail.com> wrote:

> On 21 December 2016 at 09:59, Yogesh Sharma <yogeshra...@gmail.com
> <javascript:;>> wrote:
> > Also, every hour,i am performing VACUUM and REINDEX operation on table.
>
> Why are you running REINDEX every hour? That's a very unusual thing to
> do, you'd need a pretty good reason for that.
>
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>

[GENERAL] Fwd: Request to share approach during REINDEX operation

[Yogesh Sharma]

Adding subject line


Dear All,

Thanks for your support.
I am using postgresql 9.3.6 on RHEL6.6 machine.
In my production environment, Some script are executing automatically
through cron.
These script are performing insert and update operation.

Also, every hour,i am performing VACUUM and REINDEX operation on table.

Is there any solution available to perform only REINDEX operation and other
insert or updation operations from other script will not perform during
REINDEX execution?
  OR
Preferably, during REINDEX operation, other script execution for
insert/update may wait until reindex will not completed.

Regards,
Yogesh

[GENERAL]

[Yogesh Sharma]

Dear All,

Thanks for your support.
I am using postgresql 9.3.6 on RHEL6.6 machine.
In my production environment, Some script are executing automatically
through cron.
These script are performing insert and update operation.

Also, every hour,i am performing VACUUM and REINDEX operation on table.

Is there any solution available to perform only REINDEX operation and other
insert or updation operations from other script will not perform during
REINDEX execution?
  OR
Preferably, during REINDEX operation, other script execution for
insert/update may wait until reindex will not completed.

Regards,
Yogesh

Re: [GENERAL] Query regarding deadlock

[Yogesh Sharma]

Dear John,

Thanks for your support.
I mean to say, the REINDEX calls hourly and insert query executes every minute 
to update.
So, it might be race condition that these queries can call at same time.

If there is any solution like we can add some check before REINDEX operation 
performed.
If it is possible?

Regards,
Yogesh

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Friday, November 25, 2016 10:55 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query regarding deadlock

On 11/24/2016 5:44 PM, Yogesh Sharma wrote:
> I cannot change calling of REINDEX and insert query sequence because it is 
> execute automatically through some cron script.

any cron scripts are your own doing, so this statement makes no sense at all.


-- 
john r pierce, recycling bits in santa cruz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Query regarding deadlock

[Yogesh Sharma]

Dear All,

Thanks in advance.
I found below deadlock in postgresql logs.
I cannot change calling of REINDEX and insert query sequence because it is 
execute automatically through some cron script.

ERROR:  deadlock detected
 DETAIL:  Process 2234 waits for AccessShareLock on relation 16459 of database 
16385; blocked by process 4111.
 Process 4111 waits for ShareLock on relation 16502 of database 16385; blocked 
by process 2234.
 Process 2234: INSERT INTO table1 ( id , unique_id )VALUES( '1', '4')
 Process 4111: REINDEX TABLE table1

Could you please provide any solution to resolve this deadlock.

Regards,
Yogesh

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Request to share information regarding deadlock in postgresql-9.3.6

[Yogesh Sharma]

Dear John,

Thanks for sharing solution approaches.

>do each reindex as a seperate transaction so only one table gets locked at a 
>time.
Transaction is already in separate like REINDEX TABLE table1, REINDEX TABLE  
table2 etc..
But this problem is occurred.

One more question regarding below.
alter table table1 add constraint fk_key foreign key (id, roll_number) 
references table2 (id, roll_number) on delete restrict on update restrict;
It shows below error:
ERROR:  there is no unique constraint matching given keys for referenced table 
"table2"

Table1 contains below structure:
create table table1
(
   idchar(6) not null,
   roll_number   varchar(20) not null,
-
primary key (id, roll_number)

Table2 contains below structure:

create table table2
(
   idchar(6) not null,
   roll_number   varchar(20) not null,
account_id  varchar(20) not null default '-',
-
primary key (id, roll_number, account_id)

How to resolve this issue?

Regards,
Yogesh

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, November 17, 2016 12:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock in 
postgresql-9.3.6

On 11/16/2016 6:22 PM, Yogesh Sharma wrote:
> process 5764 is waiting for relation (table) with OID 
> 16459(table2_primary_key), that table is blocked by process 4970 and process 
> 4970 is waiting for a lock on another table, OID 16502(table1), which the 
> first process has a lock on.
> Process 5764: INSERT INTO table2 ( id , roll_number, name) VALUES( 
> '1', '4','abc' ) Process 4970: REINDEX TABLE table1, table2 etc..
>
> How to resolve this problem?

don't do reindex when the tables are in use.

or

why does process 5764 have a lock on table 1 while its inserting into table 2?

or

do each reindex as a seperate transaction so only one table gets locked at a 
time.

--
john r pierce, recycling bits in santa cruz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Request to share information regarding deadlock in postgresql-9.3.6

[Yogesh Sharma]

Dear All,

Thanks for your support.

Could you please share your opinion for  deadlock resolution.
Process 5764 waits for AccessShareLock on relation 16459 of database 16385; 
blocked by process 4970.
 Process 4970 waits for ShareLock on relation 16502 of database 16385; blocked 
by process 5764.

It means we have two processes that are each waiting for locks the other have.

process 5764 is waiting for relation (table) with OID 
16459(table2_primary_key), that table is blocked by process 4970 and process 
4970 is waiting for a lock on another table, OID 16502(table1), which the first 
process has a lock on.
Process 5764: INSERT INTO table2 ( id , roll_number, name) VALUES( '1', 
'4','abc' )
Process 4970: REINDEX TABLE table1, table2 etc..

How to resolve this problem?


Regards,
Yogesh



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Request to share information regarding errors

[Yogesh Sharma]

Dear Charles,

Thanks for sharing such information.
First thing, I want to confirm.
>You are trying to insert a row in a table that has a not-null constraint on a 
>field.
>Find the record that causes the error and include a valid value for the column 
>that has the not null constraint.
Entry is already deleted from table and value is defined not null in create 
table query.
But this type of error is not displaying every time.

>A foreign key must match the primary key of the table it refers to. So you 
>should create the primary key contraint on table2 using id and phone_id.
At this time, DB is shutdown.
This shutdown is related to this error or not?


Regards,
Yogesh

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles Clavadetscher
Sent: Wednesday, November 16, 2016 3:44 PM
To: Yogesh Sharma; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding errors

Good morning

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Yogesh Sharma
> Sent: Mittwoch, 16. November 2016 07:09
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding errors
> 
> Dear All,
> 
> Thanks for your support.
> 
> I am using postgresql 9.3.6 rpm in RHEL6 version.
> 
> In postgresql logs, I have found below error messages.
> ERROR:  null value in column "column name" violates not-null constraint.

You are trying to insert a row in a table that has a not-null constraint on a 
field.
Find the record that causes the error and include a valid value for the column 
that has the not null constraint.

> Second error found:
> ERROR:  there is no unique constraint matching given keys for referenced 
> table "table2"
> alter table1 table_name add constraint fk_key foreign key (id, 
> phone_id) references table2 (id, phone_id) on delete restrict on 
> update restrict;

A foreign key must match the primary key of the table it refers to. So you 
should create the primary key contraint on table2 using id and phone_id.

CREATE UNIQUE INDEX table2_pkey ON table2 (id, phone_id); ALTER TABLE table2 
ADD PRIMARY KEY USING INDEX table2_pkey;

After that you should be able to add the foreign key to table1.

Hope this helps.
Charles

> Could you please share the details of these errors and how to resolve these 
> errors from logs.
> 
> 
> Regards,
> Yogesh
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Request to share information regarding errors

[Yogesh Sharma]

Dear All,

Thanks for your support.

I am using postgresql 9.3.6 rpm in RHEL6 version.

In postgresql logs, I have found below error messages.
ERROR:  null value in column "column name" violates not-null constraint.

Second error found:
ERROR:  there is no unique constraint matching given keys for referenced table 
"table2"
alter table1 table_name add constraint fk_key foreign key (id, phone_id) 
references table2 (id, phone_id) on delete restrict on update restrict;

Could you please share the details of these errors and how to resolve these 
errors from logs.


Regards,
Yogesh


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Request to share information regarding deadlock in postgresql-8.1.18

[Yogesh Sharma]

Dear John,

Thanks for sharing information.
>this is a logic error in your application.
Ok, I will check.

>you can look those relation numbers up in the pg_catalog to see what they are. 
>  you can see what the processes are in pg_stat_activity.
Currently machine is not available. Please suggest if any other approach  to 
identify the same.


>THis has nothing to do with the growing WAL logs...something is blocking 
>checkpoints if a single WAL file keeps >growing, are you using some form of 
>wal archiving, is that working correctly ? could something 
>be preventing checkpoints?what are the related checkpoint and WAL settings?

Many WAL files are generated in pg_xlog directory. I don’t know how I can check 
checkpoint and WAL settings?
By default setting of postgresql.conf is used.

Regards,
Yogesh



-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Monday, November 14, 2016 4:19 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock in 
postgresql-8.1.18

On 11/13/2016 11:01 PM, Yogesh Sharma wrote:
> DETAIL:  Process 12345 waits for AccessShareLock on relation 16446 of 
> database 16385; blocked by process 23136.
> Process 23136 waits for ShareLock on relation 16482 of database 16385; 
> blocked by process 12345.

you have two processes that are each waiting for locks the other have.

process 12345 is waiting for relation (table) with OID 16446, that table is 
blocked by process 23136

and process 23136 is waiting for a lock on another table, OID 16482, which the 
first process has a lock on.

this is a logic error in your application.

you can look those relation numbers up in the pg_catalog to see what 
they are.   you can see what the processes are in pg_stat_activity.

THis has nothing to do with the growing WAL logs...something is 
blocking checkpoints if a single WAL file keeps growing, are you using some 
form of wal archiving, is that working correctly ? could something 
be preventing checkpoints?what are the related checkpoint and WAL 
settings?



-- 
john r pierce, recycling bits in santa cruz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Request to share information regarding deadlock in postgresql-8.1.18

[Yogesh Sharma]

Dear All,

Thanks in advance.

In my Linux system, below error is found and after 10~12 hours, WAL file in 
pg_xlog directory are increased continuously.
PostgreSQL:ERROR:  deadlock detected
DETAIL:  Process 12345 waits for AccessShareLock on relation 16446 of database 
16385; blocked by process 23136.
Process 23136 waits for ShareLock on relation 16482 of database 16385; blocked 
by process 12345.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."table name" x WHERE "id" 
= $1 AND "table_id" = $2 FOR SHARE OF x"

Is this deadlock error is link with WAL file of pg_xlog?
Please share your opinion. 

Regards,
Yogesh

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgresql-8.1.18-2.1 information

[Yogesh Sharma]

Dear All,

Thanks for your support.

I need to take source rpm for below version for rhel.
postgresql-8.1.18-2.1

Could you please share link, so that I will take.
I have tried to search but could not found.

Regards,
Yogesh

Re: [GENERAL] Request to share information regarding postgresql pg_xlog file.

[Yogesh Sharma]

Dear John,

Thanks for your support.

>as far as how do you determine whats wrong with your file system?
I tried fsck and hardware check using SMART disk info, no issue found with disk 
or filesystem.

>what file system are you using for the volume containing the postgres data 
>directory ?  with RHEL5, you were pretty much limited to EXT3, I guess ?
File system is ext3 and mount with sync type (rw,sync,dirsync,noatime).

>It would probably be a good idea to unmount the volume and fsck it.  also 
>check your system logs for any disk IO errors.
I did that, fsck runs fine no issue.

>is this storage on a raid controller, or using software raid, or just a simple 
>file system on a single disk, or what?
We are using a simple files system which is mirrored at block level.

>desktop/consumer disk drives are notorious for lying about writeback caching, 
>telling the software the data is written when its still in a cache on the 
>drive... if the power fails before the data actually gets written to disk with 
>one of these, you can lose stuff.

Sync mode of file system ensures that, data is continuously flushed on disk as 
soon as write system call initiated on file system.


Thanks,
Yogesh

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, September 15, 2016 1:24 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding postgresql 
pg_xlog file.

On 9/15/2016 12:25 AM, Yogesh Sharma wrote:
Dear John,

Thanks for your support.

Please find below name of rpm.
RPMS/postgresql-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-devel-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-libs-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-python-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-server-8.1.18-2.1.x86_64.rpm

We are using redhat Enterprise Linux 5.8.


ok, those RPM's were built and packaged by Redhat, I believe.   If you have a 
RHEL support contract, you should be able to get help from them.  If you don't, 
you really shouldn't be running RHEL as there's no updates available without 
one.


as far as how do you determine whats wrong with your file system?   I don't 
know how you'd narrow that down, but postgres expected a file to be there, and 
it wasn't. what file system are you using for the volume containing the 
postgres data directory ?  with RHEL5, you were pretty much limited to EXT3, I 
guess ?  It would probably be a good idea to unmount the volume and fsck 
it.  also check your system logs for any disk IO errors.   is this storage on a 
raid controller, or using software raid, or just a simple file system on a 
single disk, or what?desktop/consumer disk drives are notorious for lying 
about writeback caching, telling the software the data is written when its 
still in a cache on the drive... if the power fails before the data actually 
gets written to disk with one of these, you can lose stuff.



--

john r pierce, recycling bits in santa cruz

Re: [GENERAL] Request to share information regarding postgresql pg_xlog file.

[Yogesh Sharma]

Dear John and all,

>8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was 
>released in 2009, the final 8.1.23 release was in 2010, after which it was 
>>dropped.
Yes, we understood your point.
But we require some information related to this rpm.

>These errors suggest disk file corruption, this can occur from unreliable 
>storage, undetected memory errors, and other such things.
How we can verify what is actual problem in system?

Also please share some information related to below.
we tried to stop the postgresql but it couldn’t stop and timout after 60 sec.
please confirm below message in postgre logs.
FATAL:  terminating connection due to administrator command


Regards,
Yogesh

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, September 15, 2016 11:28 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding postgresql 
pg_xlog file.

On 9/14/2016 10:09 PM, Yogesh Sharma wrote:
Thanks for your support and suggestion.
We are using below postgresql rpm.
postgresql-8.1.18-2.1

thats not the full RPM name, thats just the version.

8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was 
released in 2009, the final 8.1.23 release was in 2010, after which it was 
dropped.

current releases are 9.1 (soon to be obsoletted), 9.2, 9.3, 9.4, and 9.5, with 
9.6 in release candidate state.


CONTEXT:  writing block 53 of relation 1663/16385/280951
ERROR:  could not open relation 1663/16385/280951: No such file or directory


These errors suggest disk file corruption, this can occur from unreliable 
storage, undetected memory errors, and other such things.





--

john r pierce, recycling bits in santa cruz

[GENERAL] Request to share information regarding postgresql pg_xlog file.

[Yogesh Sharma]

Dear Team,

Thanks for your support and suggestion.
We are using below postgresql rpm.
postgresql-8.1.18-2.1

In our system, below error is found and occurring is very frequent.

CONTEXT:  writing block 53 of relation 1663/16385/280951
ERROR:  could not open relation 1663/16385/280951: No such file or directory

Due to this, size of pg_xlog is increasing continuous.
Could you please share what is possible cause of this issue.
And how we can resolve this issue.

Also , we tried to stop the postgresql but it couldn’t stop and timout after 60 
sec.
please confirm below message in postgre logs.
FATAL:  terminating connection due to administrator command

Regards,
Yogesh

Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

[Yogesh Sharma]

Dear David sir/All,

Thanks for your help.
Just wanted to confirm few things.

(9.3.5,9.2.9,9.1.14,9.0.18,8.4.22) Fix REASSIGN OWNED to not fail for text 
search objects (Álvaro Herrera)

As per my understanding from what you told the fix was done in 9.3.5 and then 
back patched to all the remaining version mentioned above.
My concern is why it was back patched to these particular version only.
Also will these feature be available only in these particular version and above 
9.3.5 version or is it available in all the version above 8.4.22

This is not about this particular patch, I want to understand the multiple 
version number which are mentioned before several patch at given link.

https://bucardo.org/postgres_all_versions.html

Please let me know what yours thought onto this.


Regards,
Yogesh

From: David G. Johnston [mailto:david.g.johns...@gmail.com]
Sent: Wednesday, June 15, 2016 7:18 PM
To: Albe Laurenz
Cc: Yogesh Sharma; Adrian Klaver; Moreno Andreo; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

On Wed, Jun 15, 2016 at 3:31 AM, Albe Laurenz 
<laurenz.a...@wien.gv.at<mailto:laurenz.a...@wien.gv.at>> wrote:
Yogesh Sharma wrote:
> I have doubt regarding release notes of all versions.
> As per release notes, below change logs are mentioned in all versions.
>
> "(8.3.8,8.4.1,8.2.14) Make LOAD of an already-loaded loadable module into a 
> no-op (Tom Lane)"
> 1. What is meaning of above lines?
> 2. This changes are implemented only in above versions or is it propagated on 
> every above newer
> versions after that?
> 3. Why same line is mentioned in the release notes of above 
> 8.3.8,8.4.1,8.2.14 versions?

Ad 1:
As of the cited versions, nothing will be done if you use LOAD to load a module
that is already loaded.

Ad 2:
The change was a bugfix that was applied to the development version (so it is 
included
in 9.0 and later) and was backpatched to 8.2, 8.3 and 8.4.

​Just to be totally clear on this point.

8.2, 8.3, and 8.4 are all Major releases of the PostgreSQL product.  For most 
other products it would as if the numbering went from "15, to 16, to 17".  The 
last 8.x release was 8.4 (so, there were 5 major releases that all shared the 
same prefix value of 8) and the 9.x series goes from 9.0 to 9.6 (presently in 
beta).  Instead of 9.7 we will be going to 10.0 AND at the same time 
modernizing our numbering scheme to lose the prefix-suffix components.  IOW, 
after 10.0 the next major release will be 11.0.  The .0 will increment for 
minor releases in which we only apply bug-fixes.  There will no longer be a 
third number.

The rest of Albe's comments apply and are largely driven by this structure.  
Each major-release is considered stand-alone and so if the same basic change is 
made to more than one major release all of them will have it documented.  But 
only the initial introduction or application of the change is documented - any 
subsequent major or minor release has that behavior unless a new release note 
indicates that said behavior changed again.

David J.

Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

[Yogesh Sharma]

Dear All,

Thanks in advance.

I have doubt regarding release notes of all versions.
As per release notes, below change logs are mentioned in all versions.

"(8.3.8,8.4.1,8.2.14) Make LOAD of an already-loaded loadable module into a 
no-op (Tom Lane)"
1. What is meaning of above lines?
2. This changes are implemented only in above versions or is it propagated on 
every above newer versions after that?
3. Why same line is mentioned in the release notes of above 8.3.8,8.4.1,8.2.14 
versions?


Regards,
Yogesh
-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver
Sent: Friday, June 10, 2016 6:47 PM
To: Moreno Andreo; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

On 06/10/2016 04:23 AM, Moreno Andreo wrote:
> Hi Yogesh,
> here
>
> https://www.postgresql.org/docs/9.3/static/release.html
>
> you can find all release notes, including changelogs for all versions.

Also a one page version:

https://bucardo.org/postgres_all_versions.html

>
> HTH,
> Moreno.
>
> Il 10/06/2016 13:17, Yogesh Sharma ha scritto:
>>
>> Dear All,
>>
>>
>>
>> Thanks for your support.
>>
>>
>>
>> Could you please assist for changelog version option used to find
>> the differences between the versions.
>>
>> In addition  we required information of changes done in all
>> releases from 8.1.2 to 9.3.6.
>>
>>
>>
>> Thanks in advance .
>>
>>
>>
>> Regards,
>>
>> Yogesh
>>
>


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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Changelog version from 8.1.2 to 9.3.6

[Yogesh Sharma]

Dear All,

Thanks for your support.

Could you please assist for changelog version option used to find the 
differences between the versions.
In addition  we required information of changes done in all releases from 8.1.2 
to 9.3.6.

Thanks in advance .

Regards,
Yogesh

Re: [GENERAL] Issue during postgresql startup

[Yogesh Sharma]

Dear All,

Could you please update your suggestion on below mail

Regards,
Yogesh

From: Yogesh Sharma
Sent: Friday, April 29, 2016 3:57 PM
To: John R Pierce; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Issue during postgresql startup

Dear All,

I am sharing my further observation regarding the system behavior.


The errors related to this missing table file started coming from 2016-04-24 
20:37:11 till the end when pg_xlog filled up the disk space and postgres 
crashed eventually.


postgresql_log.Sun.gz:<%2016-04-24 20:37:11 GMT> ERROR:  could not open 
relation 1663/16385/1299988: No such file or directory


PostgreSQL:PANIC:  could not write to file "pg_xlog/xlogtemp.21498": No space 
left on device server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.


Furthermore, as a periodic maintenance REINDEXING of table executed at 
2016-04-24 20:33:05, Although table file is recreated at index reconstruction, 
for some reason, the recreated table file 1299988 got deleted, and probably led 
to this issue.



There is a difference of roughly 4 minutes between the REINDEXING and the point 
where this error start coming. I am trying to find out if there is any sort of 
relation between postgres and table file deletion (Probably some sort of bug in 
postgres ?).



As an experiment I tried to create a dummy file “1663/16385/1299988” using dd 
command and triggered postgres start but postgres delete the dummy file 
automatically. Due to this sort of behavior I am suspecting that the actual 
file created after REINDEX might be having some sort of corruption and postgres 
deleted that file automatically.



I’ll appreciate any kind of opinion on my understanding.



Thanks,

Yogesh Sharma
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, April 28, 2016 2:51 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue during postgresql startup

On 4/28/2016 1:54 AM, Yogesh Sharma wrote:

>do you have backups from before this occurred ?
No, there is no backup available for the same.


oops.



it is possible some of your tables can be recovered by someone highly 
experienced in postgres data recovery.  such people work for the various 
postgres consultancies, like 2nd Quadrant, and so forth.   I sure don't know 
the intricacies of doing this.8.3 is a really old obsolete version, too... 
its life cycle ran from Feb. 2008 to Feb 2013.







--

john r pierce, recycling bits in santa cruz



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---

Re: [GENERAL] Issue during postgresql startup

[Yogesh Sharma]

Dear All,

I am sharing my further observation regarding the system behavior.


The errors related to this missing table file started coming from 2016-04-24 
20:37:11 till the end when pg_xlog filled up the disk space and postgres 
crashed eventually.


postgresql_log.Sun.gz:<%2016-04-24 20:37:11 GMT> ERROR:  could not open 
relation 1663/16385/1299988: No such file or directory


PostgreSQL:PANIC:  could not write to file "pg_xlog/xlogtemp.21498": No space 
left on device server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.


Furthermore, as a periodic maintenance REINDEXING of table executed at 
2016-04-24 20:33:05, Although table file is recreated at index reconstruction, 
for some reason, the recreated table file 1299988 got deleted, and probably led 
to this issue.



There is a difference of roughly 4 minutes between the REINDEXING and the point 
where this error start coming. I am trying to find out if there is any sort of 
relation between postgres and table file deletion (Probably some sort of bug in 
postgres ?).



As an experiment I tried to create a dummy file “1663/16385/1299988” using dd 
command and triggered postgres start but postgres delete the dummy file 
automatically. Due to this sort of behavior I am suspecting that the actual 
file created after REINDEX might be having some sort of corruption and postgres 
deleted that file automatically.



I’ll appreciate any kind of opinion on my understanding.



Thanks,

Yogesh Sharma
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, April 28, 2016 2:51 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue during postgresql startup

On 4/28/2016 1:54 AM, Yogesh Sharma wrote:

>do you have backups from before this occurred ?
No, there is no backup available for the same.


oops.



it is possible some of your tables can be recovered by someone highly 
experienced in postgres data recovery.  such people work for the various 
postgres consultancies, like 2nd Quadrant, and so forth.   I sure don't know 
the intricacies of doing this.8.3 is a really old obsolete version, too... 
its life cycle ran from Feb. 2008 to Feb 2013.







--

john r pierce, recycling bits in santa cruz



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---

Re: [GENERAL] Issue during postgresql startup

[Yogesh Sharma]

Dear John,

>do you have backups from before this occurred ?
No, there is no backup available for the same.

Regards,
Yogesh
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, April 28, 2016 2:18 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue during postgresql startup

On 4/28/2016 1:33 AM, Yogesh Sharma wrote:
>did you get that 'no such file or directory' error before or after you reset 
>the transaction logs ?
The error message is coming before.

that means your file system lost files.there's a number of ways that can 
happen, mostly revolving around file systems with unreliable caching combined 
with a power failure, or physical hardware data corruption can do it too.   
mucking about in the postgres data file system can do it, too, of course.   do 
you have backups from before this occurred ?



>messing with the write-ahead logs is very dangerous.  that blog is talking 
>about postgres 8.4, based on his paths.   is that what you're running?
We are running postgres 8.3.

thats a completely obsolete version, 8.3.23 was last updated in early 2013.

current supported versions are 9.1, 9.2, 9.3, 9.4, and 9.5

current releases:  9.1.21, 9.2.16, 9.3.12, 9.4.7, and 9.5.2



--

john r pierce, recycling bits in santa cruz



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---

Re: [GENERAL] Issue during postgresql startup

[Yogesh Sharma]

Dear John,

>did you get that 'no such file or directory' error before or after you reset 
>the transaction logs ?
The error message is coming before.
>messing with the write-ahead logs is very dangerous.  that blog is talking 
>about postgres 8.4, based on his paths.   is that what you're running?
We are running postgres 8.3.

Regards,
Yogesh
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, April 28, 2016 1:44 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue during postgresql startup

On 4/28/2016 12:21 AM, Yogesh Sharma wrote:

Could you please tell me, how to recover my system.



I am facing below errors.

---

could not open relation 1663/16385/1299988: No such file or directory

---



This message is continuous occurred in system .



I have tried below procedure for the same but after recover this system, all DB 
tables are destroyed.

http://www.hivelogik.com/blog/?p=513

did you get that 'no such file or directory' error before or after you reset 
the transaction logs ?

messing with the write-ahead logs is very dangerous.  that blog is talking 
about postgres 8.4, based on his paths.   is that what you're running?

I'd be hesitant to rely on random blog advise, without thoroughly understanding 
what you're doing.




--

john r pierce, recycling bits in santa cruz



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---

Re: [GENERAL] Issue during postgresql startup

[Yogesh Sharma]

Dear Sameer and all,

>Looks like someone has been playing around with the database files or some 
>disk issue.
>Basically Postgres stores each table inside
>$tablespace_dir/$database_dir/table_file
>In this error it seems one of the files have been removed/lost

Yes, I am suspecting a disk error or so.
In that case I tried to add a dummy file “base/16385/1299988” using dd command. 
But while postgre start the file deleted automatically and it failed with same 
error.

Is there any way to recover from this error or take dump of data and recreate 
DB from same dump ?

BTW log files in pg_xlog also increased abruptly from the same time when this 
error started coming. Is it related to above error ?

Any help will be much appreciated !

Let me know if any other help is required.


Thanks,
Yogesh
From: Sameer Kumar [mailto:sameer.ku...@ashnik.com]
Sent: Thursday, April 28, 2016 1:45 PM
To: Yogesh Sharma; pgsql-general
Subject: Re: [GENERAL] Issue during postgresql startup


On Thu, 28 Apr 2016 15:22 Yogesh Sharma, 
<yogesh1.sha...@nectechnologies.in<mailto:yogesh1.sha...@nectechnologies.in>> 
wrote:
Dear All,

Thanks for your support.

Could you please tell me, how to recover my system.

I am facing below errors.
---
could not open relation 1663/16385/1299988: No such file or directory
---

This message is continuous occurred in system .


Looks like someone has been playing around with the database files or some disk 
issue.

Basically Postgres stores each table inside

$tablespace_dir/$database_dir/table_file

In this error it seems one of the files have been removed/lost



I have tried below procedure for the same but after recover this system, all DB 
tables are destroyed.
http://www.hivelogik.com/blog/?p=513

You might want to post the steps and backup file/date which you have used.

Please let me know if any solution.

Regards,
Yogesh



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only.
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have
received this email in error please delete it and notify the sender
immediately. .
---


--
Sent via pgsql-general mailing list 
(pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com<http://www.ashnik.com>



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---

[GENERAL] Issue during postgresql startup

[Yogesh Sharma]

Dear All,

Thanks for your support.

Could you please tell me, how to recover my system.

I am facing below errors.
---
could not open relation 1663/16385/1299988: No such file or directory
---

This message is continuous occurred in system .

I have tried below procedure for the same but after recover this system, all DB 
tables are destroyed.
http://www.hivelogik.com/blog/?p=513

Please let me know if any solution.

Regards,
Yogesh



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Select query regarding info

[Yogesh. Sharma]

HI Everyone,

Below DB query is showing below error on postgresql9.3.
SELECT '\'' || t2.name || '\'', '\'' || t1.phone_number || '\'', '\'' || 
t1.details || '\'', '\'' || t1.description || '\'', '\'' || (CASE WHEN t1.s_id 
IS NULL THEN 'N/A' ELSE t3.s_type END) || '\'', '\'' || t1.s_id || '\'' FROM 
abc_tble AS t1 LEFT JOIN pqrtable AS t2 ON t1.s_id = nid LEFT JOIN te AS t3 ON 
t1.s_id = t3.s_id;
Invalid command \''. Try \? for help.
But Above query is working fine in postgresql8.3.
Solution is provided by someone:-
The SQL standard defines two single quotes to escape one inside a literal: 
Postgres 8.3 defaulted to a non-standard behavior where it was allowed to 
escape a single quote using a backslash: '\''
This deviation from the SQL standard was always discouraged and can be 
controlled through the configuration parameter 
standard_conforming_stringshttp://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS
With version 9.1 the default for this parameter was changed from off to on. 
Version 8.1 and later would emit a warning when you used the non-standard way 
of escaping single quotes (unless you explicitly turned that off)


Could you please provide below information.
 How to change standard_conforming_strings value of postgresql.conf? I have 
checked but this option is not found in postgresql.conf.
Because according to this option, below query is failed.

Regards,
Yogesh

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Xavier 12
Sent: Thursday, June 18, 2015 12:47 PM
To: Sameer Kumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_xlog on a hot_stanby slave


On 18/06/2015 04:00, Sameer Kumar wrote:



On Wed, 17 Jun 2015 15:24 Xavier 12 
mania...@gmail.commailto:mania...@gmail.com wrote:

On 17/06/2015 03:17, Sameer Kumar wrote:

On Tue, 16 Jun 2015 16:55 Xavier 12 
mania...@gmail.commailto:mania...@gmail.com wrote:

Hi everyone,

Questions about pg_xlogs again...
I have two Postgresql 9.1 servers in a master/slave stream replication
(hot_standby).

Psql01 (master) is backuped with Barman and pg_xlogs is correctly
purged (archive_command is used).

Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days
only, it keeps growing up until disk space is full). I have found
documentation and tutorials, mailing list, but I don't know what is
suitable for a Slave. Leads I've found :

- checkpoints
- archive_command
- archive_cleanup

Master postgresq.conf :

[...]
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f
bar...@nas.lan:/data/pgbarman/psql01/incoming/%fmailto:bar...@nas.lan:/data/pgbarman/psql01/incoming/%25f'
max_wal_senders = 5
wal_keep_segments = 64

What's this parameter's value on Slave?

Hm... You have a point.
That autovacuum parameter seems to be useless on a slave.
I'll try to remove it and check pg_xlog.

That was not my point. I was actually asking about wal_keep_segment. Nevermind 
I found that I had misses the info (found it below. Please see my response).
Besides I try to keep my master and standby config as same as possible(so my 
advise ia to not switchoff autovacuum). The parameters which are imeffective on 
slave anyways won't have an effect. Same goes for parameters on master.
This helps me when I swap roles or do a failover. I have less parameters to be 
worried about.

Okay



Can you check the pg_log for log files. They may have se info? I am sorry if 
you have already provided that info (after I finish I will try to look at your 
previous emails on this thread)

Nothing...
/var/log/postgresql/postgresql-2015-06-17_31.log is empty (except old 
messages at the begining related to a configuration issue - which is now solved 
- after rebuilding the cluster yesterday).
/var/log/syslog has nothing but these :

Jun 18 09:10:11 Bdd02 postgres[28400]: [2-1] 2015-06-18 09:10:11 CEST LOG:  
paquet de d?marrage incomplet
Jun 18 09:10:41 Bdd02 postgres[28523]: [2-1] 2015-06-18 09:10:41 CEST LOG:  
paquet de d?marrage incomplet
Jun 18 09:11:11 Bdd02 postgres[28557]: [2-1] 2015-06-18 09:11:11 CEST LOG:  
paquet de d?marrage incomplet
Jun 18 09:11:41 Bdd02 postgres[28652]: [2-1] 2015-06-18 09:11:41 CEST LOG:  
paquet de d?marrage incomplet
Jun 18 09:12:11 Bdd02 postgres[28752]: [2-1] 2015-06-18 09:12:11 CEST LOG:  
paquet de d?marrage incomplet
Jun 18 09:12:41 Bdd02 postgres[28862]: [2-1] 2015-06-18 09:12:41 CEST LOG:  
paquet de d?marrage incomplet
Jun 18 09:13:11 Bdd02 postgres[28891]: [2-1] 2015-06-18 09:13:11 CEST LOG:  
paquet de d?marrage incomplet
Jun 18 09:13:40 Bdd02 postgres[28987]: [2-1] 2015-06-18 09:13:40 CEST LOG:  
paquet de d?marrage incomplet

These messages are related to Zabbix (psql port check).




Also can you share the vacuum cost parameters in your environment?

I don't understand that part... is this in postgresql.conf ?



autovacuum = on

Slave postgresql.conf :

[...]

Re: [GENERAL] Any postgres API available to get errorcode for PQerrorMessage

[Yogesh. Sharma]

Dear All,

I am facing some issue during postgresql service stop.
Issue:-
[root@localhost postgresql-9.0.18]# runuser -l postgres -c 
'/usr/pgsql/bin/pg_ctl stop -D '\''/var/lib/pgsql/data'\'' -s -m fast'
/usr/pgsql/bin/pg_ctl: error while loading shared libraries: libpq.so.5: cannot 
open shared object file: No such file or directory

How to resolve above issue.

I have performed some operations manually and this issue is resolved.
cp /usr/pgsql/lib/libpqwalreceiver.so pgsql/lib/libpq.so.5.3 /usr/lib64/
ln -s libpq.so.5.3 libpq.so
ln -s libpq.so.5.3 libpq.so.5

How to resolve by postgresql.spec file?


Regards,
Yogesh

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rob Sargent
Sent: Wednesday, October 15, 2014 10:08 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Any postgres API available to get errorcode for 
PQerrorMessage

On 10/15/2014 02:17 AM, Roopeshakumar Narayansa Shalgar (rshalgar) wrote:
HI,

PQerrorMessage pirints the error message like (no space available,etc). Does 
postgres provide
any  API which gives the error code listed in the below appendix;

http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html


I need the exact error code so that I can shutdown my application whenever I 
face  the no space available problem.

--
Thanks and Regards,
Rupesh
Isn't this best done by monitoring tools such as nagios and done much before 
you actually run out of space?



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---

[GENERAL] Help related to Postgresql for RHEL 6.5

[Yogesh. Sharma]

Dear All,

I want to upgrade RHEL 5.4 to RHEL 6.5.
So, could you please let me know, which postgresql version is stable for RHEL 
6.5?

Regards,
Yogesh



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---

Re: [GENERAL] Help related to Postgresql for RHEL 6.5

[Yogesh. Sharma]

Dear All,

Compatibility issues  of 9.3 are more.
So, please guide which version is suitable.

Regards,
Yogesh  

-Original Message-
From: Devrim Gündüz [mailto:dev...@gunduz.org] 
Sent: Thursday, August 28, 2014 7:34 PM
To: Yogesh. Sharma
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Help related to Postgresql for RHEL 6.5


Hi,

On Thu, 2014-08-28 at 03:22 +, Yogesh. Sharma wrote:

 I want to upgrade RHEL 5.4 to RHEL 6.5.
 So, could you please let me know, which postgresql version is stable 
 for RHEL 6.5?

Any supported PostgreSQL version is available in the yum repository: 

http://yum.postgresql.org 

You can use 9.3, for example.

Regards,

--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com 
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR




DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help related to Postgresql for RHEL 6.5

[Yogesh. Sharma]

Dear David,

 Are you currently using PostgreSQL?
Currently we are using PostgreSQL 8.1.18 version on RHEL 5.8.
Now we plan to update this to PostgreSQL 9.0 version with  RHEL6.5. As in 
verion 9.0 I found least Compatibilities.

So, please guide me.

Regards,
Yogesh

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G Johnston
Sent: Friday, August 29, 2014 9:22 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Help related to Postgresql for RHEL 6.5

Yogesh. Sharma wrote
 Compatibility issues  of 9.3 are more.
 So, please guide which version is suitable.

Compatibility as in the multixact issues or does your software not work with
9.3 changes?

What about any supported version and the provided link is unclear?

There is no way for us to evaluate suitability for your specific need unless 
you provide lots more info.  If 9.3 scares you off then use 9.2

Are you currently using PostgreSQL?

If you are referring to distro-supported versions (which Debian uses in my
case) you should make that requirement specific.  The PostgreSQL community 
feels all their officially supported releases are stable.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Help-related-to-Postgresql-for-RHEL-6-5-tp5816742p5816840.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



DISCLAIMER:
---
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
---


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Seeking for vacuum advise

[Yogesh Sharma]

Hello,

We are using postgres for one of our application. Currently using 
Postgres 8.3 with CentOS 5.3 x86_64


We are using inherited tables. Base table has 3 columns:
entry_date timestamp with time zone
nametext
valuetext
pk is timestamp

data tables are derived from this base table with constraint on year 
month table_id

daystable_id
1-71
8-14 2
15-213
= 224
data tables are created pre-created before start of month 
data_table_year_mm_table_id


No updates or delete will ever occur on data table except during purge 
we will dump full table and drop it, inserting ~30 million per day 
(every couple second data is loaded using copy statement).


What are the recommendation for vacuum / auto vacuum and statistics 
generation ? Does vacuum on postgres catalog tables is enough to 
overcome oid rollover or any other measures I have to take ?


Thanks,
YS

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general