How force pg_dump to (not) include scheme name for each objects in DDL

Question

I need compare 2 DBs schemes (DDLs) - Postgress 9.5

Im executing below command on both servers:

pg_dump -U postgres --dbname=db--schema-only -f schema.sql

But I noticed one of output prefixes each objects by scheme name, eg

CREATE FUNCTION schemeName.function_name

while the other doesntm eg:

CREATE FUNCTION function_name

Is there any option in pg_dump where I can decide to inculde or not scheme names in output DDL? (Preference is at least remove those schema prefixes...)

Atombomb

Answer 1

In short:you can't. But, you can use sed to automate most of your editing.

---

#!/bin/sh

# dump only schema "tmp"
# force quoted identifiers
# use sed to strip them
# [youstillneedtoremove the "CReate SCHEMA $SCH_NAME-stuff

DB_NAME="postgres"

pg_dump -Upostgres -n tmp --schema-only --quote-all-identifiers $DB_NAME \
   | sed 's/"tmp"\.//g' > tmp_schema_stripped.sql

#EOF

Discover

So why one engine adds that buy other not? I have dozen of schemas per database not easy to filter out all of those – Atombomb

You created the schemas (for a reason) Just deal with it. [and;sed is your friend] – Discover

Wasn't me actually but app vendor... Still do not understand why one db instance add schemas ahead but other not? I'm okay to have those or not - as long as same methodology is used on both engines BTW one instance is installed on windows another on Linux box (if this make any difference...) – Atombomb

If no option to force dump to do / not include schema names in output script - can you please advise query which allows extract all schemes in selected database? Then I could call several times dump, sed and merge all together – Atombomb

I just noticed I have similar behavior on other Linux box (adding scheme names) - might it be because of different version of pg_dump tools ? – Atombomb

Sadly, pg_dump does not support this behaviour out-of-the-box. Using sed is not ideal since the replacements would have to be context-aware to avoid false positives. As an alternative, you could make a few changes to the pg_dump source code to achieve the desired outcome: #18216962 – Vivia