pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc G. Fournier <scrappy@hub.org>
To: pgsql-admin@postgresql.org
Subject: Re: Upgrading a database with pg_dumpall / psql ...
Date: Fri, 16 Nov 2007 05:44:38 +0000 (UTC)
Message-ID: <fhjao6$23cn$1@news.hub.org> (raw)
References: <fhggvh$26dp$1@news.hub.org>
	<473CFA85.8000409@reynolds.com.au>

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 <host> > 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 <host> 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



view thread (3+ messages)

Message-ID: <fhjao6$23cn$1@news.hub.org>
Permalink:  ../fhjao6$23cn$1@news.hub.org/
Also on:    postgresql.org/message-id/fhjao6$23cn$1@news.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: scrappy@hub.org
  Subject: Re: Upgrading a database with pg_dumpall / psql ...
  In-Reply-To: <fhjao6$23cn$1@news.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox