Re: pg_upgrade

Am 2015-06-22 16:21, schrieb Rainer Leo:
>>>> we are still using PostgreSQL 9.0.2 on Windows Server.
>
>>>> Now we are migrating to Windows Server 2012 R2 and we
>>>> would like to migrate PostgreSQL at the same time to
>>>> the current version 9.4.4-1
>
>>>> Which is the best way to migrate the data?
>
>>>> 1. pg_dump on the old server
>>>> 2. pg_retore on the new server
>>>> 3. pg_upgrade on the new server
>
>>>> Is this correct or is there a "best procedure" to do this?
>
>>> You do either 1 + 2 OR 3. pg_upgrade is binary upgrade, where as
>>> pg_dump + pg_restore is "logical" (dump data and schemal to SQL
>>> instructions). If you go that way also check pg_dumpall for dumping
>>> the globals.
>
>
>>> Regards
>
>>> Jan
>
>
>> Also, for 1+2 you would be advised to do the pg_dump/restore using
>> the
>> *new* binaries (9.4), things could get tricky otherwise...
>
>> Ziggy
>
> Thanks for your help.
>
> Using the 9.4 pg_dump on the old server did not work (missing
> libintl-8.dll), so I used the 9.0 pg_dump.
>
> pg_restore on the new server worked fine, BUT the perfomance is
> lousy, for example a query that took 1732ms on the old server now
> takes longer than 32000ms every time on 9.4
>
> I tuned the postgres.conf exactly like the old one, except for more
> RAM in some parameters.
>
> Does this mean I have to install 9.4 on the old server so I can use
> pg_upgrade?

That won't make a difference regarding resulting performance.
Did you run ANALYZE after pg_restore? Also, did you run the query more
than once? The new system is "cold" (caches are empty). It will take
some time (depends on your amount of data and RAM, etc) until everything
is properly loaded.
Did you compare EXPLAIN outputs on both systems (only makes sense after
running ANALYZE)?
Do the systems differ in any other way, especially storage?

Jan