pg_restore (fromuser -> touser)

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:

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:

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)

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.

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: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.

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:

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

--

[image: Ausvet Logo] <https://www.ausvet.com.au/&gt;

Dr Ben Madin

BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
ben@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia

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!"