Re: Copy entire schema A to a different schema B

On 2/20/19 2: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

Is table mytable big enough to actually need 8 parallel jobs?

More below.

>
> 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.

pg_restore -f is your friend.

If you do something like:

pg_restore -f mytable.sql c:\temp\testuser1

it will restore the pg_dump output to the file mytable.sql in text form,
where you can change things as you need.

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com