Thanks everyone. Unfortunately the schema rename would not work since the source database will be our production system. We have not gone live yet but the system is expected to be constantly used.
I have multiple tables that I need to export ranging from 20GB to 60GB each. The parallel will not work for a single table but would be beneficial if I have multiple tables to dump.
I'm thinking maybe using what Adrian has suggested with the -f option and then modify the file or maybe use a common public schema everywhere on the source and target databases. I would have to restrict who has access to the public schema.
Thanks.
Tiff
On Wed, Feb 20, 2019 at 11:14 PM Jiří Fejfar <jurafejfar@xxxxxxxxx> wrote:
Hi Tiffany, Isn't it possible for you to do:
1) rename temporarily schema testuser1 to testuser2
2) dump that schema into binary format
3 rename back schema testuser2 to testuser1
4 restore backup to testuser2 schema on other DB?
Jura.
On Wed, 20 Feb 2019 at 23:23, Tiffany Thang <tiffanythang@xxxxxxxxx> 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
>
> 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.klaver@xxxxxxxxxxx> 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@xxxxxxxxx
>> > <mailto:ronljohnsonjr@xxxxxxxxx>> 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@xxxxxxxxxxx
