pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFaster way of upgrading postgresql to 10 from 9.5
17+ messages / 6 participants
[nested] [flat]
* Faster way of upgrading postgresql to 10 from 9.5
@ 2017-11-25 03:34 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
0 siblings, 1 reply; 17+ messages in thread
From: Debraj Manna @ 2017-11-25 03:34 UTC (permalink / raw)
To: pgsql-admin
Cross posting from stack-exchange
<https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5;
*I am trying to test the migration of postgres to 10 from 9.5*
*On trying in a test DB of size 490 GB in one postgres node it is taking
about 18 mins. The command I used*
*sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
* -b /usr/lib/postgresql/9.5/bin \*
* -B /usr/lib/postgresql/10/bin \*
* -d /var/lib/postgresql/data/postgresql0 \*
* -D /var/lib/postgresql/10/data/postgresql0 \*
* -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
* -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
*Is there any faster way of doing this?*
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 04:06 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Alvaro Aguayo Garcia-Rada @ 2017-11-25 04:06 UTC (permalink / raw)
To: pgsql-admin; Debraj Manna <subharaj.manna@gmail.com>
You can do an (almost) zero downtime migration between any postgres version starting with 9.2 using pglogical.
Basically, you first set up your new instance as a full replica of the old one. Then, you change whatever configuration your app has, changing the connection parameters so they point to the new instance, reboot your app(s) if needed, and that's all. As pglogical does not blocks changes on slaves, your app will run normally even with pglogical installed and active, letting you a time frame where you can have some connections still pointing to your old instance, with changes made on such connections being reflected on your new instance(warning: the opposite way will not happen.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Debraj Manna wrote ----
Cross posting from stack-exchange
<https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5;
*I am trying to test the migration of postgres to 10 from 9.5*
*On trying in a test DB of size 490 GB in one postgres node it is taking
about 18 mins. The command I used*
*sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
* -b /usr/lib/postgresql/9.5/bin \*
* -B /usr/lib/postgresql/10/bin \*
* -d /var/lib/postgresql/data/postgresql0 \*
* -D /var/lib/postgresql/10/data/postgresql0 \*
* -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
* -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
*Is there any faster way of doing this?*
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
@ 2017-11-25 05:00 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
0 siblings, 1 reply; 17+ messages in thread
From: Debraj Manna @ 2017-11-25 05:00 UTC (permalink / raw)
To: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; +Cc: pgsql-admin
I am using one node postgres.
So if I am setting up pglogical then I guess disk space will double up?
Sent from GMail on Android
On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada" <
aaguayo@opensysperu.com> wrote:
> You can do an (almost) zero downtime migration between any postgres
> version starting with 9.2 using pglogical.
>
> Basically, you first set up your new instance as a full replica of the old
> one. Then, you change whatever configuration your app has, changing the
> connection parameters so they point to the new instance, reboot your app(s)
> if needed, and that's all. As pglogical does not blocks changes on slaves,
> your app will run normally even with pglogical installed and active,
> letting you a time frame where you can have some connections still
> pointing to your old instance, with changes made on such connections being
> reflected on your new instance(warning: the opposite way will not happen.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51)
> 954183248
> Website: www.ocs.pe
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> Cross posting from stack-exchange
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5;
>
> *I am trying to test the migration of postgres to 10 from 9.5*
>
> *On trying in a test DB of size 490 GB in one postgres node it is taking
> about 18 mins. The command I used*
>
> *sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
> * -b /usr/lib/postgresql/9.5/bin \*
> * -B /usr/lib/postgresql/10/bin \*
> * -d /var/lib/postgresql/data/postgresql0 \*
> * -D /var/lib/postgresql/10/data/postgresql0 \*
> * -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
> * -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
>
> *Is there any faster way of doing this?*
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 05:11 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Alvaro Aguayo Garcia-Rada @ 2017-11-25 05:11 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: pgsql-admin
Yes, but only during the migration. After fi ishing and checking your data, you can stop & destroy the old instance
Of course, this is only needed if you want a zero or little downtime migration, specially on production environment.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Debraj Manna wrote ----
I am using one node postgres.
So if I am setting up pglogical then I guess disk space will double up?
Sent from GMail on Android
On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada" <
aaguayo@opensysperu.com> wrote:
> You can do an (almost) zero downtime migration between any postgres
> version starting with 9.2 using pglogical.
>
> Basically, you first set up your new instance as a full replica of the old
> one. Then, you change whatever configuration your app has, changing the
> connection parameters so they point to the new instance, reboot your app(s)
> if needed, and that's all. As pglogical does not blocks changes on slaves,
> your app will run normally even with pglogical installed and active,
> letting you a time frame where you can have some connections still
> pointing to your old instance, with changes made on such connections being
> reflected on your new instance(warning: the opposite way will not happen.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51)
> 954183248
> Website: www.ocs.pe
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> Cross posting from stack-exchange
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5;
>
> *I am trying to test the migration of postgres to 10 from 9.5*
>
> *On trying in a test DB of size 490 GB in one postgres node it is taking
> about 18 mins. The command I used*
>
> *sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
> * -b /usr/lib/postgresql/9.5/bin \*
> * -B /usr/lib/postgresql/10/bin \*
> * -d /var/lib/postgresql/data/postgresql0 \*
> * -D /var/lib/postgresql/10/data/postgresql0 \*
> * -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
> * -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
>
> *Is there any faster way of doing this?*
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
@ 2017-11-25 05:17 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 17+ messages in thread
From: Debraj Manna @ 2017-11-25 05:17 UTC (permalink / raw)
To: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; +Cc: pgsql-admin
Ok.
Is there any other approach by which I can do the migration without
doubling the disk space?
I can bear small downtime.
Sent from GMail on Android
On Nov 25, 2017 10:40 AM, "Alvaro Aguayo Garcia-Rada" <
aaguayo@opensysperu.com> wrote:
Yes, but only during the migration. After fi ishing and checking your data,
you can stop & destroy the old instance
Of course, this is only needed if you want a zero or little downtime
migration, specially on production environment.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51)
954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Debraj Manna wrote ----
I am using one node postgres.
So if I am setting up pglogical then I guess disk space will double up?
Sent from GMail on Android
On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada" <
aaguayo@opensysperu.com> wrote:
> You can do an (almost) zero downtime migration between any postgres
> version starting with 9.2 using pglogical.
>
> Basically, you first set up your new instance as a full replica of the old
> one. Then, you change whatever configuration your app has, changing the
> connection parameters so they point to the new instance, reboot your app(s)
> if needed, and that's all. As pglogical does not blocks changes on slaves,
> your app will run normally even with pglogical installed and active,
> letting you a time frame where you can have some connections still
> pointing to your old instance, with changes made on such connections being
> reflected on your new instance(warning: the opposite way will not happen.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51)
> 954183248
> Website: www.ocs.pe
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> Cross posting from stack-exchange
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5;
>
> *I am trying to test the migration of postgres to 10 from 9.5*
>
> *On trying in a test DB of size 490 GB in one postgres node it is taking
> about 18 mins. The command I used*
>
> *sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
> * -b /usr/lib/postgresql/9.5/bin \*
> * -B /usr/lib/postgresql/10/bin \*
> * -d /var/lib/postgresql/data/postgresql0 \*
> * -D /var/lib/postgresql/10/data/postgresql0 \*
> * -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
> * -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
>
> *Is there any faster way of doing this?*
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 05:24 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Mark Kirkwood @ 2017-11-25 05:24 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; +Cc: pgsql-admin
I'd recommend looking at pg_upgrade again, but using the --link (-k)
option to avoid copying the data. Should be quite a bit faster.
regards
Mark
On 25/11/17 18:17, Debraj Manna wrote:
> Ok.
>
> Is there any other approach by which I can do the migration without
> doubling the disk space?
>
> I can bear small downtime.
>
> Sent from GMail on Android
>
> On Nov 25, 2017 10:40 AM, "Alvaro Aguayo Garcia-Rada"
> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>> wrote:
>
> Yes, but only during the migration. After fi ishing and checking
> your data, you can stop & destroy the old instance
>
> Of course, this is only needed if you want a zero or little
> downtime migration, specially on production environment.
>
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM: #034252
> / (+51) 995540103 <tel:995540103> | RPC: (+51) 954183248
> <tel:954183248>
> Website: www.ocs.pe <http://www.ocs.pe;
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> I am using one node postgres.
>
> So if I am setting up pglogical then I guess disk space will
> double up?
>
> Sent from GMail on Android
>
> On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada"
> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>> wrote:
>
> You can do an (almost) zero downtime migration between any
> postgres version starting with 9.2 using pglogical.
>
> Basically, you first set up your new instance as a full
> replica of the old one. Then, you change whatever
> configuration your app has, changing the connection parameters
> so they point to the new instance, reboot your app(s) if
> needed, and that's all. As pglogical does not blocks changes
> on slaves, your app will run normally even with pglogical
> installed and active, letting you a time frame where you can
> have some connections still pointing to your old instance,
> with changes made on such connections being reflected on your
> new instance(warning: the opposite way will not happen.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM:
> #034252 / (+51) 995540103 <tel:995540103> | RPC: (+51)
> 954183248 <tel:954183248>
> Website: www.ocs.pe <http://www.ocs.pe;
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> Cross posting from stack-exchange
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5;
>
>
> /I am trying to test the migration of postgres to 10 from 9.5/
> /
> /
> /On trying in a test DB of size 490 GB in one postgres node it
> is taking about 18 mins. The command I used/
> /
> /
> /sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \/
> / -b /usr/lib/postgresql/9.5/bin \/
> / -B /usr/lib/postgresql/10/bin \/
> / -d /var/lib/postgresql/data/postgresql0 \/
> / -D /var/lib/postgresql/10/data/postgresql0 \/
> / -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \/
> / -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf'
> -k /
> /
> /
> /Is there any faster way of doing this?/
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
@ 2017-11-25 05:42 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 17+ messages in thread
From: Debraj Manna @ 2017-11-25 05:42 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; +Cc: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Yes Mark I tried it like below (pg_upgrade with -k) - For 490 GB it took
about 17 mins in a single node postgres . Is it possible to reduce this?
*sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
* -b /usr/lib/postgresql/9.5/bin \*
* -B /usr/lib/postgresql/10/bin \*
* -d /var/lib/postgresql/data/postgresql0 \*
* -D /var/lib/postgresql/10/data/postgresql0 \*
* -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
* -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
On Sat, Nov 25, 2017 at 10:54 AM, Mark Kirkwood <
mark.kirkwood@catalyst.net.nz> wrote:
> I'd recommend looking at pg_upgrade again, but using the --link (-k)
> option to avoid copying the data. Should be quite a bit faster.
>
> regards
>
> Mark
>
>
> On 25/11/17 18:17, Debraj Manna wrote:
>
>> Ok.
>>
>> Is there any other approach by which I can do the migration without
>> doubling the disk space?
>>
>> I can bear small downtime.
>>
>> Sent from GMail on Android
>>
>> On Nov 25, 2017 10:40 AM, "Alvaro Aguayo Garcia-Rada" <
>> aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>> wrote:
>>
>> Yes, but only during the migration. After fi ishing and checking
>> your data, you can stop & destroy the old instance
>>
>> Of course, this is only needed if you want a zero or little
>> downtime migration, specially on production environment.
>>
>>
>> Regards,
>>
>> Alvaro Aguayo
>> Jefe de Operaciones
>> Open Comb Systems E.I.R.L.
>>
>> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM: #034252
>> / (+51) 995540103 <tel:995540103> | RPC: (+51) 954183248
>> <tel:954183248>
>> Website: www.ocs.pe <http://www.ocs.pe;
>>
>> Sent from my Sony Xperia™ smartphone
>>
>> ---- Debraj Manna wrote ----
>>
>>
>> I am using one node postgres.
>>
>> So if I am setting up pglogical then I guess disk space will
>> double up?
>>
>> Sent from GMail on Android
>>
>> On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada"
>> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>> wrote:
>>
>> You can do an (almost) zero downtime migration between any
>> postgres version starting with 9.2 using pglogical.
>>
>> Basically, you first set up your new instance as a full
>> replica of the old one. Then, you change whatever
>> configuration your app has, changing the connection parameters
>> so they point to the new instance, reboot your app(s) if
>> needed, and that's all. As pglogical does not blocks changes
>> on slaves, your app will run normally even with pglogical
>> installed and active, letting you a time frame where you can
>> have some connections still pointing to your old instance,
>> with changes made on such connections being reflected on your
>> new instance(warning: the opposite way will not happen.
>>
>> Regards,
>>
>> Alvaro Aguayo
>> Jefe de Operaciones
>> Open Comb Systems E.I.R.L.
>>
>> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM:
>> #034252 / (+51) 995540103 <tel:995540103> | RPC: (+51)
>> 954183248 <tel:954183248>
>> Website: www.ocs.pe <http://www.ocs.pe;
>>
>> Sent from my Sony Xperia™ smartphone
>>
>> ---- Debraj Manna wrote ----
>>
>>
>> Cross posting from stack-exchange
>> <https://dba.stackexchange.com/questions/191693/faster-way-o
>> f-upgrading-postgres-to-10-from-9-5>
>>
>>
>> /I am trying to test the migration of postgres to 10 from 9.5/
>> /
>> /
>> /On trying in a test DB of size 490 GB in one postgres node it
>> is taking about 18 mins. The command I used/
>> /
>> /
>> /sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \/
>> / -b /usr/lib/postgresql/9.5/bin \/
>> / -B /usr/lib/postgresql/10/bin \/
>> / -d /var/lib/postgresql/data/postgresql0 \/
>> / -D /var/lib/postgresql/10/data/postgresql0 \/
>> / -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf'
>> \/
>> / -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf'
>> -k /
>> /
>> /
>> /Is there any faster way of doing this?/
>>
>>
>>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 06:02 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Mark Kirkwood @ 2017-11-25 06:02 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Ah - sorry. missed that -k at the end! That is about as fast as you can
get without resorting to replication to do the upgrade on another host.
regards
Mark
On 25/11/17 18:42, Debraj Manna wrote:
> Yes Mark I tried it like below (pg_upgrade with -k) - For 490 GB it
> took about 17 mins in a single node postgres . Is it possible to
> reduce this?
>
> *sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
> * -b /usr/lib/postgresql/9.5/bin \*
> * -B /usr/lib/postgresql/10/bin \*
> * -d /var/lib/postgresql/data/postgresql0 \*
> * -D /var/lib/postgresql/10/data/postgresql0 \*
> * -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
> * -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
>
> On Sat, Nov 25, 2017 at 10:54 AM, Mark Kirkwood
> <mark.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.nz>>
> wrote:
>
> I'd recommend looking at pg_upgrade again, but using the --link
> (-k) option to avoid copying the data. Should be quite a bit faster.
>
> regards
>
> Mark
>
>
> On 25/11/17 18:17, Debraj Manna wrote:
>
> Ok.
>
> Is there any other approach by which I can do the migration
> without doubling the disk space?
>
> I can bear small downtime.
>
> Sent from GMail on Android
>
> On Nov 25, 2017 10:40 AM, "Alvaro Aguayo Garcia-Rada"
> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>
> <mailto:aaguayo@opensysperu.com
> <mailto:aaguayo@opensysperu.com>>> wrote:
>
> Yes, but only during the migration. After fi ishing and
> checking
> your data, you can stop & destroy the old instance
>
> Of course, this is only needed if you want a zero or little
> downtime migration, specially on production environment.
>
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM:
> #034252
> / (+51) 995540103 <tel:995540103> | RPC: (+51) 954183248
> <tel:954183248>
> Website: www.ocs.pe <http://www.ocs.pe; <http://www.ocs.pe;
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> I am using one node postgres.
>
> So if I am setting up pglogical then I guess disk space will
> double up?
>
> Sent from GMail on Android
>
> On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada"
> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>
> <mailto:aaguayo@opensysperu.com
> <mailto:aaguayo@opensysperu.com>>> wrote:
>
> You can do an (almost) zero downtime migration between any
> postgres version starting with 9.2 using pglogical.
>
> Basically, you first set up your new instance as a full
> replica of the old one. Then, you change whatever
> configuration your app has, changing the connection
> parameters
> so they point to the new instance, reboot your app(s) if
> needed, and that's all. As pglogical does not blocks
> changes
> on slaves, your app will run normally even with pglogical
> installed and active, letting you a time frame where
> you can
> have some connections still pointing to your old instance,
> with changes made on such connections being reflected
> on your
> new instance(warning: the opposite way will not happen.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM:
> #034252 / (+51) 995540103 <tel:995540103> | RPC: (+51)
> 954183248 <tel:954183248>
> Website: www.ocs.pe <http://www.ocs.pe;
> <http://www.ocs.pe;
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> Cross posting from stack-exchange
>
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5>;
>
>
> /I am trying to test the migration of postgres to 10
> from 9.5/
> /
> /
> /On trying in a test DB of size 490 GB in one postgres
> node it
> is taking about 18 mins. The command I used/
> /
> /
> /sudo -H -u postgres
> /usr/lib/postgresql/10/bin/pg_upgrade \/
> / -b /usr/lib/postgresql/9.5/bin \/
> / -B /usr/lib/postgresql/10/bin \/
> / -d /var/lib/postgresql/data/postgresql0 \/
> / -D /var/lib/postgresql/10/data/postgresql0 \/
> / -o ' -c
> config_file=/etc/postgresql/9.5/main/postgresql.conf' \/
> / -O ' -c
> config_file=/etc/postgresql/10/main/postgresql.conf'
> -k /
> /
> /
> /Is there any faster way of doing this?/
>
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
@ 2017-11-25 06:18 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:54 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Rui DeSousa <rui.desousa@icloud.com>
2017-11-25 07:27 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 14:45 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Herrera <alvherre@alvh.no-ip.org>
2017-11-25 15:19 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 17+ messages in thread
From: Debraj Manna @ 2017-11-25 06:18 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; +Cc: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Mark
Is this time expected? I mean 17 minutes for 490 GB. I could not find any
benchmark for pg_upgrade.
On Sat, Nov 25, 2017 at 11:32 AM, Mark Kirkwood <
mark.kirkwood@catalyst.net.nz> wrote:
> Ah - sorry. missed that -k at the end! That is about as fast as you can
> get without resorting to replication to do the upgrade on another host.
>
> regards
>
> Mark
>
>
> On 25/11/17 18:42, Debraj Manna wrote:
>
>> Yes Mark I tried it like below (pg_upgrade with -k) - For 490 GB it took
>> about 17 mins in a single node postgres . Is it possible to reduce this?
>>
>> *sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
>> * -b /usr/lib/postgresql/9.5/bin \*
>> * -B /usr/lib/postgresql/10/bin \*
>> * -d /var/lib/postgresql/data/postgresql0 \*
>> * -D /var/lib/postgresql/10/data/postgresql0 \*
>> * -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
>> * -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
>>
>> On Sat, Nov 25, 2017 at 10:54 AM, Mark Kirkwood <
>> mark.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.nz>>
>> wrote:
>>
>> I'd recommend looking at pg_upgrade again, but using the --link
>> (-k) option to avoid copying the data. Should be quite a bit faster.
>>
>> regards
>>
>> Mark
>>
>>
>> On 25/11/17 18:17, Debraj Manna wrote:
>>
>> Ok.
>>
>> Is there any other approach by which I can do the migration
>> without doubling the disk space?
>>
>> I can bear small downtime.
>>
>> Sent from GMail on Android
>>
>> On Nov 25, 2017 10:40 AM, "Alvaro Aguayo Garcia-Rada"
>> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>
>> <mailto:aaguayo@opensysperu.com
>> <mailto:aaguayo@opensysperu.com>>> wrote:
>>
>> Yes, but only during the migration. After fi ishing and
>> checking
>> your data, you can stop & destroy the old instance
>>
>> Of course, this is only needed if you want a zero or little
>> downtime migration, specially on production environment.
>>
>>
>> Regards,
>>
>> Alvaro Aguayo
>> Jefe de Operaciones
>> Open Comb Systems E.I.R.L.
>>
>> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM:
>> #034252
>> / (+51) 995540103 <tel:995540103> | RPC: (+51) 954183248
>> <tel:954183248>
>> Website: www.ocs.pe <http://www.ocs.pe; <http://www.ocs.pe;
>>
>> Sent from my Sony Xperia™ smartphone
>>
>> ---- Debraj Manna wrote ----
>>
>>
>> I am using one node postgres.
>>
>> So if I am setting up pglogical then I guess disk space will
>> double up?
>>
>> Sent from GMail on Android
>>
>> On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada"
>> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>
>> <mailto:aaguayo@opensysperu.com
>>
>> <mailto:aaguayo@opensysperu.com>>> wrote:
>>
>> You can do an (almost) zero downtime migration between any
>> postgres version starting with 9.2 using pglogical.
>>
>> Basically, you first set up your new instance as a full
>> replica of the old one. Then, you change whatever
>> configuration your app has, changing the connection
>> parameters
>> so they point to the new instance, reboot your app(s) if
>> needed, and that's all. As pglogical does not blocks
>> changes
>> on slaves, your app will run normally even with pglogical
>> installed and active, letting you a time frame where
>> you can
>> have some connections still pointing to your old instance,
>> with changes made on such connections being reflected
>> on your
>> new instance(warning: the opposite way will not happen.
>>
>> Regards,
>>
>> Alvaro Aguayo
>> Jefe de Operaciones
>> Open Comb Systems E.I.R.L.
>>
>> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM:
>> #034252 / (+51) 995540103 <tel:995540103> | RPC: (+51)
>> 954183248 <tel:954183248>
>> Website: www.ocs.pe <http://www.ocs.pe;
>> <http://www.ocs.pe;
>>
>> Sent from my Sony Xperia™ smartphone
>>
>> ---- Debraj Manna wrote ----
>>
>>
>> Cross posting from stack-exchange
>>
>> <https://dba.stackexchange.com/questions/191693/faster-way-
>> of-upgrading-postgres-to-10-from-9-5
>> <https://dba.stackexchange.com/questions/191693/faster-way-
>> of-upgrading-postgres-to-10-from-9-5>>
>>
>>
>> /I am trying to test the migration of postgres to 10
>> from 9.5/
>> /
>> /
>> /On trying in a test DB of size 490 GB in one postgres
>> node it
>> is taking about 18 mins. The command I used/
>> /
>> /
>> /sudo -H -u postgres
>> /usr/lib/postgresql/10/bin/pg_upgrade \/
>> / -b /usr/lib/postgresql/9.5/bin \/
>> / -B /usr/lib/postgresql/10/bin \/
>> / -d /var/lib/postgresql/data/postgresql0 \/
>> / -D /var/lib/postgresql/10/data/postgresql0 \/
>> / -o ' -c
>> config_file=/etc/postgresql/9.5/main/postgresql.conf' \/
>> / -O ' -c
>> config_file=/etc/postgresql/10/main/postgresql.conf'
>> -k /
>> /
>> /
>> /Is there any faster way of doing this?/
>>
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 06:54 ` Rui DeSousa <rui.desousa@icloud.com>
2017-11-25 11:08 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
3 siblings, 1 reply; 17+ messages in thread
From: Rui DeSousa @ 2017-11-25 06:54 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Are you using the link option (-k, --link)? it should be a lot faster than 17 minutes. The database size shouldn’t matter much as it is creating hard links instead of copying the files. Are you upgrading using the same filesystem (required for hard links)?
> On Nov 25, 2017, at 1:18 AM, Debraj Manna <subharaj.manna@gmail.com> wrote:
>
> Mark
>
> Is this time expected? I mean 17 minutes for 490 GB. I could not find any benchmark for pg_upgrade.
>
> On Sat, Nov 25, 2017 at 11:32 AM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.nz>> wrote:
> Ah - sorry. missed that -k at the end! That is about as fast as you can get without resorting to replication to do the upgrade on another host.
>
> regards
>
> Mark
>
>
> On 25/11/17 18:42, Debraj Manna wrote:
> Yes Mark I tried it like below (pg_upgrade with -k) - For 490 GB it took about 17 mins in a single node postgres . Is it possible to reduce this?
>
> *sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
> * -b /usr/lib/postgresql/9.5/bin \*
> * -B /usr/lib/postgresql/10/bin \*
> * -d /var/lib/postgresql/data/postgresql0 \*
> * -D /var/lib/postgresql/10/data/postgresql0 \*
> * -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
> * -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
>
> On Sat, Nov 25, 2017 at 10:54 AM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.nz> <mailto:mark.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.nz>>> wrote:
>
> I'd recommend looking at pg_upgrade again, but using the --link
> (-k) option to avoid copying the data. Should be quite a bit faster.
>
> regards
>
> Mark
>
>
> On 25/11/17 18:17, Debraj Manna wrote:
>
> Ok.
>
> Is there any other approach by which I can do the migration
> without doubling the disk space?
>
> I can bear small downtime.
>
> Sent from GMail on Android
>
> On Nov 25, 2017 10:40 AM, "Alvaro Aguayo Garcia-Rada"
> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com> <mailto:aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>>
> <mailto:aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>
> <mailto:aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>>>> wrote:
>
> Yes, but only during the migration. After fi ishing and
> checking
> your data, you can stop & destroy the old instance
>
> Of course, this is only needed if you want a zero or little
> downtime migration, specially on production environment.
>
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM:
> #034252
> / (+51) 995540103 <tel:995540103> | RPC: (+51) 954183248
> <tel:954183248>
> Website: www.ocs.pe <http://www.ocs.pe/; <http://www.ocs.pe <http://www.ocs.pe/>; <http://www.ocs.pe <http://www.ocs.pe/>;
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> I am using one node postgres.
>
> So if I am setting up pglogical then I guess disk space will
> double up?
>
> Sent from GMail on Android
>
> On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada"
> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com> <mailto:aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>>
> <mailto:aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>
>
> <mailto:aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>>>> wrote:
>
> You can do an (almost) zero downtime migration between any
> postgres version starting with 9.2 using pglogical.
>
> Basically, you first set up your new instance as a full
> replica of the old one. Then, you change whatever
> configuration your app has, changing the connection
> parameters
> so they point to the new instance, reboot your app(s) if
> needed, and that's all. As pglogical does not blocks
> changes
> on slaves, your app will run normally even with pglogical
> installed and active, letting you a time frame where
> you can
> have some connections still pointing to your old instance,
> with changes made on such connections being reflected
> on your
> new instance(warning: the opposite way will not happen.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1 <tel:+51-1>) 3377813 <tel:3377813> | RPM:
> #034252 / (+51) 995540103 <tel:995540103> | RPC: (+51)
> 954183248 <tel:954183248>
> Website: www.ocs.pe <http://www.ocs.pe/; <http://www.ocs.pe <http://www.ocs.pe/>;
> <http://www.ocs.pe <http://www.ocs.pe/>;
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> Cross posting from stack-exchange
>
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5 <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5;
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5 <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5>&g...;
>
>
> /I am trying to test the migration of postgres to 10
> from 9.5/
> /
> /
> /On trying in a test DB of size 490 GB in one postgres
> node it
> is taking about 18 mins. The command I used/
> /
> /
> /sudo -H -u postgres
> /usr/lib/postgresql/10/bin/pg_upgrade \/
> / -b /usr/lib/postgresql/9.5/bin \/
> / -B /usr/lib/postgresql/10/bin \/
> / -d /var/lib/postgresql/data/postgresql0 \/
> / -D /var/lib/postgresql/10/data/postgresql0 \/
> / -o ' -c
> config_file=/etc/postgresql/9.5/main/postgresql.conf' \/
> / -O ' -c
> config_file=/etc/postgresql/10/main/postgresql.conf'
> -k /
> /
> /
> /Is there any faster way of doing this?/
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:54 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Rui DeSousa <rui.desousa@icloud.com>
@ 2017-11-25 11:08 ` Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Debraj Manna @ 2017-11-25 11:08 UTC (permalink / raw)
To: Rui DeSousa <rui.desousa@icloud.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Yes same file system with link option.
Even I am wondering why it is taking so much time?
Will the time be effected by the number of databases I have in postgres?
Sent from GMail on Android
On Nov 25, 2017 12:24 PM, "Rui DeSousa" <rui.desousa@icloud.com> wrote:
> Are you using the link option (-k, --link)? it should be a lot faster than
> 17 minutes. The database size shouldn’t matter much as it is creating hard
> links instead of copying the files. Are you upgrading using the same
> filesystem (required for hard links)?
>
>
> On Nov 25, 2017, at 1:18 AM, Debraj Manna <subharaj.manna@gmail.com>
> wrote:
>
> Mark
>
> Is this time expected? I mean 17 minutes for 490 GB. I could not find any
> benchmark for pg_upgrade.
>
> On Sat, Nov 25, 2017 at 11:32 AM, Mark Kirkwood <
> mark.kirkwood@catalyst.net.nz> wrote:
>
>> Ah - sorry. missed that -k at the end! That is about as fast as you can
>> get without resorting to replication to do the upgrade on another host.
>>
>> regards
>>
>> Mark
>>
>>
>> On 25/11/17 18:42, Debraj Manna wrote:
>>
>>> Yes Mark I tried it like below (pg_upgrade with -k) - For 490 GB it took
>>> about 17 mins in a single node postgres . Is it possible to reduce this?
>>>
>>> *sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
>>> * -b /usr/lib/postgresql/9.5/bin \*
>>> * -B /usr/lib/postgresql/10/bin \*
>>> * -d /var/lib/postgresql/data/postgresql0 \*
>>> * -D /var/lib/postgresql/10/data/postgresql0 \*
>>> * -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
>>> * -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k *
>>>
>>> On Sat, Nov 25, 2017 at 10:54 AM, Mark Kirkwood <
>>> mark.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.nz>>
>>> wrote:
>>>
>>> I'd recommend looking at pg_upgrade again, but using the --link
>>> (-k) option to avoid copying the data. Should be quite a bit faster.
>>>
>>> regards
>>>
>>> Mark
>>>
>>>
>>> On 25/11/17 18:17, Debraj Manna wrote:
>>>
>>> Ok.
>>>
>>> Is there any other approach by which I can do the migration
>>> without doubling the disk space?
>>>
>>> I can bear small downtime.
>>>
>>> Sent from GMail on Android
>>>
>>> On Nov 25, 2017 10:40 AM, "Alvaro Aguayo Garcia-Rada"
>>> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>
>>> <mailto:aaguayo@opensysperu.com
>>> <mailto:aaguayo@opensysperu.com>>> wrote:
>>>
>>> Yes, but only during the migration. After fi ishing and
>>> checking
>>> your data, you can stop & destroy the old instance
>>>
>>> Of course, this is only needed if you want a zero or little
>>> downtime migration, specially on production environment.
>>>
>>>
>>> Regards,
>>>
>>> Alvaro Aguayo
>>> Jefe de Operaciones
>>> Open Comb Systems E.I.R.L.
>>>
>>> Oficina: (+51-1 <tel:+51-1 <+51-1>>) 3377813 <tel:3377813
>>> <3377813>> | RPM:
>>> #034252
>>> / (+51) 995540103 <tel:995540103 <995540103>> | RPC: (+51)
>>> 954183248
>>> <tel:954183248 <954183248>>
>>> Website: www.ocs.pe <http://www.ocs.pe; <http://www.ocs.pe;
>>>
>>> Sent from my Sony Xperia™ smartphone
>>>
>>> ---- Debraj Manna wrote ----
>>>
>>>
>>> I am using one node postgres.
>>>
>>> So if I am setting up pglogical then I guess disk space will
>>> double up?
>>>
>>> Sent from GMail on Android
>>>
>>> On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada"
>>> <aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>
>>> <mailto:aaguayo@opensysperu.com
>>>
>>> <mailto:aaguayo@opensysperu.com>>> wrote:
>>>
>>> You can do an (almost) zero downtime migration between
>>> any
>>> postgres version starting with 9.2 using pglogical.
>>>
>>> Basically, you first set up your new instance as a full
>>> replica of the old one. Then, you change whatever
>>> configuration your app has, changing the connection
>>> parameters
>>> so they point to the new instance, reboot your app(s) if
>>> needed, and that's all. As pglogical does not blocks
>>> changes
>>> on slaves, your app will run normally even with pglogical
>>> installed and active, letting you a time frame where
>>> you can
>>> have some connections still pointing to your old
>>> instance,
>>> with changes made on such connections being reflected
>>> on your
>>> new instance(warning: the opposite way will not happen.
>>>
>>> Regards,
>>>
>>> Alvaro Aguayo
>>> Jefe de Operaciones
>>> Open Comb Systems E.I.R.L.
>>>
>>> Oficina: (+51-1 <tel:+51-1 <+51-1>>) 3377813 <
>>> tel:3377813 <3377813>> | RPM:
>>> #034252 / (+51) 995540103 <tel:995540103 <995540103>> |
>>> RPC: (+51)
>>> 954183248 <tel:954183248 <954183248>>
>>> Website: www.ocs.pe <http://www.ocs.pe;
>>> <http://www.ocs.pe;
>>>
>>> Sent from my Sony Xperia™ smartphone
>>>
>>> ---- Debraj Manna wrote ----
>>>
>>>
>>> Cross posting from stack-exchange
>>>
>>> <https://dba.stackexchange.com/questions/191693/faster-way-o
>>> f-upgrading-postgres-to-10-from-9-5
>>> <https://dba.stackexchange.com/questions/191693/faster-way-o
>>> f-upgrading-postgres-to-10-from-9-5>>
>>>
>>>
>>> /I am trying to test the migration of postgres to 10
>>> from 9.5/
>>> /
>>> /
>>> /On trying in a test DB of size 490 GB in one postgres
>>> node it
>>> is taking about 18 mins. The command I used/
>>> /
>>> /
>>> /sudo -H -u postgres
>>> /usr/lib/postgresql/10/bin/pg_upgrade \/
>>> / -b /usr/lib/postgresql/9.5/bin \/
>>> / -B /usr/lib/postgresql/10/bin \/
>>> / -d /var/lib/postgresql/data/postgresql0 \/
>>> / -D /var/lib/postgresql/10/data/postgresql0 \/
>>> / -o ' -c
>>> config_file=/etc/postgresql/9.5/main/postgresql.conf' \/
>>> / -O ' -c
>>> config_file=/etc/postgresql/10/main/postgresql.conf'
>>> -k /
>>> /
>>> /
>>> /Is there any faster way of doing this?/
>>>
>>>
>>>
>>>
>>>
>>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 07:27 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
3 siblings, 0 replies; 17+ messages in thread
From: Mark Kirkwood @ 2017-11-25 07:27 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
It depends on the number of *databases* you have in the cluster, not the
size of them.
On 25/11/17 19:18, Debraj Manna wrote:
> Mark
>
> Is this time expected? I mean 17 minutes for 490 GB. I could not find
> any benchmark for pg_upgrade.
>
> On Sat, Nov 25, 2017 at 11:32 AM, Mark Kirkwood
> <mark.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.nz>>
> wrote:
>
> Ah - sorry. missed that -k at the end! That is about as fast as
> you can get without resorting to replication to do the upgrade on
> another host.
>
> regards
>
> Mark
>
>
> On 25/11/17 18:42, Debraj Manna wrote:
>
> Yes Mark I tried it like below (pg_upgrade with -k) - For 490
> GB it took about 17 mins in a single node postgres . Is it
> possible to reduce this?
>
> *sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \*
> * -b /usr/lib/postgresql/9.5/bin \*
> * -B /usr/lib/postgresql/10/bin \*
> * -d /var/lib/postgresql/data/postgresql0 \*
> * -D /var/lib/postgresql/10/data/postgresql0 \*
> * -o ' -c
> config_file=/etc/postgresql/9.5/main/postgresql.conf' \*
> * -O ' -c
> config_file=/etc/postgresql/10/main/postgresql.conf' -k *
>
> On Sat, Nov 25, 2017 at 10:54 AM, Mark Kirkwood
> <mark.kirkwood@catalyst.net.nz
> <mailto:mark.kirkwood@catalyst.net.nz>
> <mailto:mark.kirkwood@catalyst.net.nz
> <mailto:mark.kirkwood@catalyst.net.nz>>> wrote:
>
> I'd recommend looking at pg_upgrade again, but using the
> --link
> (-k) option to avoid copying the data. Should be quite a
> bit faster.
>
> regards
>
> Mark
>
>
> On 25/11/17 18:17, Debraj Manna wrote:
>
> Ok.
>
> Is there any other approach by which I can do the
> migration
> without doubling the disk space?
>
> I can bear small downtime.
>
> Sent from GMail on Android
>
> On Nov 25, 2017 10:40 AM, "Alvaro Aguayo Garcia-Rada"
> <aaguayo@opensysperu.com
> <mailto:aaguayo@opensysperu.com>
> <mailto:aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>>
> <mailto:aaguayo@opensysperu.com
> <mailto:aaguayo@opensysperu.com>
> <mailto:aaguayo@opensysperu.com
> <mailto:aaguayo@opensysperu.com>>>> wrote:
>
> Yes, but only during the migration. After fi
> ishing and
> checking
> your data, you can stop & destroy the old instance
>
> Of course, this is only needed if you want a zero
> or little
> downtime migration, specially on production
> environment.
>
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1 <tel:+51-1>) 3377813
> <tel:3377813> | RPM:
> #034252
> / (+51) 995540103 <tel:995540103> | RPC: (+51)
> 954183248
> <tel:954183248>
> Website: www.ocs.pe <http://www.ocs.pe;
> <http://www.ocs.pe; <http://www.ocs.pe;
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> I am using one node postgres.
>
> So if I am setting up pglogical then I guess disk
> space will
> double up?
>
> Sent from GMail on Android
>
> On Nov 25, 2017 9:34 AM, "Alvaro Aguayo Garcia-Rada"
> <aaguayo@opensysperu.com
> <mailto:aaguayo@opensysperu.com>
> <mailto:aaguayo@opensysperu.com <mailto:aaguayo@opensysperu.com>>
> <mailto:aaguayo@opensysperu.com
> <mailto:aaguayo@opensysperu.com>
>
> <mailto:aaguayo@opensysperu.com
> <mailto:aaguayo@opensysperu.com>>>> wrote:
>
> You can do an (almost) zero downtime migration
> between any
> postgres version starting with 9.2 using
> pglogical.
>
> Basically, you first set up your new instance
> as a full
> replica of the old one. Then, you change whatever
> configuration your app has, changing the
> connection
> parameters
> so they point to the new instance, reboot your
> app(s) if
> needed, and that's all. As pglogical does not
> blocks
> changes
> on slaves, your app will run normally even
> with pglogical
> installed and active, letting you a time frame
> where
> you can
> have some connections still pointing to your
> old instance,
> with changes made on such connections being
> reflected
> on your
> new instance(warning: the opposite way will
> not happen.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1 <tel:+51-1>) 3377813
> <tel:3377813> | RPM:
> #034252 / (+51) 995540103 <tel:995540103> |
> RPC: (+51)
> 954183248 <tel:954183248>
> Website: www.ocs.pe <http://www.ocs.pe;
> <http://www.ocs.pe;
> <http://www.ocs.pe;
>
> Sent from my Sony Xperia™ smartphone
>
> ---- Debraj Manna wrote ----
>
>
> Cross posting from stack-exchange
>
>
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5;
>
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5
> <https://dba.stackexchange.com/questions/191693/faster-way-of-upgrading-postgres-to-10-from-9-5>&g...;
>
>
> /I am trying to test the migration of postgres
> to 10
> from 9.5/
> /
> /
> /On trying in a test DB of size 490 GB in one
> postgres
> node it
> is taking about 18 mins. The command I used/
> /
> /
> /sudo -H -u postgres
> /usr/lib/postgresql/10/bin/pg_upgrade \/
> / -b /usr/lib/postgresql/9.5/bin \/
> / -B /usr/lib/postgresql/10/bin \/
> / -d /var/lib/postgresql/data/postgresql0 \/
> / -D /var/lib/postgresql/10/data/postgresql0 \/
> / -o ' -c
> config_file=/etc/postgresql/9.5/main/postgresql.conf' \/
> / -O ' -c
> config_file=/etc/postgresql/10/main/postgresql.conf'
> -k /
> /
> /
> /Is there any faster way of doing this?/
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 14:45 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2017-11-25 15:19 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
3 siblings, 1 reply; 17+ messages in thread
From: Alvaro Herrera @ 2017-11-25 14:45 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Debraj Manna wrote:
> Mark
>
> Is this time expected? I mean 17 minutes for 490 GB. I could not find any
> benchmark for pg_upgrade.
Maybe, or maybe not. Perhaps you can profile the process, to see if any
hot-spot becomes apparent, which can then be fixed.
Did you already test your applications with pg10?
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 14:45 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2017-11-25 15:19 ` Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Debraj Manna @ 2017-11-25 15:19 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Alvaro
Can you provide me any pointer on how can we profile the process?
Sent from GMail on Android
On Nov 25, 2017 8:15 PM, "Alvaro Herrera" <alvherre@alvh.no-ip.org> wrote:
> Debraj Manna wrote:
> > Mark
> >
> > Is this time expected? I mean 17 minutes for 490 GB. I could not find any
> > benchmark for pg_upgrade.
>
> Maybe, or maybe not. Perhaps you can profile the process, to see if any
> hot-spot becomes apparent, which can then be fixed.
>
> Did you already test your applications with pg10?
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 15:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-11-25 17:14 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
3 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2017-11-25 15:19 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Debraj Manna <subharaj.manna@gmail.com> writes:
> Is this time expected? I mean 17 minutes for 490 GB. I could not find any
> benchmark for pg_upgrade.
It seems awfully slow, if you're using -k. That switch should remove any
dependency on the data size, but what remains is the number of SQL objects
(databases, tables, functions, etc). If you've got tens or hundreds of
thousands of those, maybe this would be a reasonable time.
As Alvaro said, it's possible that you're hitting some fixable bottleneck;
but to identify what that is we'd need some profiling information.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 15:19 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-11-25 17:14 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 17:29 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Debraj Manna @ 2017-11-25 17:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
What profiling will help in this case?
Sent from GMail on Android
On Nov 25, 2017 8:49 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> Debraj Manna <subharaj.manna@gmail.com> writes:
> > Is this time expected? I mean 17 minutes for 490 GB. I could not find any
> > benchmark for pg_upgrade.
>
> It seems awfully slow, if you're using -k. That switch should remove any
> dependency on the data size, but what remains is the number of SQL objects
> (databases, tables, functions, etc). If you've got tens or hundreds of
> thousands of those, maybe this would be a reasonable time.
>
> As Alvaro said, it's possible that you're hitting some fixable bottleneck;
> but to identify what that is we'd need some profiling information.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Faster way of upgrading postgresql to 10 from 9.5
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` RE: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 15:19 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Tom Lane <tgl@sss.pgh.pa.us>
2017-11-25 17:14 ` Re: Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
@ 2017-11-25 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2017-11-25 17:29 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; pgsql-admin
Debraj Manna <subharaj.manna@gmail.com> writes:
> What profiling will help in this case?
If you're on a reasonably modern Linux platform, I'd try "perf",
which is capable of gathering a system-wide profile. Note you
will need debug symbols installed for Postgres to get good
results from any profiling tool.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2017-11-25 17:29 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-25 03:34 Faster way of upgrading postgresql to 10 from 9.5 Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 04:06 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:00 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:11 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2017-11-25 05:17 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 05:24 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 05:42 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:02 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 06:18 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 06:54 ` Rui DeSousa <rui.desousa@icloud.com>
2017-11-25 11:08 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 07:27 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-25 14:45 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2017-11-25 15:19 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 15:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-11-25 17:14 ` Debraj Manna <subharaj.manna@gmail.com>
2017-11-25 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox