On 3/26/20 10:55 AM, David Gauthier wrote:
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.
Can we see an example view definition?
On Thu, Mar 26, 2020 at 1:34 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto: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 <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
