X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A1EA09DC865 for ; Mon, 23 Jan 2006 12:49:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84864-04 for ; Mon, 23 Jan 2006 12:49:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail07.powweb.com (mail07.powweb.com [66.152.97.40]) by postgresql.org (Postfix) with ESMTP id 9494E9DC804 for ; Mon, 23 Jan 2006 12:49:32 -0400 (AST) Received: from [10.1.0.10] (unknown [82.76.86.38]) by mail07.powweb.com (Postfix) with ESMTP id D72C614DB18; Mon, 23 Jan 2006 08:49:30 -0800 (PST) Message-ID: <43D5091C.9010608@bitsp.com> Date: Mon, 23 Jan 2006 18:49:32 +0200 From: Ciprian Hodorogea Reply-To: ciprian.hodorogea@bitsp.com Organization: IntelliSoft SRL User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Michael Fuhr CC: Tom Lane , pgsql-admin@postgresql.org Subject: Re: pg_dump - txt sql vs binary References: <20060122204132.9567D14DB13@mail07.powweb.com> <22930.1137973880@sss.pgh.pa.us> <43D4E9A8.8050005@bitsp.com> <20060123155729.GA23544@winnie.fuhr.org> In-Reply-To: <20060123155729.GA23544@winnie.fuhr.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/269 X-Sequence-Number: 20357 Command I have used: pg_dump.exe -i -h localhost -p 5432 -U postgres -F p -v -f "C:\test.sql" bicms_server This will put in the output SQL file postgres functions also (not only those defined by me), for example: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE c; CREATE TYPE tablefunc_crosstab_2 AS ( row_name text, category_1 text, category_2 text ); CREATE FUNCTION connectby(text, text, text, text, integer, text) RETURNS SETOF record AS '$libdir/tablefunc', 'connectby_text' LANGUAGE c STABLE STRICT; ALTER FUNCTION public.connectby(text, text, text, text, integer, text) OWNER TO postgres; ...and many others. Anyway, my question would be: if I have postgres 8.1.x installed on computer A and the same version of postgres installed on computer B, which would be your recommended way of backing up data on computer A and put it on computer B? Regards, Ciprian Michael Fuhr wrote: > On Mon, Jan 23, 2006 at 04:35:20PM +0200, Ciprian Hodorogea wrote: > >>>>2. All dumps generated with pg_dump seem to contain internal functions >>>>also, which then raise problems when trying to import. Can these be >>>>somehow excluded from the generated SQL backup? >> >>Is there a way to differentiate postgres internal functions from >>user-defined functions? (other but making up a list of those functions >>names) > > > What "internal" functions are you talking about? What's the exact > command you're running and what's the exact error message or > unexpected behavior you're seeing? >