how to create dump for specific schema in postgres DB

Question

I have a Postgres database "rafiu" with many schemas namely test1, test2, test3. In this I want to dump the test2 schema and its data. I tried with the following query

pg_dump -U postgres -n test2 -t t1 -t t2 rafiu > test_schema.sql

but it dumped public.t1, public.t2 tables instead of test2 schema tables in the resultant dump file.

Kindly suggest me how to create a dump specific specific schema in a DB.

Thanks in advance.

Mingyu · Accepted Answer · 2013-11-22 07:18:13Z

17

-n test2 means to dump schema test2.

If you want to dump table test2.t1 and test2.t2, you might want to try the following statement:

pg_dump -U postgres -t test2.t1 -t test2.t2 rafiu > test_schema.sql

answered Nov 22, 2013 at 7:18

Mingyu

33.8k14 gold badges58 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fsi Over a year ago

How about the whole data from one schema? Using his example, get all tables from test2?

Shiping Over a year ago

I had the same problem and had spent a couple of days trying to resolve the issue (online searching, trying various ways). In my case, when I specified a schema and tables in the schema to dump, I got no matching tables found errors. Apparently pg_dump only searches the public schema for tables specified with -t and/or -T without prefixing the table names with the schema name. By intuition I assumed that the search would be done in the specified schema for specified tables instead in the public schema. I think the man page could make it more explicit about the behaves of -n/-N and -t/-T.

Collectives™ on Stack Overflow

how to create dump for specific schema in postgres DB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related