Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to take backup of postgresql 9.0 by using pg_dump command. command here is: cd /opt/PostgresPlus/9.0AS/bin and hit the following command

pg_dump -h xxx.xxx.xxx.xxx -p 5432 -U superuser db_name>db_name.dump

But i am getting error like,

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  schema "dbms_sql" does not exist
pg_dump: The command was: SET search_path = dbms_sql, pg_catalog

please give me suggestion.

share|improve this question
1  
It seems there's custom search_path set for the superuser user (or the db_name DB), which includes nonexistent schema. –  Milen A. Radev Aug 6 '13 at 8:49
    
Then what is the solution for that.I am unable to resolve. –  sharad vishe Aug 6 '13 at 9:29
    
@sharadvishe Check (and probably change) then settings Milen mentioned? –  dezso Aug 6 '13 at 9:35
add comment

1 Answer

There are various ways to set the search_path in PostgreSQL.

To diagnose, log into the same database as the same superuser and check:

SHOW search_path;

If it contains dbms_sql, then check your postgresql.conf, databse and role settings to find where it came from.

Use any editor or for instance grep on in a Linux shell to audit postgresql.conf:

grep 'search_path' /your/path/to/postgresql.conf

To check on database and role settings, use the default command line client psql or a graphical GUI like pgAdmin.

In pgAdmin you just select the object in the object browser to see all settings in the SQL pane.

In psql, use

\drds superuser

to see settings for this role. And

\drds '' db_name

to see settings for the database. Or just

\drds 

to see all settings for all roles and databases.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.