Re: [ADMIN] upgrade path from PG 8.3 to PG 9.5

On 21/03/2017 21:00, Scott Marlowe wrote:

On Tue, Mar 21, 2017 at 10:54 AM, Stephen Frost <sfrost@snowman.net> wrote:

Greetings,

* David G. Johnston (david.g.johnston@gmail.com) wrote:

On Tue, Mar 21, 2017 at 9:40 AM, Stephen Frost <sfrost@snowman.net> wrote:

* Thorsten Schöning (tschoening@am-soft.de) wrote:

Guten Tag Keith,
am Donnerstag, 23. Februar 2017 um 18:57 schrieben Sie:

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.

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.

The proper solution to the keyword/identifier quoting problem is to use
"--quote-all-identifiers".

Using the version of pg_dump to which you are upgrading is an entirely
supported and 'proper' approach to dealing with that issue.  Using
--quote-all-identifiers, in my opinion anyway, leads to rather ugly
results.

As I also mention, there are other things in newer versions that pg_dump
does its best to address (including things like checking for role names
in older versions starting with "pg_", which is not allowed in 9.6+).
Using the pg_dump from the version of PG to which you are upgrading is
the correct and supported approach to doing upgrades.  Using
--quote-all-identifiers is not.

Yeah there are plenty of little road bumps to hit especially when
going from something  as old as 8.3 to 9.5. emcoding issues pop up etc
etc.

Using the new version to dump is THE way to go if you are gonna dump restore.

IF you need the minimal amount of downtime possible, the only
reasonable solution is some form of logical replication like slony or
longdiste etc.

We used pg_upgrade recently for migrating from 8.3 -> 9.4 (the last version which supports 8.3), and it went nice. We had some custom functions in C, and pg_upgrade is a disciplined way to deal with the migration.
We will deploy this migration procedure to over 100 servers running 8.3.

According to this page: http://slony.info/documentation/requirements.html

slony 2.2.5 supports versions from 8.3 to 9.5 so you can upgrade from
one to the other in one fell swoop and your downtime will be measured
in seconds, or minutes at most. I suggest create a pair of test
servers running 8.3 and 9.5 to test the migration on ahead of time.
If you don't need to keep downtime to a minimum dump/restore is WAY
easier and you can do a test run while the main server is running just
by taking a backup and see if it restores cleanly and properly to the
new 9.5 machine. If you do want minimal downtime, learning slony or
some other logical replication system is worth your time. Slony docs
have a fairly old but still mostly accurate how to page here:
http://slony.info/documentation/1.2/versionupgrade.html

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

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