Re: Copy entire schema A to a different schema B

Hi Tiffany, Isn't it possible for you to do:

1) rename temporarily schema testuser1 to testuser2
2) dump that schema into binary format
3 rename back schema testuser2 to testuser1
4 restore backup to testuser2 schema on other DB?

Jura.

On Wed, 20 Feb 2019 at 23:23, Tiffany Thang <tiffanythang@gmail.com> 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
>
> On Mon, Feb 11, 2019 at 11:53 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 2/11/19 8:30 AM, Tiffany Thang wrote:
>> > Thanks Adrian and Ron. Sorry, I was not clear. What I'm trying to
>> > achieve was to dump the schema quickly and be able to restore a single
>> > or subset of objects from the dump. As far as I understand, the only way
>> > of achieving that is to use the custom format and the -j option. Is that
>> > correct? Are there any other alternatives?
>>
>> If you want to use -j then you need to use the -Fd output:
>>
>> https://www.postgresql.org/docs/10/app-pgdump.html
>>
>> "-j njobs
>> --jobs=njobs
>>
>>      Run the dump in parallel by dumping njobs tables simultaneously.
>> This option reduces the time of the dump but it also increases the load
>> on the database server. You can only use this option with the directory
>> output format because this is the only output format where multiple
>> processes can write their data at the same time."
>>
>> If you need to grab just a subset of the schema then there are options
>> to do that depending on the object. From above link as examples:
>>
>> "-n schema
>> --schema=schema
>>
>>      Dump only schemas matching schema; this selects both the schema
>> itself, and all its contained objects. ..."
>>
>>
>> "-t table
>> --table=table
>>
>>      Dump only tables with names matching table.  .."
>>
>>
>> >
>> > Thanks.
>> >
>> > Tiff
>> >
>> > On Mon, Feb 11, 2019 at 11:10 AM Ron <ronljohnsonjr@gmail.com
>> > <mailto:ronljohnsonjr@gmail.com>> wrote:
>> >
>> >     On 2/11/19 10:00 AM, Tiffany Thang wrote:
>> >      > Hi,
>> >      > To copy the source schema A to target schema B in the same
>> >     database in
>> >      > PG10.3, I use psql to dump schema A and manually removes anything
>> >     specific
>> >      > to the schema in the text dump file before importing into schema
>> >     B. How do
>> >      > I achieve the same exporting from Schema A and importing into
>> >     schema B
>> >      > using pg_dump with the -Fc option? Since the dump file generated is
>> >      > binary, I could not make modifications to the file. Is the
>> >     procedure the
>> >      > same in version 11?
>> >
>> >     Why do you need to use "--format=custom" instead of "--format=plain"?
>> >
>> >     For example:
>> >     $ pg_dump --format=plain --schema-only --schema=A
>> >
>> >
>> >     --
>> >     Angular momentum makes the world go 'round.
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com