pg dump output the schema name and the data

Question

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?

If you go through the first few lines of the dump, you will see lines like SET search_path = personal. That should ensure that the rights objects are chosen. May be you wanted to explicitly mention schema name for some other reason? — Jayadevan
– Jayadevan, Commented Oct 26, 2015 at 4:20
Thanks Jayadevan, but you are right that i want to explicitly mention the schema name also. I been restore a database.sql with same format(schema name included) and want to backup like the same format. Is there other way? — Lifee Iss Simplee
– Lifee Iss Simplee, Commented Nov 3, 2015 at 6:14

Robins Tharakan · Accepted Answer · 2015-10-26 04:18:58Z

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.

ji-ruh · Accepted Answer · 2015-10-26 06:09:47Z

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:

phpPgAdmin - http://sourceforge.net/projects/phppgadmin
AnySQL Maestro - http://www.sqlmaestro.com/products/anysql/maestro/
DBeaver - http://dbeaver.jkiss.org/

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

Collectives™ on Stack Overflow

pg dump output the schema name and the data

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related