Re: upgrade path from PG 8.3 to PG 9.5

On Wed, Mar 22, 2017 at 3:09 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> On 21/03/2017 21:00, Scott Marlowe wrote:
>
>> On Tue, Mar 21, 2017 at 10:54 AM, Stephen Frost <sfrost(at)snowman(dot)net>
>> wrote:
>>
>>> Greetings,
>>>
>>> * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
>>>
>>>> On Tue, Mar 21, 2017 at 9:40 AM, Stephen Frost <sfrost(at)snowman(dot)net>
>>>> wrote:
>>>>
>>>>> * Thorsten Schöning (tschoening(at)am-soft(dot)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.
>

I would be curious how that worked since the documentation states the old
version must be at least 8.4.7 for pg_upgrade to work.

https://www.postgresql.org/docs/9.6/static/pgupgrade.html

Just checked this with 8.3.22 and confirmed the binary won't even let you
run it.

$ pg_upgrade -b /opt/pgsql8323/bin -B /opt/pgsql962/bin -c -d
/opt/pgdata/83 -D /opt/pgdata/83upgrade
Performing Consistency Checks
-----------------------------
Checking cluster versions
This utility can only upgrade from PostgreSQL version 8.4 and later.
Failure, exiting

Not trying to be pedantic, but don't want to lead someone down a road that
won't work properly.

Keith

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>