Re: pg_upgrade vs. pg_dump

On Wed, Jan 21, 2015 at 12:52 AM, Perry, Hemy <hemy_perry(at)mentor(dot)com> wrote:

> Hello,
>
>
>
> I am planning a migration from 9.1.7 to 9.4.0.
>
> Based on my understanding, the recommendation is to use pg_upgrade over
> ‘dump and restore’.
>
> Is that right?
>
>
>
> If I want to use pg_upgrade, I need to provide the -b *bindir (*
> --old-bindir=*bindir) *and so my second question is what if I’ll provide
> 9.4 (the * new* PostgreSQL executable directory) also as the
> ‘old-bindir’, can it work that way? Always?
>
> [I am trying to solve a problem that I might not have the old-bindir
> available on the machine and only the new-bindir will be available (as well
> as the old & new datadir of course J]
>
>
>
> Thanks
>
> Hemy
>
>
>
>
>

Hi,

First Answer:
you can use dump & restore if your database size is small, but if your
database size is huge then you should go for pg_upgrade.
pg_upgrade is fast & time consuming than dump & restore for huge databases.

Second Answer:
pg_upgrade requires old bin & old data directory because pg_upgrade
upgrades headers of old data directory by using old & new binaries.
It converts the old data headers to new one.

If you put new binaries as old bin directory it will not work because the
pg_ctl of 9.4 will look for *postgresql.auto.conf* file in your old data
directory which is required for Postgresql 9.4 to start the old cluster.

I hope you got the pg_upgrade concept.

--
Thanks & Regards,
*Harshad Adalkonda*
*Database Administrator*
www.shreeyansh.com