Re: [GENERAL] postgres crash when select a record
Hello Pavel, Thanks for the info, this is very great help. Regard Louis - Original Message From: Pavel Stehule [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Postgres pgsql-general@postgresql.org Sent: Thursday, May 15, 2008 4:43:36 AM Subject: Re: [GENERAL] postgres crash when select a record Hello 2008/5/14 [EMAIL PROTECTED]: Hello, Yes, I deleted that record, and now my backup is working fine! Thanks But why this problem came out? Is there anyway to trace it down and how it happen? Or is it a bug of postgres? I can't to eliminate PostgreSQL bug, but sometimes this problem signalize hw problems. You can search in archive similar cases. Postgres crashes, because stored row are in broken format. There are some projects that would solve it better - http://svana.org/kleptog/pgsql/pgfsck.html Regards Pavel Stehule Regards Louis - Original Message From: Pavel Stehule [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Postgres pgsql-general@postgresql.org Sent: Wednesday, May 14, 2008 6:13:05 PM Subject: Re: [GENERAL] postgres crash when select a record Hello Delete this broken row Regards Pavel Stehule 2008/5/14 [EMAIL PROTECTED]: Hello, I wish some could help me on this. I got a table which has 100500 records, when I try to query this particular record select * from cs_sr_mthly_rtn where mthly_rtn_id = 61609; Postgres crash, and show this errors: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. And in the serverlog, it shows: LOG: server process (PID 395) was terminated by signal 11 LOG: terminating any other active server processes FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2008-05-14 12:52:15 BST LOG: checkpoint record is at 0/48DEC034 LOG: redo record is at 0/48DEC034; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/705241; next OID: 49152 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/48DEC07C LOG: unexpected pageaddr 0/433AC000 in log file 0, segment 74, offset 3850240 LOG: redo done at 0/4A3ABFC0 LOG: database system is ready Because of this, I'm not able to do pg_dump backup anymore. Anyone can help me to solve this problem? Thanks louis
[GENERAL] postgres crash when select a record
Hello, I wish some could help me on this. I got a table which has 100500 records, when I try to query this particular record select * from cs_sr_mthly_rtn where mthly_rtn_id = 61609; Postgres crash, and show this errors: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. And in the serverlog, it shows: LOG: server process (PID 395) was terminated by signal 11 LOG: terminating any other active server processes FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2008-05-14 12:52:15 BST LOG: checkpoint record is at 0/48DEC034 LOG: redo record is at 0/48DEC034; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/705241; next OID: 49152 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/48DEC07C LOG: unexpected pageaddr 0/433AC000 in log file 0, segment 74, offset 3850240 LOG: redo done at 0/4A3ABFC0 LOG: database system is ready Because of this, I'm not able to do pg_dump backup anymore. Anyone can help me to solve this problem? Thanks louis
Re: [GENERAL] postgres crash when select a record
No, I didn't run the query when the DB is in recovering. I did that after database system is ready - Original Message From: Glyn Astill [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Postgres pgsql-general@postgresql.org Sent: Wednesday, May 14, 2008 6:07:23 PM Subject: Re: [GENERAL] postgres crash when select a record Looks loke you tried to run your query whilst postgres was recovering from an improper shutdown, try once again when the last thing in the log is LOG: database system is ready - Original Message From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: Postgres pgsql-general@postgresql.org Sent: Wednesday, 14 May, 2008 5:07:03 PM Subject: [GENERAL] postgres crash when select a record Hello, I wish some could help me on this. I got a table which has 100500 records, when I try to query this particular record select * from cs_sr_mthly_rtn where mthly_rtn_id = 61609; Postgres crash, and show this errors: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. And in the serverlog, it shows: LOG: server process (PID 395) was terminated by signal 11 LOG: terminating any other active server processes FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2008-05-14 12:52:15 BST LOG: checkpoint record is at 0/48DEC034 LOG: redo record is at 0/48DEC034; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/705241; next OID: 49152 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/48DEC07C LOG: unexpected pageaddr 0/433AC000 in log file 0, segment 74, offset 3850240 LOG: redo done at 0/4A3ABFC0 LOG: database system is ready Because of this, I'm not able to do pg_dump backup anymore. Anyone can help me to solve this problem? Thanks louis Sent from Yahoo! Mail. A Smarter Email.
Re: [GENERAL] postgres crash when select a record
Hello, Yes, I deleted that record, and now my backup is working fine! Thanks But why this problem came out? Is there anyway to trace it down and how it happen? Or is it a bug of postgres? Regards Louis - Original Message From: Pavel Stehule [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Postgres pgsql-general@postgresql.org Sent: Wednesday, May 14, 2008 6:13:05 PM Subject: Re: [GENERAL] postgres crash when select a record Hello Delete this broken row Regards Pavel Stehule 2008/5/14 [EMAIL PROTECTED]: Hello, I wish some could help me on this. I got a table which has 100500 records, when I try to query this particular record select * from cs_sr_mthly_rtn where mthly_rtn_id = 61609; Postgres crash, and show this errors: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. And in the serverlog, it shows: LOG: server process (PID 395) was terminated by signal 11 LOG: terminating any other active server processes FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2008-05-14 12:52:15 BST LOG: checkpoint record is at 0/48DEC034 LOG: redo record is at 0/48DEC034; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/705241; next OID: 49152 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/48DEC07C LOG: unexpected pageaddr 0/433AC000 in log file 0, segment 74, offset 3850240 LOG: redo done at 0/4A3ABFC0 LOG: database system is ready Because of this, I'm not able to do pg_dump backup anymore. Anyone can help me to solve this problem? Thanks louis
[GENERAL] Duplicated primary key id happen
Hello, I got a problem to restore a database, because there is a problem in my production database, which not suppose to happen like that, but it happen, not sure is a postgres bug or not. I got a table call CS_SR_MTHLY_RTN which has MTHLY_RTN_ID (INTEGER) as a primary key. So the MTHLY_RTN_ID value should be unique, but somehow there are 2 record which id is 15 in this table. The first record look fine to me, because the data in other columns look ok. But the second one, definitely I'm sure is wrong. There are value in column sr_batch_id and er_batch_id, and the value are very very big, 808464726 842018867 and the rest of the column value are null. How can this happen? Currently using Postgres 8.2.5 Regards Louis
[GENERAL] Duplicated primary key id happen
Hello, I got a problem to restore a database, because there is a problem in my production database, which not suppose to happen like that, but it happen, not sure is a postgres bug or not. I got a table call CS_SR_MTHLY_RTN which has MTHLY_RTN_ID (INTEGER) as a primary key. So the MTHLY_RTN_ID value should be unique, but somehow there are 2 record which id is 15 in this table. The first record look fine to me, because the data in other columns look ok. But the second one, definitely I'm sure is wrong. There are value in column sr_batch_id and er_batch_id, and the value are very very big, 808464726 842018867 and the rest of the column value are null. How can this happen? Currently using Postgres 8.2.5 Regards Louis
[GENERAL] timestamp problem
Hello, I got 2 database, one for product use and another for developer use. Both are same. In these database, I got a procedure which has a TIMSTAMP datatype as IN parameter. My problem is, when my Java application use the developer database and call this procedure, it is working fine, but when I switch to product database, it show error that this procedure not exit, it is looking for a procedure with TIMSTAMPTZ, why ? Is there somewhere in postgres can set not look for TIMESTAMPTZ, but TIMESTAMP? Regards Louis
Re: [GENERAL] Postgres file structure doubt
Hello Scott, Thanks for clear my doubt. Yes, I'm planning to do PITR backup. Another question, from what I understand, when there are data transaction going on, postgres will store in the log file, which is in /usr/local/pgsql/data/pg_xlog, when these data will finally save it into /database/pg/mydata? For e.g, if I did a pg_dump backup a week ago, and the system crash today, but I'm able to get all the files in /usr/local/pgsql/data/pg_xlog, can I still recover my database to the latest state? Regards Louis - Original Message From: Scott Marlowe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Monday, November 19, 2007 5:38:12 PM Subject: Re: [GENERAL] Postgres file structure doubt On Nov 19, 2007 11:24 AM, [EMAIL PROTECTED] wrote: Hi everyone, Got a doubt in my setup, please correct me if I'm wrong. In my postgres setup, /usr/local/pgsql (where postgres install) /usr/local/pgsql/data (PGDATA) /database/pg/mydata (tablespace which use for all the table I create) /database/pg/myindex (index which use for all the table I create) 1) In this setup, the actual user data are store in PGDATA the table structure index are store in /database/pg/mydata /database/pg/myindex Am I correct? The data that defines users, and tables, and other objects are in PGDATA. The data from users are stored in mydata/myindex. Not sure if that matches what you wrote or not... 2) So to backup (not pg_dump), I should make sure it include these 2 folder right? /usr/local/pgsql/data /database/pg/ To backup, you should generally use pg_dump. Are you planning on using PITR? Are you planning on shutting down your database when you back it up? if you're not using PITR, you must shut down postgresql to take a file system backup. 3) I think my setup is not quite right, I should move the PGDATA to /database/pg right? Sorta a personaly choice really. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Postgres file structure doubt
Hi everyone, Got a doubt in my setup, please correct me if I'm wrong. In my postgres setup, /usr/local/pgsql (where postgres install) /usr/local/pgsql/data (PGDATA) /database/pg/mydata (tablespace which use for all the table I create) /database/pg/myindex (index which use for all the table I create) 1) In this setup, the actual user data are store in PGDATA the table structure index are store in /database/pg/mydata /database/pg/myindex Am I correct? 2) So to backup (not pg_dump), I should make sure it include these 2 folder right? /usr/local/pgsql/data /database/pg/ 3) I think my setup is not quite right, I should move the PGDATA to /database/pg right? Regards Louis
[GENERAL] public schema doubt
Hello, I did a migration from 8.2.4 to 8.2.5, I used pg_dumpall to backup all the db and then restore it into 8.2.5. In my 8.2.4 db, I don't have public schema (it was dropped when I create the db, so only myschema is there), but when I restore to 8.2.5, I found that it created a public schema for me. So why it doing this for me? I thought it should restore back excatly the same what I backup from 8.2.4? Should I just let the public schema there? Will it cause any problem? In the postgres doc http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html (5.7.2) it said if I create a table without specify any schema name, it will put it into public schema, so I test it in my case, but it not doing it, the table is create under myschema, strange. And I check the search_path, it show $user,public . Could someone explain it to me? Thanks Louis
Re: [GENERAL] public schema doubt
Hello Martin, Yes, SHOW search_path show this $user,public . Oh yes, I get it now, the $user will take priority. Another question, this public schema, in the usual practice way, do you delete it or just leave it there and create your own schema? Regards Louis - Original Message From: Martin Gainty [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 16, 2007 3:17:40 PM Subject: RE: [GENERAL] public schema doubt Hello Louis SHOW search_path if public is not on search_path or does'nt come first then SET search_path TO public,$user; HTH/ Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Hello, I did a migration from 8.2.4 to 8.2.5, I used pg_dumpall to backup all the db and then restore it into 8.2.5. In my 8.2.4 db, I don't have public schema (it was dropped when I create the db, so only myschema is there), but when I restore to 8.2.5, I found that it created a public schema for me. So why it doing this for me? I thought it should restore back excatly the same what I backup from 8.2.4? Should I just let the public schema there? Will it cause any problem? In the postgres doc http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html (5.7.2) it said if I create a table without specify any schema name, it will put it into public schema, so I test it in my case, but it not doing it, the table is create under myschema, strange. And I check the search_path, it show $user,public . Could someone explain it to me? Thanks Louis Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare! Try now!
