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.kla...@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 <ronljohnso...@gmail.com > > <mailto:ronljohnso...@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.kla...@aklaver.com >
