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