Re: pg_restore (fromuser -> touser)

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 <popalzie(at)gmail(dot)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:
ben(at)ausvet(dot)com(dot)au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia