Re: Faster way of upgrading postgresql to 10 from 9.5

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(dot)desousa(at)icloud(dot)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(dot)manna(at)gmail(dot)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(dot)kirkwood(at)catalyst(dot)net(dot)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(dot)kirkwood(at)catalyst(dot)net(dot)nz <mailto:mark(dot)kirkwood(at)catalyst(dot)net(dot)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(at)opensysperu(dot)com <mailto:aaguayo(at)opensysperu(dot)com>
>>> <mailto:aaguayo(at)opensysperu(dot)com
>>> <mailto:aaguayo(at)opensysperu(dot)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(at)opensysperu(dot)com <mailto:aaguayo(at)opensysperu(dot)com>
>>> <mailto:aaguayo(at)opensysperu(dot)com
>>>
>>> <mailto:aaguayo(at)opensysperu(dot)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?/
>>>
>>>
>>>
>>>
>>>
>>
>
>