Re: restore a pg_dumpall only breaks

>> Or maybe use "sed" to strip out the -reuse-previous options from pg_dumpall's output

Removing " -reuse-previous=on" from the pg_dumpall file worked. I still would like to upgrade to 9.2. As with my prior
email,if there is a recipe of sorts for upgrading pg8.4 to pg9.2 that would be great. 

Thanks

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, November 24, 2016 10:22 AM
To: Marc Fromm <Marc.Fromm@wwu.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restore a pg_dumpall only breaks

Marc Fromm <Marc.Fromm@wwu.edu> writes:
>> A dump from 9.2 is no sure thing to restore into an 8.1 database; there may be SQL syntax in it that 8.1 doesn't
understand. Have you checked for errors during the restore? 

> After the last successful database restore from the pg_dumpall file this is displayed on the ssh session.
> psql:pg_dbs.bkp:1029100: \connect: invalid connection option "-reuse-previous"

Ah.  That option was introduced quite recently as part of a security fix.
It's no surprise 8.x doesn't recognize it.

You could probably work around this by using pg_dumpall with -g to just dump roles and tablespaces, and then
pg_dump'ingindividual databases separately.  A mite tedious and error-prone.  Or maybe use "sed" to strip out the
-reuse-previousoptions from pg_dumpall's output.  But really this is the best answer, because 8.x is long out of
support:

> I figure the best thing to do is to upgrade postgres 8.4 (not 8.1 as previously mentioned) to 9.2 on this centOS6.4
server,so it's version matches the other server. I searched for some time on how to do this and every tutorial does it
alittle different and each has different steps that the others do not have. Is there a clear step by step procedure to
dothis upgrade? 

If you're working with locally-built copies of Postgres, I'd say just follow the recipe given in the pg_upgrade man
page. If you are working with someone's packaged version of Postgres, the packager may have provided a script or
somethingfor upgrades, in which case follow their recommendation.  Either way it's a good idea to make a backup
beforehandin case of disaster. 

            regards, tom lane