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.kirkwood@catalyst.net.nz <mailto: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
>         <mailto:mark.kirkwood@catalyst.net.nz>
>         <mailto: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>>
>                 <mailto: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> <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>>
>                 <mailto: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>
>                 <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?/
>
>
>
>
>
>