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" 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 > 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 > >>> 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" >>> >>> >> >> 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 >> <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" >>> >>> >> >>> >> 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 < >>> tel:3377813 <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 >>> >>> >> f-upgrading-postgres-to-10-from-9-5 >>> >> 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?/ >>> >>> >>> >>> >>> >> > >