Thread: Upgrade from PostgreSQL 9.6 to 11

On 10/6/19 7:36 π.μ., Pawan Sharma wrote:
> Hello All.
>
>
> What is the best way to upgrade from PostgreSQL 9.6 to PostgreSQL 11 instead of pg_upgrade.
why not pg_upgrade ? If the size is near the 3TB mark as you say, pg_upgrade is the fastest IMHO.
>
> - Less downtime.
> - Approx database size are 1-3TB.
>
> Thanks for your help..!!!
>
>
> Regards,
> Pawan


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On Mon, Jun 10, 2019 at 09:00:38AM +0300, Achilleas Mantzios wrote:
> On 10/6/19 7:36 π.μ., Pawan Sharma wrote:
>> What is the best way to upgrade from PostgreSQL 9.6 to PostgreSQL
>> 11 instead of pg_upgrade.
>
> why not pg_upgrade ? If the size is near the 3TB mark as you say,
> pg_upgrade is the fastest IMHO.

When it comes to upgrades, you could also look at logical
replication.  Unfortunately your origin version cannot do that.  Have
you looked at things like BDR or Slony?  They are logical-based,
meaning a lower downtime than pg_upgrade, but they take longer.

For 3TB pg_upgrade can also be very fast if you use --link.  Be wary
of having backups though, all the time.
--
Michael

Thanks all,

Is pg_upgrade is the best method if I am doing upgrade on same server or different server.

Same server means: source and Target on same server

Different: source and Target are different server.

A traditional upgrade by definition is on the same server, (if we are not talking about logical replication or any other equivalent technology).
So yes pg_upgrade is what would be best, along with -k (--link) for maximum speed.

On Mon, Jun 10, 2019, 12:07 PM Michael Paquier <michael@paquier.xyz> wrote:

On Mon, Jun 10, 2019 at 09:00:38AM +0300, Achilleas Mantzios wrote:
> On 10/6/19 7:36 π.μ., Pawan Sharma wrote:
>> What is the best way to upgrade from PostgreSQL 9.6 to PostgreSQL
>> 11 instead of pg_upgrade.
>
> why not pg_upgrade ? If the size is near the 3TB mark as you say,
> pg_upgrade is the fastest IMHO.

When it comes to upgrades, you could also look at logical
replication.  Unfortunately your origin version cannot do that.  Have
you looked at things like BDR or Slony?  They are logical-based,
meaning a lower downtime than pg_upgrade, but they take longer.

For 3TB pg_upgrade can also be very fast if you use --link.  Be wary
of having backups though, all the time.
--
Michael

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 6/9/19 11:36 PM, Pawan Sharma wrote:
> Hello All.
>
>
> What is the best way to upgrade from PostgreSQL 9.6 to PostgreSQL 11 
> instead of pg_upgrade.
>
> - Less downtime.
> - Approx database size are 1-3TB.

If you really don't want to do pg_upgrade, then a possibility is 
multi-threaded pg_dump using directory format, followed by a parallel rsync 
if required, and then multi-threaded pg_restore.

-- 
Angular momentum makes the world go 'round.