Re: pg_upgrade

>>>>>  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? 
Yes 
 
> 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.
I ran the query more than 10 times. All queries cost more then 32sec.

> Did you compare EXPLAIN outputs on both systems (only makes sense after 
> running ANALYZE)?
EXPLAIN shows that the 9.4 queryplaner has a different plan:

OLD (9.0):
==================================================================
QUERY PLAN
Hash Left Join  (cost=5278.77..14784.76 rows=39672 width=1305)
  Hash Cond: (k.lieferbedingungid = lb.lieferbedingungid)
  ->  Hash Left Join  (cost=5277.41..14127.16 rows=39672 width=1286)
        Hash Cond: (k.zahlungsbedingungid = zb.zahlungsbedingungid)
        ->  Hash Left Join  (cost=5276.14..13668.47 rows=39672 width=1262)
              Hash Cond: (k.zahlungsartid = za.zahlungsartid)
              ->  Hash Left Join  (cost=5275.01..13518.55 rows=39672 width=1252)
                    Hash Cond: (k.bankverbindungid = b.bankverbindungid)
                    ->  Hash Left Join  (cost=4384.74..12159.15 rows=39672 width=1119)
                          Hash Cond: (a.nation_iso = n.iso_3166alpha2)
                          ->  Hash Left Join  (cost=4376.21..11605.12 rows=39672 width=1105)
                                Hash Cond: ((k.kundengruppeid)::text = (kg.kundengruppeid)::text)
                                ->  Hash Left Join  (cost=4374.94..11058.37 rows=39672 width=1103)
                                      Hash Cond: (k.adresseid = a.adresseid)
                                      ->  Hash Left Join  (cost=2819.09..8758.67 rows=39672 width=1014)
                                            Hash Cond: (a.nation_iso = n.iso_3166alpha2)
                                            ->  Hash Left Join  (cost=2810.55..8204.64 rows=39672 width=1003)
                                                  Hash Cond: (k.kontaktid = ko.kontaktid)
                                                  ->  Hash Left Join  (cost=1555.85..5970.69 rows=39672 width=609)
                                                        Hash Cond: (k.lieferadresseid = a.adresseid)
                                                        ->  Seq Scan on kunde k  (cost=0.00..4051.72 rows=39672 width=520)
                                                        ->  Hash  (cost=1021.49..1021.49 rows=42749 width=93)
                                                              ->  Seq Scan on adresse a  (cost=0.00..1021.49 rows=42749 width=93)
                                                  ->  Hash  (cost=763.20..763.20 rows=39320 width=394)
                                                        ->  Seq Scan on kontakt ko  (cost=0.00..763.20 rows=39320 width=394)
                                            ->  Hash  (cost=5.46..5.46 rows=246 width=14)
                                                  ->  Seq Scan on nation n  (cost=0.00..5.46 rows=246 width=14)
                                      ->  Hash  (cost=1021.49..1021.49 rows=42749 width=93)
                                            ->  Seq Scan on adresse a  (cost=0.00..1021.49 rows=42749 width=93)
                                ->  Hash  (cost=1.12..1.12 rows=12 width=4)
                                      ->  Seq Scan on kundengruppe kg  (cost=0.00..1.12 rows=12 width=4)
                          ->  Hash  (cost=5.46..5.46 rows=246 width=17)
                                ->  Seq Scan on nation n  (cost=0.00..5.46 rows=246 width=17)
                    ->  Hash  (cost=612.34..612.34 rows=22234 width=133)
                          ->  Seq Scan on bankverbindung b  (cost=0.00..612.34 rows=22234 width=133)
              ->  Hash  (cost=1.06..1.06 rows=6 width=14)
                    ->  Seq Scan on zahlungsart za  (cost=0.00..1.06 rows=6 width=14)
        ->  Hash  (cost=1.12..1.12 rows=12 width=28)
              ->  Seq Scan on zahlungsbedingung zb  (cost=0.00..1.12 rows=12 width=28)
  ->  Hash  (cost=1.16..1.16 rows=16 width=23)
        ->  Seq Scan on lieferbedingung lb  (cost=0.00..1.16 rows=16 width=23)


