Re: Postgres DB Migration from 8.3 to 9.1

On 2012-10-12, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Thu, Oct 11, 2012 at 07:38:07PM -0700, John R Pierce wrote:
>> On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote:
>> >We have our production environment database server in Postgres 8.3
>> >version. we have planned to upgrade to lastest version 9.1. Dump
>> >from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any
>> >other quick method to upgrade from 8.3 to 9.1. We need to reduce
>> >our downtime below 1 hour. Any Possibilities..?
>>
>>
>> is it the dump or the restore taking the lions share of that time?
>>
>> I don't know if pg_upgrade supports 8.3, but that would be one
>> approach. getting it setup to work correctly can require some
>> tinkering, but once you have that sorted, you start with a base
>> backup of the 8.3 file system, and pg_upgrade 'converts' it to the
>> newer version. you need both runtimes setup side by side so either
>> can be run as pg_upgrade will need to start the old version in order
>> to dump its metadata catalogs prior to migrating the data files. if
>> you put both data directories on the same file system, it can use
>> hard linking to 'move' the datafiles.
>
> Upgrading with pg_upgrade from 8.3 is going to require 9.2 to be
> compiled with --disable-integer-datetimes.

Doesn't that depend on what the 8.3 is using? Eg. Debian has used
integer datetimes since 8.1 (or earlier - 8.1 is the oldest I have
at hand)

If he is using float datetimes is that going to be discontinued
sometime?

--
⚂⚃ 100% natural