Обсуждение: Dump schema without the functions
Hi there, how can I dump a schema with all tables, but without the functions? Is there a way to do it, or do I have to manually drop the functions later when having used the pg_restore? Thanks for any advice, Stef
Вложения
> how can I dump a schema with all tables, but without the functions? Is > there a way to do it, or do I have to manually drop the functions > later when having used the pg_restore? Stef, You can edit the data between dump and restore, to comment out the function references. Or, you can use the "-L" argument with pg_restore to provide a list of the specific items you want to restore. For example: pg_dump -Fc mydb > db.dump pg_restore -l db.dump | grep -v FUNCTION > db.nofunc.dump pg_restore -d newdb db.nofunc.dump (assuming the word "FUNCTION" doesn't appear elsewhere in your schema object names. If it does, you might try appending the schema, such as grep -v "FUNCTION public") Adam
Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes: > how can I dump a schema with all tables, but without the functions? There's no built-in single command for that. You can accomplish it by using pg_restore -l to make a list of objects, then edit the list, then pg_restore -L to restore only the objects in the edited list. regards, tom lane
>> how can I dump a schema with all tables, but without the functions? > > There's no built-in single command for that. You can accomplish it by > using pg_restore -l to make a list of objects, then edit the list, > then pg_restore -L to restore only the objects in the edited list. Hmmm.. I probably should have mentioned that it's not a "normal" dump, but one including imported shapefiles. So my dump comes from this: pg_dump -Fc ... and - sorry, myself not being an expert - it seems to me that this file is not editable anymore. When I try to dump the file in text form, it gets rather big, and when trying to import it, I get this: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost Stef
Вложения
Stefan Schwarzer wrote: >>> how can I dump a schema with all tables, but without the functions? >> >> There's no built-in single command for that. You can accomplish it by >> using pg_restore -l to make a list of objects, then edit the list, >> then pg_restore -L to restore only the objects in the edited list. > > Hmmm.. I probably should have mentioned that it's not a "normal" dump, > but one including imported shapefiles. So my dump comes from this: > > pg_dump -Fc ... > > and - sorry, myself not being an expert - it seems to me that this file > is not editable anymore. No, but if you run pg_restore -l <my_dump_file> that will output a list of objects that IS editable. Then pg_restore -L ... will only restore the items in that list. -- Richard Huxton Archonet Ltd
>>>> how can I dump a schema with all tables, but without the functions? >>> >>> There's no built-in single command for that. You can accomplish >>> it by >>> using pg_restore -l to make a list of objects, then edit the list, >>> then pg_restore -L to restore only the objects in the edited list. >> Hmmm.. I probably should have mentioned that it's not a "normal" >> dump, but one including imported shapefiles. So my dump comes from >> this: >> pg_dump -Fc ... >> and - sorry, myself not being an expert - it seems to me that this >> file is not editable anymore. > > No, but if you run pg_restore -l <my_dump_file> that will output a > list of objects that IS editable. Then pg_restore -L ... will only > restore the items in that list. Ah, ok. Right, I can see that. But I don't really get how the final command will look like. pg_restore -L <file_without_FUNCTIONS> -d <my_database> But somewhere I have to indicate the original file, no? I mean, where do all my data now come from? The original dump contains all data; the newly created via pg_restore -l geodataportal.public | grep -v FUNCTION > pgdump.geodataportal.public.no-func has only the TOC. But no data... Thanks for any advice. Stef
Вложения
Stefan Schwarzer wrote: > But I don't really get how the final command will look like. > > pg_restore -L <file_without_FUNCTIONS> -d <my_database> > > But somewhere I have to indicate the original file, no? I mean, where do > all my data now come from? The original dump contains all data; the > newly created via > > pg_restore -l geodataportal.public | grep -v FUNCTION > > pgdump.geodataportal.public.no-func > > has only the TOC. But no data... Then you feed that file to pg_restore -L, along the original dump file. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>> But I don't really get how the final command will look like. >> >> pg_restore -L <file_without_FUNCTIONS> -d <my_database> >> >> But somewhere I have to indicate the original file, no? I mean, >> where do >> all my data now come from? The original dump contains all data; the >> newly created via >> >> pg_restore -l geodataportal.public | grep -v FUNCTION > >> pgdump.geodataportal.public.no-func >> >> has only the TOC. But no data... > > Then you feed that file to pg_restore -L, along the original dump > file. I mean, that was exactly my question, how this would look like... Ok, figured it out meanwhile: pg_restore -L pgdump.geodataportal.public.no-func -v pgdump.geodataportal.public -U xxx -d geodataportal Thanks for your help!! Stef