3

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...)

Maciej
  • 10,423
  • 17
  • 64
  • 97

1 Answers1

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
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • So why one engine adds that buy other not? I have dozen of schemas per database not easy to filter out all of those – Maciej Sep 29 '19 at 21:41
  • **You** created the schemas (for a reason) Just deal with it. [and;sed is your friend] – wildplasser Sep 29 '19 at 22:03
  • 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...) – Maciej Sep 30 '19 at 05:23
  • 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 – Maciej Oct 02 '19 at 05:59
  • 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 ? – Maciej Oct 10 '19 at 13:50