Search Postgresql Archives

Re: pg_restore (fromuser -> touser)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, 25 Jul 2021 at 21:09, Mayan <popalzie@xxxxxxxxx> 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)

 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux