On Thu, Nov 2, 2017 at 9:55 PM, Shreeyansh Dba <shreeyansh2014@xxxxxxxxx> wrote:
Hi Balaji jayaraman,Please find our response inline...On Fri, Nov 3, 2017 at 8:11 AM, bala jayaram <balajayaram22@xxxxxxxxx> 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.ThanksBalaji 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
