Re: pg_restore (fromuser -> touser) - Mailing list pgsql-general
| From | Vijaykumar Jain |
|---|---|
| Subject | Re: pg_restore (fromuser -> touser) |
| Date | |
| Msg-id | CAM+6J94RPD4KWempmdr0zGiuAqSDe5K6GK-14ku8UR3aqdux0g@mail.gmail.com Whole thread Raw |
| In response to | Re: pg_restore (fromuser -> touser) (Mayan <popalzie@gmail.com>) |
| List | pgsql-general |
On Sun, 25 Jul 2021 at 21:09, Mayan <popalzie@gmail.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@db:~/playground/demo$ createdb demo
postgres@db:~/playground/demo$ createuser demo
postgres@db:~/playground/demo$ pg_restore -d demo demo.db -- some dummy restore which has objects owned by demo user in demo schema
postgres@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)
pgsql-general by date: