From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marc Fromm <Marc.Fromm@wwu.edu>
cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: restore a pg_dumpall only breaks
In-reply-to: 
 <CY1PR0201MB1436CFF069435D629954DF1C9DB60@CY1PR0201MB1436.namprd02.prod.outlook.com>
References: 
 <CY1PR0201MB14360D78FE30F98FFAB1E00B9DB60@CY1PR0201MB1436.namprd02.prod.outlook.com>
 <13942.1479953090@sss.pgh.pa.us>
 <CY1PR0201MB1436CFF069435D629954DF1C9DB60@CY1PR0201MB1436.namprd02.prod.outlook.com>
Comments: In-reply-to Marc Fromm <Marc.Fromm@wwu.edu>
	message dated "Thu, 24 Nov 2016 06:07:51 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <22571.1480011741.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 24 Nov 2016 13:22:21 -0500
Message-ID: <22572.1480011741@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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-prev=
ious"

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'ing individual databases
separately.  A mite tedious and error-prone.  Or maybe use "sed" to strip
out the -reuse-previous options 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 prev=
iously 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 tuto=
rial does it a little different and each has different steps that the other=
s do not have. Is there a clear step by step procedure to do this 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 something for upgrades, in which case follow their
recommendation.  Either way it's a good idea to make a backup beforehand
in case of disaster.

			regards, tom lane


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin