Thread: Re: surprisingly slow creation of gist index used in excludeconstraint

Quoting Chris Withers <chris@withers.org>:

> Hi,
>
> I'm upgrading a database from 9.4 to 11.5 by dumping from the old
> cluster and loading into the new cluster.
>
> The database is tiny: around 2.3G, but importing this table is
> proving problematic:
> Column          |       Type        |                        Modifiers
> ...
> I feel like asking what I'm doing wrong here? The new server is
> pretty decent hardware...
>
> Concrete questions:
>
> - what, if anything, am I getting badly wrong here?
>
> - what can I do to speed up creation of this index?
>
> - failing that, what can I do to import and then create the index       in
> the background?
>
> As you can imagine, a 15hr outage for an upgrade has not met with
> large amounts of happiness from the people whose application it is
> ;-)
>
> Chris
Hi Chris,

This sounds like a candidate for pg_logical replicating from the old  
to new system.

Regards,
Ken

On 14/05/2020 21:16, ktm@rice.edu wrote:
> 
> Hi Chris,
> 
> This sounds like a candidate for pg_logical replicating from the old to 
> new system.

Can you point me to a good guide as to how to easily set this up for one 
database and would work between pg 9.4 and pg 11.5?

cheers,

Chris

> >
> >Hi Chris,
> >
> >This sounds like a candidate for pg_logical replicating from the
> >old to new system.
> 
> Can you point me to a good guide as to how to easily set this up for
> one database and would work between pg 9.4 and pg 11.5?
> 
> cheers,
> 
> Chris

Hi Chris,

Here is on for 9.4 to 10.3:

https://hunleyd.github.io/posts/Upgrading-PostgreSQL-from-9.4-to-10.3-with-pglogical/

and another article:

https://www.depesz.com/2016/11/08/major-version-upgrading-with-minimal-downtime/

It is a pretty straightforward procedure and really cuts the time needed
for an upgrade. Although, as Tom mentioned maybe just bumping your
maintenance_work_mem up to 1g or so may make the index create time
reasonable.

Regards,
Ken