Re: Faster way of upgrading postgresql to 10 from 9.5

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