CODEWITHSUNDEEP
postgresqldatabase-backupspgadminpg-dump
Lifee Iss Simplee
Lifee Iss Simplee

Reputation: 21

pg dump output the schema name and the data

when i use (pg_dump -f backup.sql mydb), the schema name are not included. How to dump that will output also the scheme name(personal). see example below

Output : ALTER TYPE basicinfo OWNER TO postgres; // note : basicinfo is the name of the table

Expected Output : ALTER TYPE personal.basicinfo OWNER TO postgres;

any ideas?

Upvotes: 2

Views: 1904

Answers (2)

ji-ruh
ji-ruh

Reputation: 701

You cant do that using pg_dump directly. Try to use different tools, They have different implementation for exporting PostgreSQL database.

Here are list of tools:

  1. phpPgAdmin - http://sourceforge.net/projects/phppgadmin

  2. AnySQL Maestro - http://www.sqlmaestro.com/products/anysql/maestro/

  3. DBeaver - http://dbeaver.jkiss.org/

All refence: https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

Upvotes: 0

Robins Tharakan
Robins Tharakan

Reputation: 2473

Global Database properties such as OWNER etc. are always given with SCHEMA names in a pg_dump output.

However, for non-global database objects, as far as I know, there isn't any way to get SCHEMA names prepended to all the database objects. The way the script works is that it sets the SET search_path before-hand all Schema specific database objects... which is more efficient and has the same effect.

In case you are trying to parse an pg_dump output to extract a given SQL line that works independently, you may have to also parse the nearest-preceding SET search_path line and execute that before executing the target line (for e.g. ALTER TYPE) to have the desired effect.

Upvotes: 3

Related Questions