Thread: pg_dump and schemas
Hi All, Is there a way to pass a parameter to pg_dump that would make the produced dump be loaded into a different schema rather then the one it is being dumped from? Basically be able to say dump out of public, but write the dump so its restored to say "test1". Thanks, Rusty -- Rusty Conover InfoGears Inc. http://www.infogears.com
Hi Rusty, Try passing the output through a utility like sed, already there under Linux , but versions that work under Windows are available(eg, cygwin) eg, using a pipe: pg_dump -d.... | sed 's/public/test1/g' > dump.sql or converting a pg_dump output file: pg_dump <creates dump.sql> cat dump.sql | sed 's/public/test1/g' > dump2.sql With tools like these freely available, you don't really need to spend time reinventing them in your database applications.Of course. if you have the "public" schema name used elsewhere in your database, you may need to get a bit creativein your use of sed, but it can pretty well always be made to do this sort of operation. Cheers, Brent Wood >>> Rusty Conover <rconover@infogears.com> 05/04/08 8:42 AM >>> Hi All, Is there a way to pass a parameter to pg_dump that would make the produced dump be loaded into a different schema rather then the one it is being dumped from? Basically be able to say dump out of public, but write the dump so its restored to say "test1". Thanks, Rusty -- Rusty Conover InfoGears Inc. http://www.infogears.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hi Brent, It's not he best solution, because we could have fields containing "public" in their names and sed would happily change those to test1 as well. I'm looking for a safer solution, thats why it should be a part of pg_dump. Rusty On Apr 5, 2008, at 12:41 AM, Brent Wood wrote: > Hi Rusty, > > Try passing the output through a utility like sed, already there > under Linux , but versions that work under Windows are available > (eg, cygwin) > > eg, using a pipe: pg_dump -d.... | sed 's/public/test1/g' > dump.sql > > or converting a pg_dump output file: > > pg_dump <creates dump.sql> > cat dump.sql | sed 's/public/test1/g' > dump2.sql > > With tools like these freely available, you don't really need to > spend time reinventing them in your database applications. Of > course. if you have the "public" schema name used elsewhere in your > database, you may need to get a bit creative in your use of sed, but > it can pretty well always be made to do this sort of operation. > > Cheers, > > Brent Wood > > > >>>> Rusty Conover <rconover@infogears.com> 05/04/08 8:42 AM >>> > Hi All, > > Is there a way to pass a parameter to pg_dump that would make the > produced dump be loaded into a different schema rather then the one it > is being dumped from? Basically be able to say dump out of public, > but write the dump so its restored to say "test1". > > Thanks, > > Rusty > -- > Rusty Conover > InfoGears Inc. > http://www.infogears.com > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Rusty Conover InfoGears Inc. http://www.infogears.com
Hi Rusty, In which case can you not include the text around the schema & table creation commands to ensure other instances of "public"do not match the string? I'm not sure exactly what the pg_dump output contains, but you could use something like: cat pg_dump.sql | sed 's/CREATE SCHEMA "public"/CREATE SCHEMA "new_schema"/' | \ sed 's/CREATE TABLE "public"./CREATE TABLE "new_schema"./' > new_schema.sql This should avoid any ambiguity as to which instances of "public" you want changed in the pg_dump sql file. I think adding support for changing schema names in pg_dump would make it unnecessarily complex, as why just schemas? Alsorename databases, tables, columns, index names, change comments... I've yet to find something like this I couldn't do with sed, & if there was there is always awk for the truly desparate :-) pg_dump generates the dump, reliably, simply & safely. Any change you want from the original is not, IMHO, the role of abackup program. That should ONLY back up a replica of your data. Make changes afterwards if you like, but a backup programshouldn't modify your data. Just my 02c, & I ain't no Postgres developer, so I'm not speaking for them in this.... Cheers, Brent Wood Hi Brent, It's not he best solution, because we could have fields containing "public" in their names and sed would happily change those to test1 as well. I'm looking for a safer solution, thats why it should be a part of pg_dump. Rusty On Apr 5, 2008, at 12:41 AM, Brent Wood wrote: > Hi Rusty, > > Try passing the output through a utility like sed, already there > under Linux , but versions that work under Windows are available > (eg, cygwin) > > eg, using a pipe: pg_dump -d.... | sed 's/public/test1/g' > dump.sql