NEW (9.4):
==================================================================
QUERY PLAN
Hash Left Join  (cost=5203.29..12392.94 rows=39688 width=1292)
  Hash Cond: (k.lieferbedingungid = lb.lieferbedingungid)
  ->  Hash Left Join  (cost=5201.93..11734.23 rows=39688 width=1273)
        Hash Cond: (k.zahlungsbedingungid = zb.zahlungsbedingungid)
        ->  Hash Left Join  (cost=5200.66..11274.37 rows=39688 width=1249)
              Hash Cond: (k.zahlungsartid = za.zahlungsartid)
              ->  Hash Left Join  (cost=5199.53..11124.40 rows=39688 width=1239)
                    Hash Cond: (k.bankverbindungid = b.bankverbindungid)
                    ->  Hash Left Join  (cost=4326.26..9781.06 rows=39688 width=1106)
                          Hash Cond: (k.kontaktid = ko.kontaktid)
                          ->  Hash Left Join  (cost=3088.45..7563.41 rows=39688 width=712)
                                Hash Cond: (a_1.nation_iso = n_1.iso_3166alpha2)
                                ->  Hash Left Join  (cost=3079.91..7009.16 rows=39688 width=701)
                                      Hash Cond: (k.lieferadresseid = a_1.adresseid)
                                      ->  Hash Left Join  (cost=1544.86..5111.28 rows=39688 width=612)
                                            Hash Cond: (a.nation_iso = n.iso_3166alpha2)
                                            ->  Hash Left Join  (cost=1536.32..4557.03 rows=39688 width=598)
                                                  Hash Cond: (k.adresseid = a.adresseid)
                                                  ->  Hash Left Join  (cost=1.27..2277.83 rows=39688 width=509)
                                                        Hash Cond: ((k.kundengruppeid)::text = (kg.kundengruppeid)::text)
                                                        ->  Seq Scan on kunde k  (cost=0.00..1730.88 rows=39688 width=507)
                                                        ->  Hash  (cost=1.12..1.12 rows=12 width=4)
                                                              ->  Seq Scan on kundengruppe kg  (cost=0.00..1.12 rows=12 width=4)
                                                  ->  Hash  (cost=1000.58..1000.58 rows=42758 width=93)
                                                        ->  Seq Scan on adresse a  (cost=0.00..1000.58 rows=42758 width=93)
                                            ->  Hash  (cost=5.46..5.46 rows=246 width=17)
                                                  ->  Seq Scan on nation n  (cost=0.00..5.46 rows=246 width=17)
                                      ->  Hash  (cost=1000.58..1000.58 rows=42758 width=93)
                                            ->  Seq Scan on adresse a_1  (cost=0.00..1000.58 rows=42758 width=93)
                                ->  Hash  (cost=5.46..5.46 rows=246 width=14)
                                      ->  Seq Scan on nation n_1  (cost=0.00..5.46 rows=246 width=14)
                          ->  Hash  (cost=746.25..746.25 rows=39325 width=394)
                                ->  Seq Scan on kontakt ko  (cost=0.00..746.25 rows=39325 width=394)
                    ->  Hash  (cost=595.34..595.34 rows=22234 width=133)
                          ->  Seq Scan on bankverbindung b  (cost=0.00..595.34 rows=22234 width=133)
              ->  Hash  (cost=1.06..1.06 rows=6 width=14)
                    ->  Seq Scan on zahlungsart za  (cost=0.00..1.06 rows=6 width=14)
        ->  Hash  (cost=1.12..1.12 rows=12 width=28)
              ->  Seq Scan on zahlungsbedingung zb  (cost=0.00..1.12 rows=12 width=28)
  ->  Hash  (cost=1.16..1.16 rows=16 width=23)
        ->  Seq Scan on lieferbedingung lb  (cost=0.00..1.16 rows=16 width=23) 


I tried REINDEX, it had no effect on the queryplan.


> Do the systems differ in any other way, especially storage?
The old Server is over 5 years old, so the new hardware is totally 
differnt:  12 CPU, 64GB RAM, RAID6 on SAS, virtual Hyper-V machine

> Jan


Any more suggestions?


Rainer

You should run EXPLAIN (ANALYZE, BUFFERS) instead of just EXPLAIN.

Also, please show memory-related and query tuning config parameters.

Regards,

Igor Neyman