Date: Tue, 21 Mar 2017 12:40:49 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Thorsten =?iso-8859-1?Q?Sch=F6ning?= <tschoening@am-soft.de>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: upgrade path from PG 8.3 to PG 9.5
Message-ID: <20170321164049.GN9812@tamriel.snowman.net>
References: <58AF1E37.90309@nrc-cnrc.gc.ca>
 <CAHw75vtP=FPdVeMZE5bWE6_+Mi0RzyXgr7fEAjDCvBmkkMpdcQ@mail.gmail.com>
 <722763388.20170321172524@am-soft.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="MvClVf7ictIISkbk"
Content-Disposition: inline
In-Reply-To: <722763388.20170321172524@am-soft.de>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--MvClVf7ictIISkbk
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Thorsten Sch=F6ning (tschoening@am-soft.de) wrote:
> Guten Tag Keith,
> am Donnerstag, 23. Februar 2017 um 18:57 schrieben Sie:
>=20
> > You can go directly from 8.3 to 9.5. Just be sure and use the
> > pg_dump from 9.5 to generate the dump file from 8.3.
>=20
> Is there a specific reason for that? My current approach for upgrades
> was dumping the old database using the old pg_dump, uninstalling the
> old Postgres, installing the new one and restore from the old dump.

Yes.  Using the older pg_dump may result in dumps that can't be restored
into the newer version of PG because things like keywords have been
added and must now be quoted.  There are possibly other things that have
been changed between the old version and the new one which would also
require the new pg_dump to be used, but keywords are the big one.

> That way I didn't accidently mix-up installations or use wrong tools
> or such.

Having a good, clean, way to install and handle multiple versions
concurrently is valuable not just for pg_dump but also for pg_upgrade
and just generally a good thing.  The PGDG RPM packages do an alright
job of this, and the Debian packaging is quite good at handling it.

> Did pg_dump have bugs in the past which resulted in pg_restore not
> being able to read the dumps?

This isn't generally an issue, but what goes into the dump file is
straight SQL for things like creating tables and such; pg_restore does
not do anything for dealing with keyword changes or anything like that.

Thanks!

Stephen

--MvClVf7ictIISkbk
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJY0VeQAAoJEO1sijiDR2RVpioQAMQSe8dLZLi9FNqunRminaYk
a2Z2E//sjmj9drDuNyPKshCOGPM6cXuPUpv4u5tqU37l8iZIuB7cVOx25LfHCLEs
/nEwKZJQMqLxp3ug1HQARY/fOFG0kIgBzFsJadiv5wzkZaTakDj/LEH5l27k1SGb
bao5Y930hXLl2qujOhejRQP/1N/O8wmR3G0xuXUOt/sIAvfVUtTDavSpnCZ0tF8h
bvYbPFKa+fM3kkviwlHi4+ltTQGkqnPg7gi0tzE/ZpFbL9zvjYMVTE4sOf1IoNX1
XFyBxttpAlEzaZwjUjWtTtl1/4RDzHyWlYbpTa/ICQmmOD5cpI/MQqTnqsQn/1JM
n8wnDKkRqiUnnWSfWZLQ91xXXuKYPABQOq3jcPgnHxelRFPtqLpRpIBiKC5IrxIG
7/gUR5cLE0dSW7mWL8zOnLDBdi3JLSoG9LDhnY1AoHJkqQsuk+BRdvmdOvdU92KR
BPDrCeuve/zPtVm6zWNLQ1c2oHz+y8fz7MqBUQegr9IZXVoWfs6B3IF6exD0Ojsp
RxJyoTlF6vaexqILkJtUeapSeDOi4UO6qS61JM4Z0sVV8FzOUsH8xAy1cu2faSWV
5K6JyPvKtM6b8V2bYCy42Xy0BciBnjsHo1STAxoK9JCcKKzvLSypcZE8uQltQLaJ
EqBwXZP5l6ESm49Rw/cu
=DwCj
-----END PGP SIGNATURE-----

--MvClVf7ictIISkbk--