pg_restore (fromuser -> touser)
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)
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 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 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)
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.johns...@gmail.com> wrote: > On Sun, Jul 25, 2021 at 8:02 PM Ganesh Korde > 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. > >
