Re: Copy entire schema A to a different schema B

[Rob Sargent]

On 2/20/19 4:21 PM, Tiffany Thang wrote:

Hi Ron,

How would that work if I'm only interested in importing/refreshing a single table in a target schema that contains several other tables?

Thanks.

Tiff

On Wed, Feb 20, 2019 at 5:36 PM Rob Sargent <robjsarg...@gmail.com <mailto:robjsarg...@gmail.com>> wrote:

    On 2/20/19 3:22 PM, Tiffany Thang wrote:

    Hi Adrian,
    I managed to backup my table in parallel using -Fd but I'm back
    to my original issue where I could not restore the table to a
    different schema.

    For example,
    I would like to backup testuser1.mytable and restore it to
    testuser2.mytable.

    pg_dump -U testuser1 -Fd -f c:\temp\testuser1 -j 8 -t mytable -h
    myserver testdb

    where mytable is in testuser1 schema

    The dump completed fine but when I attempted to restore the table
    using pg_restore to another database, it tried to create the
    table in testuser1 schema. The restore failed since testuser1
    schema does not exist in the target database. When I created a
    testuser1 schema in the target database, the restore worked fine.
    Since the dump toc is in binary format, I could not make the
    change to reflect the new target schema, testuser2.

    So, how should I go about restoring tables from one schema to a
    different schema name?

    Thanks.

    Tiff

    Can you not simply rename the schema after importing it? Perhaps
    renaming an exiting version first, if necessary. Rename schema A
    to B; import schema A; rename A to C; rename B to A;

If nothing is in the way for the restore maybe all you need is

ALTER TABLE [ IF EXISTS ]/|name|/
    SET SCHEMA/|new_schema|/