Re: Copy entire schema A to a different schema B - Mailing list pgsql-general

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.klaver@aklaver.com <mailto: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>
>      > <mailto: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 <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com