pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Upgrading a database with pg_dumpall / psql ...
3+ messages / 3 participants
[nested] [flat]

* Upgrading a database with pg_dumpall / psql ...
@ 2007-11-15 04:12  "Marc G. Fournier From": <scrappy@hub.org>
  0 siblings, 1 reply; 3+ messages in thread

From: "Marc G. Fournier From": @ 2007-11-15 04:12 UTC (permalink / raw)
  To: pgsql-admin

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

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Upgrading a database with pg_dumpall / psql ...
@ 2007-11-16 02:03  Paul Lambert <paul.lambert@reynolds.com.au>
  parent: "Marc G. Fournier From": <scrappy@hub.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Paul Lambert @ 2007-11-16 02:03 UTC (permalink / raw)
  To: pgsql-admin

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.

-- 
Paul Lambert
Database Administrator
AutoLedgers



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Upgrading a database with pg_dumpall / psql ...
@ 2007-11-16 05:44  Marc G. Fournier <scrappy@hub.org>
  parent: Paul Lambert <paul.lambert@reynolds.com.au>
  0 siblings, 0 replies; 3+ messages in thread

From: Marc G. Fournier @ 2007-11-16 05:44 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2007-11-16 05:44 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-15 04:12 Upgrading a database with pg_dumpall / psql ... "Marc G. Fournier From": <scrappy@hub.org>
2007-11-16 02:03 ` Paul Lambert <paul.lambert@reynolds.com.au>
2007-11-16 05:44   ` Marc G. Fournier <scrappy@hub.org>

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