On Thu, 2020-03-26 at 13:16 -0400, David Gauthier wrote: > 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. > > 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 ?
The best way would be to use pg_dump to move the schema definitions over while preserving the "public" schema, and then use ALTER ... SET SCHEMA newschema; on all objects. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
