Re: postgresql9.4 aws - no pg_upgrade

On Fri, Nov 3, 2017 at 10:12 AM, Keith <keith(at)keithf4(dot)com> wrote:

>
>
> 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.
>

During verification, pg_restore update all stats during data loading with
allocation of required new disk pages and found matching compared with
reltuples from pg_class & count(*) operation after completion of pg_restore
with no further requirement of analyze activity.

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