Dump/restoring a given table in a given schema

From: Murthy Kambhampaty <murthy(dot)kambhampaty(at)goeci(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Dump/restoring a given table in a given schema
Date: 2003-02-26 21:51:13
Message-ID: 2D92FEBFD3BE1346A6C397223A8DD3FC092170@THOR.goeci.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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

Browse pgsql-admin by date

  From Date Subject
Next Message Stephen Harris 2003-02-27 01:00:24 Re: ident sameuser failure
Previous Message Sidar Lopez Cruz 2003-02-26 18:46:19 Dump from mssql

Browse pgsql-general by date

  From Date Subject
Next Message Joseph Shraibman 2003-02-26 22:11:18 Re: How do I change the server encoding? SOLVED
Previous Message Andrew Bartley 2003-02-26 21:51:04 Re: WITHOUT OIDS