Forum OpenACS Q&A: restore dump from 7.0.3 to 7.1.2 Problem
I just restored my database from a pg_dump backup version 7.0.3 to 7.1.2. It appears to work in that I can select * from users ...yada.
But tied to AOLserver and trying to get a page up I get this error:
[-conn0-] Notice: Querying 'select nextval('sec_id_seq') from dual; Notice: dbinit: sql(localhost::tgndata): 'select nextval 'sec_id_seq') from dual' Error: Database operation "1row" failed (exception NSINT, "Query did not return a row.") Database operation "1row" failed (exception NSINT, "Query did not return a row.") while executing "ns_db 1row $db [db_sql_prep $sql" (procedure "database_to_tcl_string" line 6) invoked from within "database_to_tcl_string $db "select sec_id_seq.nextval from dual"" (procedure "ad_assign_session_id" line 9) invoked from within "ad_assign_session_id $db" (procedure "ad_validate_security_info" line 16) invoked from within "ad_validate_security_info -secure $secure" (procedure "ad_verify_and_get_user_id" line 3)
I searched for 'sec_id_seq' in /www/doc/sql and came up with nothing. Perhaps it is something that didn't get restored with from the dump???
Pages that don't call the database come up fine...
db_name# d sec_id_seqThe correct response should be:
Sequence "sec_id_seq" Attribute | Type ---------------+--------- sequence_name | name last_value | integer increment_by | integer max_value | integer min_value | integer cache_value | integer log_cnt | integer is_cycled | "char" is_called | "char"If you don't see that, then the sequence doesn't exist; that would explain your error message. The next step would be to grep your dumpfile for the proper creation command:
CREATE SEQUENCE "sec_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;If the sequence didn't get properly restored, you can manually create it using the command above, replacing the "minvalue" with some value higher than your current highest index value.
Robin, I did check and I do have sec_id_seq as you described...and I turned on verbose (Don's suggestion). Further analysis led me to discover that the dual table is missing stuff. I can't do:
select sysdate from dual;I get "(0 rows)" rather than
sysdate ------------------------ 2001-08-08 21:46:47-04 (1 row)
So where do I get the .sql code to restore the dual table?
and Robin, where did you find the code to restore the sequence?
I didn't find it in ~/www/doc/sql/
Basically, you probably only need to re-load www/doc/sql/postgres.sql.
1. createlang plpgsql mydb
2. createlang pltcl mydb
3. psql -f postgres.sql mydb (it will give you some errors, ignore it, are normal becuase you did the previous commands)
PROBLEM SOLVED! Yes, I was using Pascal's document.... I didn't tell all.... I am using Virtual Jerry's * Aolserver setup running another site on same box same IP address. We upgraded the single postgresql from 7.0.3 to 7.1.2 and on that site there were minor errors mostly due to missing stuff: survey-simple.sql and table users_special_quotas which I loaded with psql and all was well.
So When adding our other site from the other server, I did a
createdb -T template1 mydbname
psql -d template1 -f pg_dumpoutput
I did this above command and added at the end
... > ta_dumplog
which mainly shows a bunch of creates....last two times, it seemed like the terminal locked up... no verbose text to screen... I just watched "TOP" to see when it was done... It takes a long time to restore our 129 Mbytes!
I found the same errors, particularly the missing survey-simple.sql that prevented any (data) page from appearing. SO now all is well...
Onto a seamless DomainName migration plan where I do this AGAIN with fresh HOT data. (Almost as hot as it is here in Maine --- hi 90's today)
And for lurkers I'll do this first...
Then you can tell me to Go Jump in a Lake, Bob!
*Ref for email users: Virtual Jerry