agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedpg_upgrade
13+ messages / 8 participants
[nested] [flat]
* pg_upgrade
@ 2001-02-07 05:30 Thyagarajan Vasudevan <thyaga@vitalect.com>
0 siblings, 0 replies; 13+ messages in thread
From: Thyagarajan Vasudevan @ 2001-02-07 05:30 UTC (permalink / raw)
To: pgsql-admin; pgsql-general@postgresql.org
Hi All
I am trying to upgrade from Postgres 6.5 to Postgres 7.0.3. Now, in
the previous version of the Database, i have a lot of triggers which
simulate the Foreign key functionality, by using the checkPrimaryKey and
the checkForeignKey functions defined in refint.c.
My question is:
If I use pg_upgrade to shift from 6.5 to 7.0.3, will these
triggers automatically be removed and be converted into a FOREIGN KEY
constraint in the appropriate tables.
Thanks
Thyaga
^ permalink raw reply [nested|flat] 13+ messages in thread
* pg_upgrade
@ 2015-06-22 09:14 Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
0 siblings, 1 reply; 13+ messages in thread
From: Rainer Leo @ 2015-06-22 09:14 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
@ 2015-06-22 10:36 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-06-22 10:44 ` Re: pg_upgrade Ziggy Skalski <zskalski@afilias.info>
2015-06-23 16:15 ` Re: pg_upgrade Catalin Iacob <iacobcatalin@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Jan Lentfer @ 2015-06-22 10:36 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
@ 2015-06-22 10:44 ` Ziggy Skalski <zskalski@afilias.info>
2015-06-22 14:21 ` Re: pg_upgrade Rainer Leo <leo@workfile.de>
1 sibling, 1 reply; 13+ messages in thread
From: Ziggy Skalski @ 2015-06-22 10:44 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
2015-06-22 10:44 ` Re: pg_upgrade Ziggy Skalski <zskalski@afilias.info>
@ 2015-06-22 14:21 ` Rainer Leo <leo@workfile.de>
2015-06-22 14:31 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
0 siblings, 1 reply; 13+ messages in thread
From: Rainer Leo @ 2015-06-22 14:21 UTC (permalink / raw)
To: pgsql-admin
>>> 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
2015-06-22 10:44 ` Re: pg_upgrade Ziggy Skalski <zskalski@afilias.info>
2015-06-22 14:21 ` Re: pg_upgrade Rainer Leo <leo@workfile.de>
@ 2015-06-22 14:31 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-06-23 07:50 ` Re: pg_upgrade Rainer Leo <leo@workfile.de>
0 siblings, 1 reply; 13+ messages in thread
From: Jan Lentfer @ 2015-06-22 14:31 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
2015-06-22 10:44 ` Re: pg_upgrade Ziggy Skalski <zskalski@afilias.info>
2015-06-22 14:21 ` Re: pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 14:31 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
@ 2015-06-23 07:50 ` Rainer Leo <leo@workfile.de>
2015-06-23 13:50 ` Re: pg_upgrade Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 13+ messages in thread
From: Rainer Leo @ 2015-06-23 07:50 UTC (permalink / raw)
To: pgsql-admin
>>>>> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
2015-06-22 10:44 ` Re: pg_upgrade Ziggy Skalski <zskalski@afilias.info>
2015-06-22 14:21 ` Re: pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 14:31 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
2015-06-23 07:50 ` Re: pg_upgrade Rainer Leo <leo@workfile.de>
@ 2015-06-23 13:50 ` Igor Neyman <ineyman@perceptron.com>
2015-07-06 13:22 ` Re: pg_upgrade Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 13+ messages in thread
From: Igor Neyman @ 2015-06-23 13:50 UTC (permalink / raw)
To: Rainer Leo <leo@workfile.de>; pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
2015-06-22 10:44 ` Re: pg_upgrade Ziggy Skalski <zskalski@afilias.info>
2015-06-22 14:21 ` Re: pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 14:31 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
2015-06-23 07:50 ` Re: pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-23 13:50 ` Re: pg_upgrade Igor Neyman <ineyman@perceptron.com>
@ 2015-07-06 13:22 ` Igor Neyman <ineyman@perceptron.com>
0 siblings, 0 replies; 13+ messages in thread
From: Igor Neyman @ 2015-07-06 13:22 UTC (permalink / raw)
To: Rainer Leo <leo@workfile.de>; +Cc: pgsql-admin
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<mailto: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> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rainer Leo
Sent: Tuesday, June 23, 2015 3:51 AM
To: pgsql-admin@postgresql.org<mailto: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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Re: pg_upgrade Jan Lentfer <Jan.Lentfer@web.de>
@ 2015-06-23 16:15 ` Catalin Iacob <iacobcatalin@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Catalin Iacob @ 2015-06-23 16:15 UTC (permalink / raw)
To: Jan Lentfer <Jan.Lentfer@web.de>; +Cc: pgsql-admin
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?
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* pg_upgrade
@ 2024-06-12 14:06 Wasim Devale <wasimd60@gmail.com>
2024-06-12 19:50 ` Re: pg_upgrade Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 13+ messages in thread
From: Wasim Devale @ 2024-06-12 14:06 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
Hi I am getting this error message while performing PG upgrade.
Database user "postgres" is not install user failure existing
Screenshot attached.
Thanks,
Wasim
Attachments:
[image/jpeg] IMG_20240612_193347462_HDR~2.jpg (1008.6K, ../../CAB5fag4COrZFzYYcbJN4penKuprYNog3nT-z5PRn29dcnvmE7A@mail.gmail.com/3-IMG_20240612_193347462_HDR~2.jpg)
download | view image
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2024-06-12 14:06 pg_upgrade Wasim Devale <wasimd60@gmail.com>
@ 2024-06-12 19:50 ` Holger Jakobs <holger@jakobs.com>
2024-06-13 04:50 ` Re: pg_upgrade Wasim Devale <wasimd60@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Holger Jakobs @ 2024-06-12 19:50 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 12.06.24 um 16:06 schrieb Wasim Devale:
> Hi I am getting this error message while performing PG upgrade.
>
> Database user "postgres" is not install user failure existing
>
> Screenshot attached.
>
> Thanks,
> Wasim
Hi Wasim,
It seems that the operating system user "postgres" did not set up the
cluster you are trying to upgrade.
Check which OS user owns the data directory.
Unfortunately, you don't show the complete shell prompt and pg_upgrade
command line in your question or your screen photograph (it's not even a
real screen shot).
It might help to chown the complete data directory to user and group
postgres, but it's difficult to tell definitely without further information.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_upgrade
2024-06-12 14:06 pg_upgrade Wasim Devale <wasimd60@gmail.com>
2024-06-12 19:50 ` Re: pg_upgrade Holger Jakobs <holger@jakobs.com>
@ 2024-06-13 04:50 ` Wasim Devale <wasimd60@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Wasim Devale @ 2024-06-13 04:50 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
It's a real photo graph It's has companies information like ip addresses
hence I cropped it. It's resolved thanks for your help.
On Thu, 13 Jun, 2024, 1:21 am Holger Jakobs, <holger@jakobs.com> wrote:
> Am 12.06.24 um 16:06 schrieb Wasim Devale:
> > Hi I am getting this error message while performing PG upgrade.
> >
> > Database user "postgres" is not install user failure existing
> >
> > Screenshot attached.
> >
> > Thanks,
> > Wasim
>
> Hi Wasim,
>
> It seems that the operating system user "postgres" did not set up the
> cluster you are trying to upgrade.
>
> Check which OS user owns the data directory.
>
> Unfortunately, you don't show the complete shell prompt and pg_upgrade
> command line in your question or your screen photograph (it's not even a
> real screen shot).
>
> It might help to chown the complete data directory to user and group
> postgres, but it's difficult to tell definitely without further
> information.
>
> Regards,
>
> Holger
>
>
> --
>
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-06-13 04:50 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-07 05:30 pg_upgrade Thyagarajan Vasudevan <thyaga@vitalect.com>
2015-06-22 09:14 pg_upgrade Rainer Leo <leo@workfile.de>
2015-06-22 10:36 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-06-22 10:44 ` Ziggy Skalski <zskalski@afilias.info>
2015-06-22 14:21 ` Rainer Leo <leo@workfile.de>
2015-06-22 14:31 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-06-23 07:50 ` Rainer Leo <leo@workfile.de>
2015-06-23 13:50 ` Igor Neyman <ineyman@perceptron.com>
2015-07-06 13:22 ` Igor Neyman <ineyman@perceptron.com>
2015-06-23 16:15 ` Catalin Iacob <iacobcatalin@gmail.com>
2024-06-12 14:06 pg_upgrade Wasim Devale <wasimd60@gmail.com>
2024-06-12 19:50 ` Holger Jakobs <holger@jakobs.com>
2024-06-13 04:50 ` Wasim Devale <wasimd60@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox