Hi - we have had to do such operations quite commonly, if you want to automate / stream such a change I would suggest that you look into sed. Off the top of my head, consider something like this:
pg_dump --format=p --schema=schema_a original_database | sed 's/ schema_a./ schema_b./g' | psql new_database this is presuming a space before the schemaname, and a fullstop between schema and other elements. cheers Ben On Sat, 24 Jul 2021 at 01:38, Mayan <popal...@gmail.com> wrote: > Hi, > > > > I had a general question about a feature that we depended on heavily when > using other RDBMS providers which was the ability to take a schema dump and > restore it to a different database and a different schema in that database > (could be to the same database as well). Basically, there was an option > on restore to specify a FROMUSER and a TOUSER directive so schema A could > be restored elsewhere but as schema B. > > > > I don’t see such an option in Postgres and the only workaround I am aware > of is to do a plain-text (format=p) and then a crude find/replace to > replace the old schema name with the new schema name. I’ve never > actually tested this to be sure even this would work. Also, using this > option will prevent us from parallelizing the import or export, so it’s > really not something we want to do. > > > > This would be a really useful feature in my opinion along with the ability > to maintain parallelization options. > > > > Are there any such features on the roadmap? Is my understanding correct > about the available ways to accomplish this – again, in a practical and > performant way? > > > > Thanks, > > Mayan > -- [image: Ausvet Logo] <https://www.ausvet.com.au/> Dr Ben Madin BVMS MVPHMgmt PhD MANZCVS GAICD Managing Director Mobile: +61 448 887 220 <+61448887220> E-mail: b...@ausvet.com.au Website: www.ausvet.com.au Skype: benmadin Address: 5 Shuffrey Street Fremantle, WA 6160 Australia
