Postgresql – Schema Dump Without User Mapping Passwords

pg-dumppostgresqlpostgresql-9.5

Is it possible to do a pg_dump --schema-only of the database schema but not include any username/password combinations that are in user mappings?

For example, I would like items such as the following to not be put in the dump:

CREATE USER MAPPING FOR greg SERVER db_name OPTIONS (
    password 'supersecret',
    username 'bobs_uncle'
);

Best Answer

The only thing I can think of is to create a single-purpose non-super user and grant them read access to all the objects in the database. Then run pg_dump connected as that user. Perhaps not the most elegant solution.

Related Solutions

Postgresql – pg_dump does not honor -n

When dumping a schema with the mentioned command, as postgres user:

pg_dump -f file.sql -F plain --inserts -n schemaname database

it produces a file whose basic structure looks like:

-- various SET commands
CREATE SCHEMA schemaname;

ALTER SCHEMA schemaname OWNER TO ownername;

SET search_path = schemaname, pg_catalog;

CREATE TABLE tablename(...) ...

ALTER TABLE schemaname.tablename OWNER TO ownername;

COPY tablename (columns...) FROM stdin;
data...
\.

The fact that search_path is set to the newly created schema implies that the CREATE TABLE and COPY statements that follow will happen in that schema and not in the public schema.

Or SET search_path would have to fail, which shouldn't happen even if the creation of the schema just above in the script failed. If schemaname does not exist, the next schema tried would be pg_catalog and CREATE TABLE would refuse to write into it, and after that there's no next schema in the search path, and in particular not public.

It's hard to imagine what mishappening could cause the above script to end up creating and populating anything in the public schema.

PostgreSQL – Missing Data for One Schema in pg_dump

If you don't have the required permissions, pg_dump will give you an error message rather than an empty COPY statement. Moreover, permissions do not apply to superusers.

You should verify what the database user used by pg_dump sees when selecting from the original table.

The only explanations I can think of are:

Somebody deleted all rows from the table.
You have experienced data corruption that somehow rendered the table's file empty of zeroed out.

Try the following to find the file:

SELECT oid FROM pg_database
WHERE datname = 'db';

SELECT relfilenode FROM pg_class
WHERE oid = 'schema1.some_table'::regclass;

If the numbers returned are 1234 and 5678, examine the file base/1234/5678 in the data directory: How big is it, what does it contain?

Best Answer

Related Solutions

Postgresql – pg_dump does not honor -n

PostgreSQL – Missing Data for One Schema in pg_dump

Related Question