Re: Postgres DB Migration from 8.3 to 9.1

On 10/12/2012 08:05 AM, Amitabh Kant wrote:
> On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan
> <nvishalakshi(at)sirahu(dot)com <mailto:nvishalakshi(at)sirahu(dot)com>> wrote:
>
> Hi Friends,
>
> 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..?
>
> Thanks in Advance.
>
> --
> Best Regards,
> Vishalakshi.N
>
>
> Try using the -j <number-of-jobs> option to speed up restore process.
> See http://www.postgresql.org/docs/9.1/static/app-pgrestore.html . Not
> sure though whether it will bring it up within your range.
>
> Amitabh
Note that one issue with the -j option is that it requires the input be
a regular file rather than a pipe so you have to wait until you have a
complete dump stored on-disk somewhere before you can start the restore.
This delay may offset, eliminate or overshadow any benefit from the
parallel-restore speedup.

Pg_upgrade does support upgrades from 8.3:
http://www.postgresql.org/docs/current/static/pgupgrade.html but you
will need to set up a dev-system to become familiar with the process.

Depending on the nature of your data, you may be able to roll-your-own.
Some of our systems have large tables of data that, once collected,
remain static. If you have that type of situation you may be able to
pre-migrate historical data and then have a reduced window to migrate
recent/live data.

Cheers,
Steve