Re: Copy entire schema A to a different schema B

Tiffany Thang <tiffanythang@xxxxxxxxx> · Wed, 20 Feb 2019 18:21:47 -0500

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 <robjsargent@xxxxxxxxx> 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;