Forum OpenACS Q&A: Problem with Postgres v7.2.3 restore of .dmp

Collapse
1: Problem with Postgres v7.2.3 restore of .dmp
Posted by Richard Hamilton on 11/27/03 12:11 AM
I am getting some errors that I have not seen before in my database restores - invalid command \N. I have posted the relevant section from the psql log in the hope that someone will know exactly which bit is failing and maybe why. I had already applied /acs-kernel/sql/postgres/postgres.sql and the redefinitions of functions failed as usual because they are defined in template1. Hope someone can help.

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'forums_forum_id_pk' for table 'forums_forums'

CREATE

CREATE

CREATE

CREATE

CREATE

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'forums_messages_pk' for table 'forums_messages'

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'cr_news_pk' for table 'cr_news'

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'surveys_pk' for table 'surveys'

CREATE

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'survey_sections_pk' for table 'survey_sections'

CREATE

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'survey_q_question_id_pk' for table 'survey_questions'

CREATE

CREATE

CREATE

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'survey_qc_choice_id_pk' for table 'survey_question_choices'

CREATE

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'survey_resp_response_id_pk' for table 'survey_responses'

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

ERROR:  acs_function_args: Permission denied.

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \N

invalid command \.

ERROR:  parser: parse error at or near "person__new"

invalid command \.

ERROR:  permission denied

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

CREATE

Collapse
2: Re: Problem with Postgres v7.2.3 restore of .dmp (response to 1)
Posted by Richard Hamilton on 11/27/03 12:13 AM
Oh, and here is the result on server re-start :

Request Error
Server startup failed: Error during bootstrapping
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind 0or1row nsdb0 {

        select 1 as admin_exists_p
        from dual
        where exists (select 1
                      from all_o..."
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql"
    invoked from within
"db_exec 0or1row $db $full_name $sql"
    invoked from within
"set selection [db_exec 0or1row $db $full_name $sql]"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
    set selection [db_exec 0or1row $db $full_name $sql]
    }"
    (procedure "db_string" line 7)
    invoked from within
"db_string admin_exists_p {} -default 0"
    (procedure "ad_acs_administrator_exists_p" line 2)
    invoked from within
"ad_acs_administrator_exists_p"
    (procedure "ad_verify_install" line 8)
    invoked from within
"ad_verify_install"

Collapse
3: Re: Problem with Postgres v7.2.3 restore of .dmp (response to 2)
Posted by Richard Hamilton on 11/27/03 01:13 AM
A quick scan of the PostgreSQL online docs revealed a small detail that I didn't know which is that pg_dump creates .dmp files that are relative to template0.

So when I retried the restore I typed the following :

createdb -T template0 dbname

and then ran the restore as before.

All now hunky-dorie.

I think my problem stemmed from the fact that I have password authentication set up in postgres. I think that some of the DML statements in the dump were trying to do something to pre-existing functions in template1 owned by another user.

I post this in case it helps someone sometime!

R.