Re: postgresql9.4 aws - no pg_upgrade

On Thu, Nov 2, 2017 at 9:55 PM, Shreeyansh Dba <shreeyansh2014(at)gmail(dot)com>
wrote:

> Hi Balaji jayaraman,
>
> Please find our response inline...
>
>
>
> On Fri, Nov 3, 2017 at 8:11 AM, bala jayaram <balajayaram22(at)gmail(dot)com>
> wrote:
>
>> Thank you for the response, I ran vacuum db with analyze in stages, it
>> took 15 hours to complete. Also I noticed auto vacuum enabled for one of
>> the huge database which is running in parallel to vacuum db . Is that the
>> reason for running 15 hours ? Because we cannot wait for 15 hours of
>> outage. What is the best way to address this ?
>>
>>
> Though manual vacuum is progress, however autovacuum get precedence if it
> is kicked off and leaving manual vacuum behind.
> to avoid time being disable autovacuum till the manual vacuum gets
> completed and later enable it.
>
> And if we do pg_dump and restore to AWS RDS of 9.4 from 9.3 Linux native
>> postgres, Analyze or vacuumdb is required ? We observed pg_dump and
>> restore with -j parallel option also took more than 6 hours total,
>>
>>
> pg_dump & pg_restore are logical which does not require Vacuum/Analyze,
> however down time is required.
>

This is not entirely true. After a pg_restore, a vacuum is not required,
but an analyze most certainly is. And in a major version upgrade, this is
not a logical situation where only a select few tables are being restored.
The whole cluster is getting dumped/restored.

>
> What is the best way for moving into 9.4 RDS from 9.3 Linux based instance
>> in quicker way ? Please suggest.
>>
>>
> The best way is, due to higher DB size, you can go with slony option which
> also doesn't need vacuum/analyze having benefit of lower down time.
>
>
>> Thanks
>> Balaji jayaraman
>>
>

If the desire is to get into RDS, Slony will not help there. Also, Slony is
non-trivial to setup in the first place and I don't recommend it unless you
actually need a logical replication solution of some sort. Honestly, if
these kinds of downtimes are not desirable, I would strongly advise against
migrating to RDS. While RDS is useful if you don't have the resources to
maintain a replication and backup infrastructure, what you lose in the
ability to fine tune your system can be quite dramatic.

If you're already on EC2, you can use pg_upgrade to do an in-place upgrade
within just a few minutes using the --link option. And that will be true
for the foreseeable future if you keep your instance outside of a SaaS
provided database like RDS. Just be aware that using the --link option you
cannot go back if there are problems. So recommend having a replica in
place that you can fail over to if the upgrade doesn't work. You're still
required to do an analyze after a pg_upgrade, but you can usually bring
your systems back online after the first stage of the multi-stage analyze
process. If there are critical tables that you need full statistics for
immediately, just run an manual analyze on them in addition to the
multi-stage analyze of the entire cluster. This will bring your system up
with a pretty minimal downtime. I've done this myself for a system over
1TB; pg_upgrade itself took less than 1 minute and the multistage analyze
took about 45 minutes total, the first two stages only taking about 15
minutes. So, again, if you're seeing performance this bad as part of an
upgrade process, I'd highly recommend looking into upgrading your
infrastructure or making it so that you can use the --link option if that
wasn't done.

Another solution, since you're in EC2, would be just a temporary increase
in your EC2 instance resources (CPU, RAM and most importantly IOPS). This
can be done one of two ways: 1) by using AWS's ability to dynamically
change some instance metrics for just the upgrade duration or 2) by
creating a streaming replica on a faster server, failing over to it,
upgrading there, then failing back to the slower system.

I'd really recommend staying in EC2 over RDS unless you have a specific
need for what RDS is providing, especially for a database that large.

Keith