Search Postgresql Archives

Re: How to restore a dump containing CASTs into a database with a new user?

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

 



Guten Tag Christophe Pettus,
am Sonntag, 19. Juli 2020 um 23:38 schrieben Sie:

> In this case, you may need to change the ownership of the various
> objects directly in the database, rather than using dump/restore as
> a way of changing ownership all at once.

Does Postgres support that in an easy way, without the need to reverse
engineer an otherwise unknown the schema?

What I've read it does not, one always needs to know which objects are
used, needs to additionally handle newly created objects not used yet
etc. That seems very complicated when one simply wants to restore a
backup into a newly created database.

Additionally, who owns types on which level in the end? To
successfully restore, I needed to change ownership of type "inet" to
one new user. Is that per database restored into or for the whole
cluster now? Because I easily have multiple of the same databases for
different tenants and all those tenants are individual users which
would need to own that type "inet" to be able to restore their
individual databases.

> This is not infrequent
> when you have an existing database in which a superuser owns everything

In my setup it only seems to be necessary for the CASTs, everything
else seems to be properly reowned how I restore. And that approach
seems much easier than rewoning individual objects.

So depending on who owns the type "inet" on which level in the end,
Postgres could reown those types automatically during restores as
well. Looking at the provided arguments and used commands, it's very
clear what the user wants.

OTOH, if that type is owned on a higher level than the restored
database itself only, that seems to be a design issue. Because that
would prevent other users owning that type for their individual
databases as well.

Mit freundlichen Grüßen,

Thorsten Schöning

-- 
Thorsten Schöning       E-Mail: Thorsten.Schoening@xxxxxxxxxx
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow







[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 Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux