From ciprian.hodorogea@bitsp.com Sun Jan 22 20:41:35 2006 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 C75829DC998 for ; Sun, 22 Jan 2006 16:41: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 47970-04 for ; Sun, 22 Jan 2006 16:41:35 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 959D09DC9CA for ; Sun, 22 Jan 2006 16:41:32 -0400 (AST) Received: from mail07.powweb.com (mail07.powweb.com [66.152.97.40]) by svr4.postgresql.org (Postfix) with ESMTP id 87AE65AF83A for ; Sun, 22 Jan 2006 20:41:34 +0000 (GMT) Received: from hodoman (home-307494.b.astral.ro [193.231.126.246]) by mail07.powweb.com (Postfix) with ESMTP id 9567D14DB13 for ; Sun, 22 Jan 2006 12:41:32 -0800 (PST) Reply-To: From: "Ciprian Hodorogea" To: Subject: pg_dump - txt sql vs binary Date: Sun, 22 Jan 2006 22:41:27 +0200 Organization: IntelliSoft SRL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0028_01C61FA4.FCA383F0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcYflDewaAMLez3yQ6ucWNsP/jb6dA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20060122204132.9567D14DB13@mail07.powweb.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/258 X-Sequence-Number: 20346 This is a multi-part message in MIME format. ------=_NextPart_000_0028_01C61FA4.FCA383F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi All, 1. I noticed that binary dumps created with Postgres 8.0.3 can not be imported with Postgres 8.1.x, so this is not a good backup idea. 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? All I want is to obtain a valid SQL file that I can then import within any upcoming version of Postgres. Does anyone have any suggestion please? Best regards, Ciprian Hodorogea IntelliSoft SRL http://www.bitsp.com/ ------=_NextPart_000_0028_01C61FA4.FCA383F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi All,

 

  1. I noticed that binary dumps created with Postgres 8.0.3 can not be imported with Postgres 8.1.x, so this is not a = good backup idea.

 

  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? =

 

  All I want is to obtain a valid SQL file = that I can then import within any upcoming version of Postgres.  Does anyone = have any suggestion please?

 

Best regards,

 

Ciprian Hodorogea

IntelliSoft = SRL

http://www.bitsp.com/

 

