Re: pg_restore (fromuser -> touser)

On Sun, 25 Jul 2021 at 21:09, Mayan <popalzie(at)gmail(dot)com> wrote:

> Thanks for your reply. Is this something that I can request as a feature
> add? I don't think it should be too much of effort (based on my limited
> source code knowledge), but I'm not familiar with the process to request a
> feature.
>

although there is not flag in command line, but you always run the alter
<something> rename to <something else> for role and schema post restore.
i can be corrected, if i am missing anything. i tried with pagilla db, but
just keeping it simple here.
PS: pg_hba.conf needs to be updated with new role/user post restore, or
else user will not be able to connect.

postgres(at)db:~/playground/demo$ createdb demo
postgres(at)db:~/playground/demo$ createuser demo
postgres(at)db:~/playground/demo$ pg_restore -d demo demo.db -- some dummy
restore which has objects owned by demo user in demo schema

postgres(at)db:~/playground/demo$ psql demo
psql (14beta1)
Type "help" for help.

demo=# \dt demo.*
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
demo | t | table | demo
(1 row)

demo=# \ds demo.t_id_seq
List of relations
Schema | Name | Type | Owner
--------+----------+----------+-------
demo | t_id_seq | sequence | demo
(1 row)

demo=# \df demo.*
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------+------------------+---------------------+------
demo | trig_func | trigger | | func
(1 row)

demo=# \dnS demo
List of schemas
Name | Owner
------+-------
demo | demo
(1 row)

---the below to sql statements will probably do what you want in your
restore command line flag, you need not edit dump file by hand imho

*demo=# alter schema demo rename to production;*
*ALTER SCHEMA*
*demo=# alter role demo rename to production;*
*ALTER ROLE*

--validate
demo=# \dnS demo -- no more demo schema
List of schemas
Name | Owner
------+-------
(0 rows)

demo=# \dnS
List of schemas
Name | Owner
--------------------+------------
information_schema | postgres
pg_catalog | postgres
pg_toast | postgres
production | production
public | postgres
(5 rows)

demo=# \dt production.*
List of relations
Schema | Name | Type | Owner
------------+------+-------+------------
production | t | table | production
(1 row)

demo=# \df production.trig_func
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------+------------------+---------------------+------
production | trig_func | trigger | | func
(1 row)

demo=# \ds production.t_id_seq
List of relations
Schema | Name | Type | Owner
------------+----------+----------+------------
production | t_id_seq | sequence | production
(1 row)