Re: Faster way of upgrading postgresql to 10 from 9.5

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 <subharaj(dot)manna(at)gmail(dot)com> 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/>> <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/>>
> <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?/
>
>
>
>
>
>