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