------=_NextPart_000_0028_01C61FA4.FCA383F0-- From tgl@sss.pgh.pa.us Sun Jan 22 23:51:22 2006 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 5A9029DC89E for ; Sun, 22 Jan 2006 19:51:22 -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 75284-06 for ; Sun, 22 Jan 2006 19:51:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id F07729DC86D for ; Sun, 22 Jan 2006 19:51:19 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0MNpKLa022931; Sun, 22 Jan 2006 18:51:20 -0500 (EST) To: ciprian.hodorogea@bitsp.com cc: pgsql-admin@postgresql.org Subject: Re: pg_dump - txt sql vs binary In-reply-to: <20060122204132.9567D14DB13@mail07.powweb.com> References: <20060122204132.9567D14DB13@mail07.powweb.com> Comments: In-reply-to "Ciprian Hodorogea" message dated "Sun, 22 Jan 2006 22:41:27 +0200" Date: Sun, 22 Jan 2006 18:51:20 -0500 Message-ID: <22930.1137973880@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.096 required=5 tests=[AWL=0.096] X-Spam-Score: 0.096 X-Spam-Level: X-Archive-Number: 200601/259 X-Sequence-Number: 20347 "Ciprian Hodorogea" writes: > 1. I noticed that binary dumps created with Postgres 8.0.3 can not be > imported with Postgres 8.1.x, so this is not a good backup idea. If you mean a filesystem dump, no. > 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? Details please? regards, tom lane From ciprian.hodorogea@bitsp.com Mon Jan 23 14:35:21 2006 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 9DD159DC806 for ; Mon, 23 Jan 2006 10:35:21 -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 59177-01 for ; Mon, 23 Jan 2006 10:35:25 -0400 (AST) X-Greylist: delayed 17:53:49.286311 by SQLgrey- Received: from mail07.powweb.com (mail07.powweb.com [66.152.97.40]) by postgresql.org (Postfix) with ESMTP id 399349DC809 for ; Mon, 23 Jan 2006 10:35:16 -0400 (AST) Received: from [10.1.0.10] (unknown [82.76.86.38]) by mail07.powweb.com (Postfix) with ESMTP id DCF4E14DBE3; Mon, 23 Jan 2006 06:35:18 -0800 (PST) Message-ID: <43D4E9A8.8050005@bitsp.com> Date: Mon, 23 Jan 2006 16:35:20 +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: Tom Lane CC: pgsql-admin@postgresql.org Subject: Re: pg_dump - txt sql vs binary References: <20060122204132.9567D14DB13@mail07.powweb.com> <22930.1137973880@sss.pgh.pa.us> In-Reply-To: <22930.1137973880@sss.pgh.pa.us> 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 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/265 X-Sequence-Number: 20353 >> 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) Regards, Ciprian From tgl@sss.pgh.pa.us Mon Jan 23 15:56:42 2006 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 DBEB59DC8A2 for ; Mon, 23 Jan 2006 11:56:42 -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 72796-07 for ; Mon, 23 Jan 2006 11:56:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 3ADE59DC813 for ; Mon, 23 Jan 2006 11:56:37 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0NFueUo005774; Mon, 23 Jan 2006 10:56:41 -0500 (EST) To: ciprian.hodorogea@bitsp.com cc: pgsql-admin@postgresql.org Subject: Re: pg_dump - txt sql vs binary In-reply-to: <43D4E9A8.8050005@bitsp.com> References: <20060122204132.9567D14DB13@mail07.powweb.com> <22930.1137973880@sss.pgh.pa.us> <43D4E9A8.8050005@bitsp.com> Comments: In-reply-to Ciprian Hodorogea message dated "Mon, 23 Jan 2006 16:35:20 +0200" Date: Mon, 23 Jan 2006 10:56:40 -0500 Message-ID: <5773.1138031800@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.096 required=5 tests=[AWL=0.096] X-Spam-Score: 0.096 X-Spam-Level: X-Archive-Number: 200601/266 X-Sequence-Number: 20354 Ciprian Hodorogea writes: > 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) I can think of several possibilities, but it's not clear what you need to accomplish. Would you show us details of the problem you're having, rather than jumping to conclusions about how to fix it? Bear in mind that the rest of the world has found no need to do any such thing while using pg_dump. regards, tom lane From mike@fuhr.org Mon Jan 23 15:57:34 2006 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 662109DC9A0 for ; Mon, 23 Jan 2006 11:57:34 -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 73587-03 for ; Mon, 23 Jan 2006 11:57:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id BD9FB9DC97A for ; Mon, 23 Jan 2006 11:57:30 -0400 (AST) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k0NFvUYG025712 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Mon, 23 Jan 2006 08:57:32 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k0NFvTMa024183; Mon, 23 Jan 2006 08:57:29 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k0NFvTqK024182; Mon, 23 Jan 2006 08:57:29 -0700 (MST) (envelope-from mfuhr) Date: Mon, 23 Jan 2006 08:57:29 -0700 From: Michael Fuhr To: Ciprian Hodorogea Cc: Tom Lane , pgsql-admin@postgresql.org Subject: Re: pg_dump - txt sql vs binary Message-ID: <20060123155729.GA23544@winnie.fuhr.org> References: <20060122204132.9567D14DB13@mail07.powweb.com> <22930.1137973880@sss.pgh.pa.us> <43D4E9A8.8050005@bitsp.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43D4E9A8.8050005@bitsp.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.107 required=5 tests=[AWL=0.107] X-Spam-Score: 0.107 X-Spam-Level: X-Archive-Number: 200601/267 X-Sequence-Number: 20355 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? -- Michael Fuhr From ciprian.hodorogea@bitsp.com Mon Jan 23 16:49:35 2006 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? > From tgl@sss.pgh.pa.us Mon Jan 23 17:24:27 2006 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 9162F9DC813 for ; Mon, 23 Jan 2006 13:24:27 -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 90996-04 for ; Mon, 23 Jan 2006 13:24:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9B5389DC804 for ; Mon, 23 Jan 2006 13:24:24 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0NHN5sH006689; Mon, 23 Jan 2006 12:23:05 -0500 (EST) To: ciprian.hodorogea@bitsp.com cc: Michael Fuhr , pgsql-admin@postgresql.org Subject: Re: pg_dump - txt sql vs binary In-reply-to: <43D5091C.9010608@bitsp.com> References: <20060122204132.9567D14DB13@mail07.powweb.com> <22930.1137973880@sss.pgh.pa.us> <43D4E9A8.8050005@bitsp.com> <20060123155729.GA23544@winnie.fuhr.org> <43D5091C.9010608@bitsp.com> Comments: In-reply-to Ciprian Hodorogea message dated "Mon, 23 Jan 2006 18:49:32 +0200" Date: Mon, 23 Jan 2006 12:23:05 -0500 Message-ID: <6688.1138036985@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.096 required=5 tests=[AWL=0.096] X-Spam-Score: 0.096 X-Spam-Level: X-Archive-Number: 200601/271 X-Sequence-Number: 20359 Ciprian Hodorogea writes: > 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; Those *were* all defined by you, at some point, because they aren't preloaded in the system. Perhaps you have forgotten running the contrib-module installation scripts, but you evidently did. If you are having difficulty in reloading the definitions it suggests that your new installation is lacking the contrib modules. You could install them, or manually remove the unwanted stuff from the dump file, or drop the functions from the source installation before making a dump. (Some but not all of the contrib modules have "uninstall" scripts, which would help with the last alternative.) > 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? pg_dump is certainly the preferred method. regards, tom lane From ciprian.hodorogea@bitsp.com Tue Jan 24 08:34:18 2006 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 1BAFA9DCA45 for ; Tue, 24 Jan 2006 04:34:18 -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 07336-01 for ; Tue, 24 Jan 2006 04:34:18 -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 CBD4E9DC9F8 for ; Tue, 24 Jan 2006 04:34:15 -0400 (AST) Received: from [10.1.0.10] (unknown [82.76.86.38]) by mail07.powweb.com (Postfix) with ESMTP id 8F7AA14DAFF; Tue, 24 Jan 2006 00:34:15 -0800 (PST) Message-ID: <43D5E66E.9020002@bitsp.com> Date: Tue, 24 Jan 2006 10:33:50 +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: Tom Lane CC: Michael Fuhr , 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> <43D5091C.9010608@bitsp.com> <6688.1138036985@sss.pgh.pa.us> In-Reply-To: <6688.1138036985@sss.pgh.pa.us> 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/273 X-Sequence-Number: 20361 > > pg_dump is certainly the preferred method. > I have Postgres 8.1.1 installed on a Windows machine and on a Linux machine. When I do pg_dump (from Windows) and then pg_restore from Linux, I get 52 errors, which I ignore and things seem to work fine, but I suppose something is wrong about this... It is only the setup from the website that I have installed and not other contribs. Best Regards, Ciprian From mike@fuhr.org Tue Jan 24 21:24:13 2006 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 2E3C19DC85B for ; Tue, 24 Jan 2006 17:24:13 -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 39148-10 for ; Tue, 24 Jan 2006 17:24:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id DC0189DC801 for ; Tue, 24 Jan 2006 17:24:10 -0400 (AST) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k0OLO4BE027378 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 24 Jan 2006 14:24:07 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k0OLO4Lf091469; Tue, 24 Jan 2006 14:24:04 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k0OLO4wx091468; Tue, 24 Jan 2006 14:24:04 -0700 (MST) (envelope-from mfuhr) Date: Tue, 24 Jan 2006 14:24:04 -0700 From: Michael Fuhr To: Ciprian Hodorogea Cc: Tom Lane , pgsql-admin@postgresql.org Subject: Re: pg_dump - txt sql vs binary Message-ID: <20060124212404.GA91387@winnie.fuhr.org> References: <20060122204132.9567D14DB13@mail07.powweb.com> <22930.1137973880@sss.pgh.pa.us> <43D4E9A8.8050005@bitsp.com> <20060123155729.GA23544@winnie.fuhr.org> <43D5091C.9010608@bitsp.com> <6688.1138036985@sss.pgh.pa.us> <43D5E66E.9020002@bitsp.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43D5E66E.9020002@bitsp.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200601/278 X-Sequence-Number: 20366 On Tue, Jan 24, 2006 at 10:33:50AM +0200, Ciprian Hodorogea wrote: > >pg_dump is certainly the preferred method. > > I have Postgres 8.1.1 installed on a Windows machine and on a Linux > machine. When I do pg_dump (from Windows) and then pg_restore from > Linux, I get 52 errors, which I ignore and things seem to work fine, but > I suppose something is wrong about this... As Tom mentioned, the Windows box apparently has some contributed modules that the Linux box doesn't, so when you try to restore the catalog entries on Linux you get errors. If you know you don't need those modules then you can ignore the errors; if you'd like to have an error-free restore then either add the modules to Linux, remove them from Windows, or delete them from the backup. If you're using pg_restore then you could use the -l/--list and -L/--use-list options to omit certain objects from the restore. See the documentation for an example. > It is only the setup from the website that I have installed and not > other contribs. Did you build from source or did you install a pre-built package? If from source then the contributed modules are in the contrib directory; if from a package then look around for another package that contains the contributed modules. -- Michael Fuhr From systemguards@gmail.com Tue Jan 24 21:43:39 2006 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 F02339DCC83 for ; Tue, 24 Jan 2006 17:43:39 -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 42931-07 for ; Tue, 24 Jan 2006 17:43:41 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.207]) by postgresql.org (Postfix) with ESMTP id 9A6CB9DCC13 for ; Tue, 24 Jan 2006 17:43:35 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 57so1203886wri for ; Tue, 24 Jan 2006 13:43:37 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=qenOUammAqF5h+kCOstqW2IqlDw34rROrHNMT09rbI9JIKfpLgqNXlJdALFU8HsB+ubk/JReTSM8r+741ZdcfKFeYXay4Io7+BdAem0TYDNJ8PVKmpASdUirV3zfLDhlTFhZuqyT77PDHTeZBEhQYYKbowR2rGZiWs/3xj/3ef8= Received: by 10.54.93.14 with SMTP id q14mr567852wrb; Tue, 24 Jan 2006 13:43:37 -0800 (PST) Received: by 10.54.93.8 with HTTP; Tue, 24 Jan 2006 13:43:37 -0800 (PST) Message-ID: Date: Tue, 24 Jan 2006 16:43:37 -0500 From: Jaime Casanova To: Michael Fuhr Subject: Re: pg_dump - txt sql vs binary Cc: Ciprian Hodorogea , Tom Lane , pgsql-admin@postgresql.org In-Reply-To: <20060124212404.GA91387@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20060122204132.9567D14DB13@mail07.powweb.com> <22930.1137973880@sss.pgh.pa.us> <43D4E9A8.8050005@bitsp.com> <20060123155729.GA23544@winnie.fuhr.org> <43D5091C.9010608@bitsp.com> <6688.1138036985@sss.pgh.pa.us> <43D5E66E.9020002@bitsp.com> <20060124212404.GA91387@winnie.fuhr.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.091 required=5 tests=[AWL=0.091] X-Spam-Score: 0.091 X-Spam-Level: X-Archive-Number: 200601/279 X-Sequence-Number: 20367 > On Tue, Jan 24, 2006 at 10:33:50AM +0200, Ciprian Hodorogea wrote: > > > > I have Postgres 8.1.1 installed on a Windows machine and on a Linux > > machine. When I do pg_dump (from Windows) and then pg_restore from > > Linux, I get 52 errors, which I ignore and things seem to work fine, bu= t > > I suppose something is wrong about this... > > > It is only the setup from the website that I have installed and not > > other contribs. > > Did you build from source or did you install a pre-built package? > If from source then the contributed modules are in the contrib > directory; if from a package then look around for another package > that contains the contributed modules. > when installing from the pg_installer (http://pginstaller.projects.postgresql.org) it let you to install contrib modules just selecting them (and easy way to do it... so they appear to newbies as just other options in the server, and not as contrib modules), also it defaults to install plpgsql in template1 and let you install and activate (i don't know what it refers with 'activate') postgis and pl/java when selecting options for the server... it also install some pgadmin support functions by default... just ignore warnings works for me because i don't use most of these things, and for plpgsql i create it by hand in linux before run the script generated by pg_dump -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)