Re: Copy entire schema A to a different schema B

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(dot)klaver(at)aklaver(dot)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(at)gmail(dot)com
> > <mailto:ronljohnsonjr(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com
>