Forum OpenACS Q&A: restore dump from 7.0.3 to 7.1.2 Problem

1: restore dump from 7.0.3 to 7.1.2 Problem

Posted by Bob OConnor on 08/08/01 09:42 PM

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...

SUGGESTIONS?
AND
THANK YOU

TIA
-Bob

2: Response to restore dump from 7.0.3 to 7.1.2 Problem (response to 1)

Posted by Don Baccus on 08/08/01 09:46 PM

Turn "verbose" on in the database pool portion of your init file so we can see exactly what PG is complaining about.

3: Response to restore dump from 7.0.3 to 7.1.2 Problem (response to 1)

Posted by Robin Felix on 08/09/01 12:35 AM

If the rest of your queries are working properly, you might want to log into postgresql to see if the sequence "sec_id_seq" exists.

db_name# d sec_id_seq

The 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.

4: Response to restore dump from 7.0.3 to 7.1.2 Problem (response to 1)

Posted by Bob OConnor on 08/09/01 03:53 AM

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/

Thank you.

5: Response to restore dump from 7.0.3 to 7.1.2 Problem (response to 1)

Posted by Roberto Mello on 08/09/01 08:27 AM

I will put a link to Pascal Schaeffer's "Upgrading from PG 7.0.x to PG 7.1" in a more prominent place in openacs.org:

http://pascal.scheffers.net/openacs/pgupdate/

Basically, you probably only need to re-load www/doc/sql/postgres.sql.

6: I have restored a dump from 7.0.3 to 7.1.2 (response to 1)

Posted by Rocael Hernández Rizzardini on 08/09/01 04:38 PM

After creating de DB I did:

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)

7: Response to restore dump from 7.0.3 to 7.1.2 Problem (response to 1)

Posted by Bob OConnor on 08/09/01 05:35 PM

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
Then
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...

dropdb mydbname

Then you can tell me to Go Jump in a Lake, Bob!

THANK YOU!

--
*Ref for email users: Virtual Jerry
http://www.theashergroup.com/tag/articles/nsvhr/virtual-hosting-howto