Copy entire schema A to a different schema B

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Copy entire schema A to a different schema B

Tiffany Thang
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?

Thank.

Tiff
Reply | Threaded
Open this post in threaded view
|

Re: Copy entire schema A to a different schema B

Adrian Klaver-4
On 2/11/19 8: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

Use the -f option to pg_restore:

https://www.postgresql.org/docs/10/app-pgrestore.html

So something like:

pg_restore -f text_file.sql the_binary_file.out

This will get you back to a text file you can manipulate.

> dump file generated is binary, I could not make modifications to the
> file. Is the procedure the same in version 11?
>
> Thank.
>
> Tiff


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Copy entire schema A to a different schema B

Ron-2
In reply to this post by Tiffany Thang
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.

Reply | Threaded
Open this post in threaded view
|

Re: Copy entire schema A to a different schema B

Tiffany Thang
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?

Thanks.

Tiff

On Mon, Feb 11, 2019 at 11:10 AM Ron <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Copy entire schema A to a different schema B

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Copy entire schema A to a different schema B

Tiffany Thang
Thanks Adrian!

Tiff

On Mon, Feb 11, 2019 at 11:53 AM Adrian Klaver <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> 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
[hidden email]