Dump schema without the functions

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