Re: pg_restore (fromuser -> touser)

On 2021-07-27 09:43:45 +0800, Ben Madin wrote:
> 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.

Actually the "." in a regular expression matches any character, so this
will also replace e.g. " schema_a5" with " schema_b.". This is easily
fixed (just add a backslash), but more importantly, it will replace that
string everywhere, regardless of context. This might lead to syntax
errors or data corruption.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"