Subject: Re: Faster way of upgrading postgresql to 10 from 9.5
To: Debraj Manna <subharaj.manna@gmail.com>,
 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
References: 
 <CAF6DVKOZOqFPLFcnmxF6PU7j7NMkQN90jkb8af-gTRAyfZKZ_w@mail.gmail.com>
 <jdhahc12n02hggumet17qaok.1511582685139@email.android.com>
 <CAF6DVKNrE86fKEJhw4YCs5erekKCTJXnq5sWHc_C5wR5XJo6Rg@mail.gmail.com>
 <hbcp5fi9i6fpjd66b6r2a8p7.1511586597413@email.android.com>
 <CAF6DVKPf7++_RGYY5xDNyhXhix6RYeNOT2VT_C0AC+Cs=PbWDA@mail.gmail.com>
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Message-ID: <4d397bf0-14c3-2bf8-bee6-c023a654b7ef@catalyst.net.nz>
Date: Sat, 25 Nov 2017 18:24:28 +1300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAF6DVKPf7++_RGYY5xDNyhXhix6RYeNOT2VT_C0AC+Cs=PbWDA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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