Обсуждение: Upgrading a database with pg_dumpall / psql ...
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
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
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