Thread: pg_restore (fromuser -> touser)

pg_restore (fromuser -> touser)

From
Mayan
Date:

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

Re: pg_restore (fromuser -> touser)

From
Ninad Shah
Date:
This is true. Such a feature is not available in PostgreSQL.

What you need to do is you have to take a structure dump, and change the schema name as per required. And, then, you may copy the data.


Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 23:08, Mayan <popalzie@gmail.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

Re: pg_restore (fromuser -> touser)

From
Mayan
Date:
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. 

Thanks,
Mayan

On Fri, Jul 23, 2021, 10:58 PM Ninad Shah <nshah.postgres@gmail.com> wrote:
This is true. Such a feature is not available in PostgreSQL.

What you need to do is you have to take a structure dump, and change the schema name as per required. And, then, you may copy the data.


Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 23:08, Mayan <popalzie@gmail.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

Re: pg_restore (fromuser -> touser)

From
Vijaykumar Jain
Date:
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)

 

Re: pg_restore (fromuser -> touser)

From
"David G. Johnston"
Date:
On Sun, Jul 25, 2021 at 8:39 AM 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. 

You just did.  But unless you are going to fund or help with actual development I don't see this going very far.  This has been inquired about many times in the past yet the feature still does not exist.

David J.

Re: pg_restore (fromuser -> touser)

From
Ganesh Korde
Date:
You just need to export dump without any privileges. And while restoring dump use the new role.

Regards,
Ganesh Korde.

On Mon, 26 Jul 2021, 1:01 am David G. Johnston, <david.g.johnston@gmail.com> wrote:
On Sun, Jul 25, 2021 at 8:39 AM 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. 

You just did.  But unless you are going to fund or help with actual development I don't see this going very far.  This has been inquired about many times in the past yet the feature still does not exist.

David J.

Re: pg_restore (fromuser -> touser)

From
"David G. Johnston"
Date:
On Sun, Jul 25, 2021 at 8:02 PM Ganesh Korde <ganeshakorde@gmail.com> wrote:
You just need to export dump without any privileges. And while restoring dump use the new role.

You should read the body of the original email and not just the subject line.  The actual question pertains to schemas - which IIUC are tightly linked to roles in other DBs (hence the observed behavior elsewhere) but aside from some default search_path stuff are unrelated in PostgreSQL.

David J.

Re: pg_restore (fromuser -> touser)

From
Mayan
Date:
Thanks for the responses everyone and the reality check of how new features get in.  I will look into the suggestion by Vijaykumar in more detail and try it out.

Thanks again,
Mayan 

On Sun, Jul 25, 2021 at 10:11 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sun, Jul 25, 2021 at 8:02 PM Ganesh Korde <ganeshakorde@gmail.com> wrote:
You just need to export dump without any privileges. And while restoring dump use the new role.

You should read the body of the original email and not just the subject line.  The actual question pertains to schemas - which IIUC are tightly linked to roles in other DBs (hence the observed behavior elsewhere) but aside from some default search_path stuff are unrelated in PostgreSQL.

David J.

Re: pg_restore (fromuser -> touser)

From
Ben Madin
Date:
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@gmail.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



--

Ausvet Logo

Dr Ben Madin
 
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Skype: benmadin
Address: 
5 Shuffrey Street
Fremantle, WA 6160
Australia

Re: pg_restore (fromuser -> touser)

From
"Peter J. Holzer"
Date:
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!"

Attachment