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.

  • 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
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, privacy policy and cookie policy

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