Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_upgrade

pg_upgrade

From
Rainer Leo
Date:
Hello,

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?


Regards,
Rainer Leo

Re: pg_upgrade

From
Jan Lentfer
Date:
Am 2015-06-22 11:14, schrieb Rainer Leo:
> pg_upgrade Hello,
>
>  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

Re: pg_upgrade

From
Ziggy Skalski
Date:
On 2015-06-22 12:36 PM, Jan Lentfer wrote:
> Am 2015-06-22 11:14, schrieb Rainer Leo:
>> pg_upgrade Hello,
>>
>>  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

Re: pg_upgrade

From
Rainer Leo
Date:
>>>  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?

Rainer

Re: pg_upgrade

From
Jan Lentfer
Date:
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

Re: pg_upgrade

From
Rainer Leo
Date:
>>>>>  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

Re: pg_upgrade

From
Igor Neyman
Date:

 

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rainer Leo
Sent: Tuesday, June 23, 2015 3:51 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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

 

Re: pg_upgrade

From
Catalin Iacob
Date:
On Mon, Jun 22, 2015 at 11:36 AM, Jan Lentfer <Jan.Lentfer@web.de> wrote:
> 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).

I was thinking (and could not find definite answer) if it is supported
to reuse the binary files (essentially what pg_upgrade does) across
diferent Windows versions. According to, for example,
http://superuser.com/a/643087/22951 this isn't supported across
different operating systems.

Streaming replication also suggests the same OS on both sides.

So is pg_upgrade actually supported in Rainer's case where he is
switching the Windows version?

Re: pg_upgrade

From
Igor Neyman
Date:

 

 

From: Rainer Leo [mailto:leo@workfile.de]
Sent: Monday, July 06, 2015 6:40 AM
To: Igor Neyman
Subject: Re: [ADMIN] pg_upgrade

 

Hello Igor,

I still have trouble with the new Server.

I would really appreciate if you could take
a look at the attached .conf 

Maybe you can point me to a parameter which
would speed up the performance.


Thank you very much for your help!

Regards, 
Rainer



------------------------- Original Nachricht -------------------------

Von.......: Igor Neyman <ineyman@perceptron.com> 
An........: Rainer Leo 
Datum.....: Dienstag, 23. Juni 2015, 15:50 
Betreff...: Re: [ADMIN] pg_upgrade

 
 
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rainer Leo
Sent: Tuesday, June 23, 2015 3:51 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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

 

 

Leo,

 

If this machine is dedicated database server, considering you have 64GB RAM, I’d increase shared_buffers to 8GB and effective_cache_size to about 546GB (again, depending on what else is running on this machine).

Also, work_mem and temp_buffers may need some tuning, but you still didn’t provide the results of EXPLAIN (ANALYZE, BUFFERS) for your slow queries, so it’s hard to tell.

 

Also, try keeping this conversation on PG mailing list: there are people much more knowledgeable than I am who could help.

 

Regards,

Igor Neyman