Re: Faster way of upgrading postgresql to 10 from 9.5

It depends on the number of *databases* you have in the cluster, not the
size of them.

On 25/11/17 19:18, 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(dot)kirkwood(at)catalyst(dot)net(dot)nz <mailto: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>
> <mailto: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>>
>         <mailto: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> <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>>
>         <mailto: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>
>         <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>
>        
> <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?/
>
>
>
>
>
>