Thanks Adrian for the quick reply.
I don't have a lot of choice regarding PG version. I work for a large corp with an IT dept which offers the version I have. They create VMs which are DB servers and this is the best they offer. But I could request something newer. Never hurts to try.
Ya, I kinda figured that there's nothing wrong with referencing tables from the default (public) schema. So I tried to redefine the view by referencing the public tables literally, as in "public.thetable". The plan was to do some sort of global replace of "public." with "myschem." in the output of pg_dump, maybe with sed or something. But even after explicitly using "public.", it didn't stick in the view def.
On Thu, Mar 26, 2020 at 1:34 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 3/26/20 10:16 AM, David Gauthier wrote:
> Here's an interesting one for you...
> psql (9.6.7, server 11.3) on linux
>
> I have 2 DBs, differnet servers/instances. I want to take all the
> metadata and data for a set of tables/views in the public schema of one
> DB and move it all over to be inside a schema of a second DB/instance.
Well first, the current minor version of 9.6 is .17 so you are 10
releases behind. In fact the 9.6.8 release includes changes that impact
the below:
https://www.postgresql.org/docs/9.6/release-9-6-8.html
>
> I'm using pg_dump to create the script and I believe I can insert a "set
> search_path=myschem" in the output of pg_dump such that when it runs,
> the "CREATE TABLE", "CREATE VIEW", "GRANT...", etc.... commands, will
> all go into the new schema (which I have prepared). Problem is the view
> defs.
> The view defs do not prefix the referenced tables with "myschem.", so
> the CREATE VIEW xyx commands fail.
>
> Is there a way to do this ?
By manually changing the definition? It is not an error for a VIEW in
one schema to refer to tables in other schemas. AFAIK the code has no
way of knowing you want to move the underlying tables just by specifying
a search_path.
>
> Thanks in Advance.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
