Обсуждение: Restore from online backup, query from pg_proc, got wrong result, please see the problem!
After restoring from online backup and archive xlog files, I query pg_proc using SQL: select * from pg_proc where proname= 'xxx' and oid = XXX. I got no result back, but when using SQL:select * from pg_proc where proname = 'xxx', I gotwhat I want, and the result OID is just the one in the first SQL.Then I reindex pg_proc, retry the first SQL, the resultis correct. Any one konw what is the matter? How can I solve this without reindexing the table! PS: I am using PG8.3.7 -------------- Richard 2010-08-11
"Richard" <husttripper@vip.sina.com> writes: > After restoring from online backup and archive xlog files, I query pg_proc using SQL: select * from pg_proc where proname= 'xxx' and oid = XXX. I got no result back, but when using SQL:select * from pg_proc where proname = 'xxx', I gotwhat I want, and the result OID is just the one in the first SQL.Then I reindex pg_proc, retry the first SQL, the resultis correct. > Any one konw what is the matter? How can I solve this without reindexing the table! Well, apparently you did the restore incorrectly, leading to an inconsistent index on pg_proc. Since you proided no details about how you did that, it's hard to say more. regards, tom lane
I did the followwing things to make a backup: 1.config the archive on 2. select * from pg_start_backup() 3.tar the $PTDATA dir to a package 4.select * from pg_stop_backup() The followwing things to restore: 1.Unzip the tar package I packaged 2.Copy the archived XLOG fils to $PTDATA 3.pg_ctl start Then did the staff I describe in the last mail. ------------------ Richard 2010-08-12 ------------------------------------------------------------- 发件人:Tom Lane 发送日期:2010-08-12 00:02:31 收件人:Richard 抄送:pgsql-general 主题:Re: [GENERAL] Restore from online backup, query from pg_proc, got wrong result, please see the problem! "Richard" <husttripper@vip.sina.com> writes: > After restoring from online backup and archive xlog files, I query pg_proc using SQL: select * from pg_proc where proname= 'xxx' and oid = XXX. I got no result back, but when using SQL:select * from pg_proc where proname = 'xxx', I gotwhat I want, and the result OID is just the one in the first SQL.Then I reindex pg_proc, retry the first SQL, the resultis correct. > Any one konw what is the matter? How can I solve this without reindexing the table! Well, apparently you did the restore incorrectly, leading to an inconsistent index on pg_proc. Since you proided no details about how you did that, it's hard to say more. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
------------------ Richard 2010-08-12 ------------------------------------------------------------- 发件人:Richard 发送日期:2010-08-12 00:11:13 收件人:Tom Lane 抄送:pgsql-general 主题:Re: [GENERAL] Restore from online backup, query from pg_proc, got wrong result, please see the problem! I did the followwing things to make a backup: 1.config the archive on 2. select * from pg_start_backup() 3.tar the $PTDATA directory to a package 4.select * from pg_stop_backup() The followwing things to restore: 1.unzip the tar package I packaged 2.copy the archived XLOG files to $PTDATA 3.pg_ctl start Then did the staff I describe in the last mail. ------------------ Richard 2010-08-12 ------------------------------------------------------------- 发件人:Tom Lane 发送日期:2010-08-12 00:02:31 收件人:Richard 抄送:pgsql-general 主题:Re: [GENERAL] Restore from online backup, query from pg_proc, got wrong result, please see the problem! "Richard" <husttripper@vip.sina.com> writes: > After restoring from online backup and archive xlog files, I query pg_proc using SQL: select * from pg_proc where proname= 'xxx' and oid = XXX. I got no result back, but when using SQL:select * from pg_proc where proname = 'xxx', I gotwhat I want, and the result OID is just the one in the first SQL.Then I reindex pg_proc, retry the first SQL, the resultis correct. > Any one konw what is the matter? How can I solve this without reindexing the table! Well, apparently you did the restore incorrectly, leading to an inconsistent index on pg_proc. Since you proided no details about how you did that, it's hard to say more. regards, tom lane -- 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