From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Tiffany Thang <tiffanythang(at)gmail(dot)com> |
Cc: | Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Copy entire schema A to a different schema B |
Date: | 2019-02-21 00:16:31 |
Message-ID: | d9e0d326-9f00-b9b9-b6b0-4ae68515b735@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-02-21 00:26:15 | Re: Plpythonu extension |
Previous Message | Rob Sargent | 2019-02-21 00:02:25 | Re: Copy entire schema A to a different schema B |