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.

Pig

Answer 1

-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

Unpredictable

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

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. – Velour