Re: Faster way of upgrading postgresql to 10 from 9.5 - Mailing list 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 > >> 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 | 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 | 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 >> >> > of-upgrading-postgres-to-10-from-9-5 >> > 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?/ >> >> >> >> >> >