Re: 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(dot)schwarzer(at)grid(dot)unep(dot)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