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.
Best 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:If it contains
dbms_sql
, then check yourpostgresql.conf
, databse and role settings to find where it came from.Use any editor or for instance
grep
on in a Linux shell to auditpostgresql.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
to see settings for this role. And
to see settings for the database. Or just
to see all settings for all roles and databases.