0

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.

3
  • 1
    It seems there's custom search_path set for the superuser user (or the db_name DB), which includes nonexistent schema. Aug 6, 2013 at 8:49
  • Then what is the solution for that.I am unable to resolve. Aug 6, 2013 at 9:29
  • @sharadvishe Check (and probably change) then settings Milen mentioned?
    – dezso
    Aug 6, 2013 at 9:35

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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