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 got what I want, and the result OID is just the one in the first SQL.Then I reindex pg_proc, retry the first SQL, the result is 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 got what I want, and the result OID is just the one in the first SQL.Then I reindex pg_proc, retry the first SQL, the result is 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 got what I want, and the result OID is just the one in the first SQL.Then I reindex pg_proc, retry the first SQL, the result is 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 got what I want, and the result OID is just the one in the first SQL.Then I reindex pg_proc, retry the first SQL, the result is 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