Postgresql – pg_dump issue while dumping single schema

pg-dumppostgresql

pg_dump does refuse to dump a given schema in my DB.

pg_dump -d <dbname> -s -n dm_geo -f <filename>

Result

-- 
-- PostgreSQL database dump 
--

-- Dumped from database version 9.5.4
-- Dumped by pg_dump version 9.5.4

SET statement_timeout = 0; 
...
SET row_security =off;

--
-- PostgreSQL database dump complete
--

Best Answer

Seems your command is wrong, Follow the below command to dump dm_geo schema.

To dump without data

pg_dump dbname -s -n dm_geo -f backup.sql

To dump schema and data

pg_dump dbname -n dm_geo -f backup.sql

Related Solutions

Postgresql – pg_dump: SQL command failed

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.

PostgreSQL – How to Prevent Writes to a Schema Temporarily

You can REVOKE access from that schema, but I am affraid the application will have some unwanted effects. PostgreSQL has fine tuned permission administration. And later you can GRANT access to that schema.