Is there a way to do pg_dump -h host1 -d db1 -t tbl1 -Fc | pg_restore -d db1 -h host2 selectively, when there are multiple tables named tbl1 in db1 on host1?
The following scenario clarifies the question: I have two tables named exmpl_tbl, one in the schema "public" and the other in the schema "test_tables" respectively. I'd like to be able to dump and restore public.exmpl_tbl or test_tables.exmpl_tbl individually. Presently, pg_restore tries to restore all occurrences of exmpl_tbl; if a table of this name exists in ANY schema, pg_restore fails complaining that "exmpl_tbl" already exists. This behavior makes it dangerous, here anyway, to have identically named tables in different schemas, which in turn makes it dangerous to use schemas "To organize database objects into logical groups to make them more manageable". Can pg_dump and pg_restore be taught qualified names? For testing, I drop public.exmpl_tbl on host2 in db1, and (1) pg_dump -h host1 -d db1 -t public.exmpl_tbl [-Fc] dumps nothing (2) pg_dump -h host1 -d db1 -t exmpl_tbl [-Fc] | pg_restore -h host2 -t public.exmpl_tbl restores nothing (3) pg_dump -h host1 -d db1 -t exmpl_tbl -FC | pg_restore -l -h host2 complains that the table already exists (4) pg_dump -h host1 -d db1 -t exmpl_tbl | pg_restore -h host2 -d db1 inserts the rows of [host1.]db1.public.exmpl_tbl into [host2.]db1.public.exmpl_tbl and the rows of [host1.]db1.sh_tables.exmpl_tbl into [host2.]db1.sh_tables.exmpl_tbl; if either table exists, pg_restore proceeds to add duplicate records unless each table has a unique index defined (5) pg_dump -h host1 -d db1 -t exmpl_tbl -Fc | pg_restore -h host2 -d db1 -c fails with error unless at least on table named exmpl_tbl exists in the database. The workaround, which is slow and gives lots of errors, is to put a unique index on every table, and then use a plain text dump and restore with -t exmpl_tbl in the pg_dump options. Yech! It sure would be nice if pg_dump and pg_restore knew to use qualified names (<schema>.<table>) like the SQL commands. Thanks, Murthy ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly