Can i stop the "Public" schema from being Dropped with pg_dump?

Question

I am doing a pg_dump command as follows:

/Library/PostgreSQL/8.4/bin/pg_dump --host localhost --port 5432 --username xxx --format plain --clean --inserts --verbose --file /Users/xxx/documents/output/SYSTEM_admin_20131203101809.sql --exclude-table public.dbmirror_mirroredtransaction --exclude-table public.dbmirror_mirrorhost --exclude-table public.dbmirror_pending --exclude-table public.dbmirror_pendingdata --exclude-table public.mdflog --exclude-table public.fcpersistentstore --exclude-table public.backup_restore --exclude-table public.mdflogeventcode testdb

The problem I have is that in the plain sql file that is created it adds a command to try and DROP the whole of the PUBLIC schema as shown in this snippet:

...
DROP FUNCTION public.f_updateeventlog();
DROP FUNCTION public.f_updateadmindata();
DROP PROCEDURAL LANGUAGE plpgsql;
DROP SCHEMA public;

CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO postgres;
COMMENT ON SCHEMA public IS 'standard public schema';
...

I DO want to drop all the other objects I have not excluded in the exclude-tables parameters I have provided, but I DONT want it to DROP the schema.

I have tried adding the schema as an exclude-table parameter but that did not work.

I am using Postgresql 8.4 for the pg_dump.

EDIT: I wanted to update this question and say I believe that it is not possible to get pg_dump to exclude the DROP / CREATE public command in the plain format. I believe you have to use the custom format and then pg_restore in order to stop that from happening. As I am using psql to restore and pg_dump with plain format, I simply remove the commands I don't want from the sql file after its created automatically as part of the Java process I am creating and I can get around this. I am leaving the question in case someone does find a way of doing this.

Answer 1

I think your best bet is to post-process the dump after it has been made. You could do this with grep or the like or just search and delete from the dump after it has been made.

The fact is that you are using the --clean option which drops the restored database objects. There isn't a ready way to tell pg_dump to drop only some restored database objects, which is probably a good thing.