Re: Faster way of upgrading postgresql to 10 from 9.5

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>) 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(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>) 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?/
>>
>>
>>
>>
>>
>