Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 949F22E2D90 for ; Fri, 16 Nov 2007 01:44:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 91241-01 for ; Fri, 16 Nov 2007 01:44:47 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 3775C2E2D81 for ; Fri, 16 Nov 2007 01:44:47 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id lAG5ikD8072263 for ; Fri, 16 Nov 2007 01:44:46 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id lAG5icE6072168 for pgsql-admin@postgresql.org; Fri, 16 Nov 2007 01:44:38 -0400 (AST) (envelope-from news) From: "Marc G. Fournier" X-Newsgroups: pgsql.admin Subject: Re: Upgrading a database with pg_dumpall / psql ... Date: Fri, 16 Nov 2007 05:44:38 +0000 (UTC) Organization: Hub.Org Networking Services Lines: 54 Message-ID: References: <473CFA85.8000409@reynolds.com.au> X-Complaints-To: usenet@news.hub.org User-Agent: nn/6.7.3 To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/161 X-Sequence-Number: 27404 paul.lambert@reynolds.com.au (Paul Lambert) writes: >Marc G. Fournier From: wrote: >> This is one of those 'stupid questions I hate to ask' ... I've rechecked the >> docs to make sure I haven't missed something stupid, and unless I'm blind, >> I haven't ... >> >> I'm trying to upgrade a database from 8.1 -> 8.2 ... on a seperate server, >> with 8.2 pg_dump already installed, I do: >> >> pg_dumpall -U pgsql -h > dump.sql >> >> Works great. >> >> Then I upgrade the 8.1 server to 8.2, initdb, and try and do the restore >> doing: >> >> psql -U pgsql -h template1 < dump.sql >> >> And get a whack of: >> >> invalid command \n >> invalid command \ >> invalid command \ni >> invalid command \ >> invalid command \n >> invalid command \n >> invalid command \. >> >> and other errors ... so I'm obviously missing something that is probably >> obvious to everyone else ... am I dumping wrong? If I use '--inserts', >> it, of course, works like a charm, its only when I try and use COPY ... >> >Perhaps in your old install standard_conforming_strings was set to off? >Where in your new 8.2 install it is on by default. Check the >postgresql.conf settings in your new install comparing against your 8.1 >install, probably find some things particularly in the platform/version >compatibility section are different. After trying several things, and asking Tom offlist, what it turned out to be was that I had dump'd with slony configured in, and when it trying to rebuild the one table I was using slony with, it wasn't created properly, and everything cascaded from there ... Basically, if you want to restore a dump, make sure any/all 'modules' you have in your original dump exist before starting to restore ... tsearch2 will cause the above also, again, same reasons ... -- ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . scrappy@hub.org MSN . scrappy@hub.org Yahoo . yscrappy Skype: hub.org ICQ . 7615664