From: | Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> |
---|---|
To: | Debraj Manna <subharaj(dot)manna(at)gmail(dot)com> |
Cc: | Alvaro Aguayo Garcia-Rada <aaguayo(at)opensysperu(dot)com>, pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Faster way of upgrading postgresql to 10 from 9.5 |
Date: | 2017-11-25 06:02:29 |
Message-ID: | a1a12fbe-f5aa-542f-77f2-5220b24d917c@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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>>
> 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>>> 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(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>
>
> 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?/
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Debraj Manna | 2017-11-25 06:18:27 | Re: Faster way of upgrading postgresql to 10 from 9.5 |
Previous Message | Debraj Manna | 2017-11-25 05:42:30 | Re: Faster way of upgrading postgresql to 10 from 9.5 |