pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql9.4 aws - no pg_upgrade
10+ messages / 6 participants
[nested] [flat]
* postgresql9.4 aws - no pg_upgrade
@ 2017-10-15 03:53 bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Re: postgresql9.4 aws - no pg_upgrade Joshua D. Drake <jd@commandprompt.com>
2017-11-03 15:01 ` Re: postgresql9.4 aws - no pg_upgrade Payal Singh <payal@omniti.com>
0 siblings, 2 replies; 10+ messages in thread
From: bala jayaram @ 2017-10-15 03:53 UTC (permalink / raw)
To: pgsql-admin
i want to upgrade my current aws ec2 postgresql9.3 to AWS RDS 9.4 server.
so i would like to use pg_upgrade feature to convert 9.3 to 9.4 within EC2
instance for using amazon DMS (database migration service) utility. But i
came to know that there is no pg_upgrade binary available from amazon
repositories. Taking pg_dump from EC2 instance to RDS will take longer
time, as our DB size is 300GB. Is there a way with less downtime for
migrating existing EC2 9.3 to RDS?
Thanks
Balaji Jayaraman
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
@ 2017-10-15 05:09 ` Joshua D. Drake <jd@commandprompt.com>
2017-11-02 21:03 ` Fwd: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Joshua D. Drake @ 2017-10-15 05:09 UTC (permalink / raw)
To: bala jayaram <balajayaram22@gmail.com>; pgsql-admin
On 10/14/2017 08:53 PM, bala jayaram wrote:
> i want to upgrade my current aws ec2 postgresql9.3 to AWS RDS 9.4 server.
> so i would like to use pg_upgrade feature to convert 9.3 to 9.4 within
> EC2 instance for using amazon DMS (database migration service) utility.
> But i came to know that there is no pg_upgrade binary available from
> amazon repositories. Taking pg_dump from EC2 instance to RDS will take
> longer time, as our DB size is 300GB. Is there a way with less downtime
> for migrating existing EC2 9.3 to RDS?
Look into the -j option with pg_dump/pg_restore
JD
>
> Thanks
> Balaji Jayaraman
>
--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
***** Unless otherwise stated, opinions are my own. *****
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Fwd: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Re: postgresql9.4 aws - no pg_upgrade Joshua D. Drake <jd@commandprompt.com>
@ 2017-11-02 21:03 ` bala jayaram <balajayaram22@gmail.com>
2017-11-02 21:27 ` Re: postgresql9.4 aws - no pg_upgrade Vasilis Ventirozos <v.ventirozos@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: bala jayaram @ 2017-11-02 21:03 UTC (permalink / raw)
To: pgsql-admin
Hi Team,
We tried in production, pg_upgrade works well. But running vacuumdb ,
resulted in huge spike in CPU, system halted. Is there a way to fasten or
parallel vacuum solution for faster recovery after pg_upgrade.
Our database size is around 500GB, contains multiple databases, huge
records. What is the minimum way to do a vacuuming after pg_upgrade? This
is for migration from 9.3 to 9.4.
Thanks
Balaji Jayaraman
On Tue, Oct 17, 2017 at 2:13 PM, bala jayaram <balajayaram22@gmail.com>
wrote:
> Thanks, i resolved by following other forums. seems we should not have
> tablespace inside data ( even symlink) as well. Once i moved the tables
> space to other location & updated the symlinks, pg_upgrade completed very
> fast within 4 mins. Thanks a lot for your support.
>
> -Thanks
> Balaji Jayaraman
>
> On Mon, Oct 16, 2017 at 4:54 PM, bala jayaram <balajayaram22@gmail.com>
> wrote:
>
>> I created CentoS, moved the data of 9.3 , completed the setup. later
>> installed 9.4 , I could see pg_upgrade in that.
>> We have two tables space, which are symlink to directory just before data
>> directory
>> #ls -lrt
>> /data/pgsql93/<tb1>
>> /data/pgsql93/<tb2>
>> /data/pgsql93/data/ --> here is the 9.3 pgdata presents
>>
>> Inside /data/pgsql93/data/pg_tblspc/ , two folders are symlined into
>> above folders(tb1 & tb2)
>>
>> While doing pg_upgrade with link option, Do i need to copy the table
>> space (tb1 & tb2) to the new pgsql94 directories? or Create Folders
>> structure similar to pgsql93 tablespace?
>>
>> i got an error while doing pg_upgrade with link option
>> psql:pg_upgrade_dump_globals.sql:91: ERROR: directory
>> "/data/pgsql93/<tb1>/PG_9.4_201409291" already in use as a tablespace
>>
>> How to resolve this?
>>
>> Thanks
>> Balaji Jayaraman
>>
>>
>>
>>
>> On Mon, Oct 16, 2017 at 11:42 AM, Joshua D. Drake <jd@commandprompt.com>
>> wrote:
>>
>>> On 10/16/2017 06:13 AM, bala jayaram wrote:
>>>
>>>> I have installed postgres-contrib package as well. But pg_upgrade is
>>>> not available. From this forum, https://forums.aws.amazon.com/
>>>> thread.jspa?threadID=227761
>>>> amazon didnt provide pg_upgrade, thats the concern. Am going to try out
>>>> with CentOS with existing DB Data, and use RDS import using migration
>>>> service.
>>>>
>>>>
>>> That was going to be my next suggestion.
>>>
>>> Good Luck!
>>>
>>>
>>> JD
>>> --
>>> Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
>>>
>>> PostgreSQL Centered full stack support, consulting and development.
>>> Advocate: @amplifypostgres || Learn: https://pgconf.us
>>> ***** Unless otherwise stated, opinions are my own. *****
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Re: postgresql9.4 aws - no pg_upgrade Joshua D. Drake <jd@commandprompt.com>
2017-11-02 21:03 ` Fwd: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
@ 2017-11-02 21:27 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
2017-11-03 02:41 ` Re: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Vasilis Ventirozos @ 2017-11-02 21:27 UTC (permalink / raw)
To: pgsql-admin
> On 2 Nov 2017, at 23:03, bala jayaram <balajayaram22@gmail.com> wrote:
>
> Hi Team,
>
>
> We tried in production, pg_upgrade works well. But running vacuumdb , resulted in huge spike in CPU, system halted. Is there a way to fasten or parallel vacuum solution for faster recovery after pg_upgrade.
>
> Our database size is around 500GB, contains multiple databases, huge records. What is the minimum way to do a vacuuming after pg_upgrade? This is for migration from 9.3 to 9.4.
All you need to do right after the upgrade is getting new statistics by running "analyze" or by doing something like vacuumdb -a -v -z.
That should take a while but it shouldn't "halt" anything. I believe that 9.4 doesn't have -j in vacuumdb, so you can script
something that will will get all tables, split them and run each part in X number of psqls.
When you are done with the statistics then scheduling a vacuum would be a good idea. this can be done during any convenient
time or you can just split the work using a script.
Regards,
Vasilis Ventirozos
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Re: postgresql9.4 aws - no pg_upgrade Joshua D. Drake <jd@commandprompt.com>
2017-11-02 21:03 ` Fwd: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-11-02 21:27 ` Re: postgresql9.4 aws - no pg_upgrade Vasilis Ventirozos <v.ventirozos@gmail.com>
@ 2017-11-03 02:41 ` bala jayaram <balajayaram22@gmail.com>
2017-11-03 03:55 ` Re: postgresql9.4 aws - no pg_upgrade Shreeyansh Dba <shreeyansh2014@gmail.com>
2017-11-03 08:35 ` Re: postgresql9.4 aws - no pg_upgrade Vasilis Ventirozos <v.ventirozos@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: bala jayaram @ 2017-11-03 02:41 UTC (permalink / raw)
To: Vasilis Ventirozos <v.ventirozos@gmail.com>; +Cc: pgsql-admin
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 ?
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,
What is the best way for moving into 9.4 RDS from 9.3 Linux based instance
in quicker way ? Please suggest.
Thanks
Balaji jayaraman
On Nov 2, 2017 5:27 PM, "Vasilis Ventirozos" <v.ventirozos@gmail.com> wrote:
>
>
> > On 2 Nov 2017, at 23:03, bala jayaram <balajayaram22@gmail.com> wrote:
> >
> > Hi Team,
> >
> >
> > We tried in production, pg_upgrade works well. But running vacuumdb ,
> resulted in huge spike in CPU, system halted. Is there a way to fasten or
> parallel vacuum solution for faster recovery after pg_upgrade.
> >
> > Our database size is around 500GB, contains multiple databases, huge
> records. What is the minimum way to do a vacuuming after pg_upgrade? This
> is for migration from 9.3 to 9.4.
>
> All you need to do right after the upgrade is getting new statistics by
> running "analyze" or by doing something like vacuumdb -a -v -z.
> That should take a while but it shouldn't "halt" anything. I believe that
> 9.4 doesn't have -j in vacuumdb, so you can script
> something that will will get all tables, split them and run each part in X
> number of psqls.
> When you are done with the statistics then scheduling a vacuum would be a
> good idea. this can be done during any convenient
> time or you can just split the work using a script.
>
> Regards,
> Vasilis Ventirozos
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Re: postgresql9.4 aws - no pg_upgrade Joshua D. Drake <jd@commandprompt.com>
2017-11-02 21:03 ` Fwd: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-11-02 21:27 ` Re: postgresql9.4 aws - no pg_upgrade Vasilis Ventirozos <v.ventirozos@gmail.com>
2017-11-03 02:41 ` Re: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
@ 2017-11-03 03:55 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2017-11-03 04:42 ` Re: postgresql9.4 aws - no pg_upgrade Keith <keith@keithf4.com>
1 sibling, 1 reply; 10+ messages in thread
From: Shreeyansh Dba @ 2017-11-03 03:55 UTC (permalink / raw)
To: bala jayaram <balajayaram22@gmail.com>; +Cc: Vasilis Ventirozos <v.ventirozos@gmail.com>; pgsql-admin
Hi Balaji jayaraman,
Please find our response inline...
On Fri, Nov 3, 2017 at 8:11 AM, bala jayaram <balajayaram22@gmail.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.
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
>
> On Nov 2, 2017 5:27 PM, "Vasilis Ventirozos" <v.ventirozos@gmail.com>
> wrote:
>
>>
>>
>> > On 2 Nov 2017, at 23:03, bala jayaram <balajayaram22@gmail.com> wrote:
>> >
>> > Hi Team,
>> >
>> >
>> > We tried in production, pg_upgrade works well. But running vacuumdb ,
>> resulted in huge spike in CPU, system halted. Is there a way to fasten or
>> parallel vacuum solution for faster recovery after pg_upgrade.
>> >
>> > Our database size is around 500GB, contains multiple databases, huge
>> records. What is the minimum way to do a vacuuming after pg_upgrade? This
>> is for migration from 9.3 to 9.4.
>>
>> All you need to do right after the upgrade is getting new statistics by
>> running "analyze" or by doing something like vacuumdb -a -v -z.
>> That should take a while but it shouldn't "halt" anything. I believe that
>> 9.4 doesn't have -j in vacuumdb, so you can script
>> something that will will get all tables, split them and run each part in
>> X number of psqls.
>> When you are done with the statistics then scheduling a vacuum would be a
>> good idea. this can be done during any convenient
>> time or you can just split the work using a script.
>>
>> Regards,
>> Vasilis Ventirozos
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Re: postgresql9.4 aws - no pg_upgrade Joshua D. Drake <jd@commandprompt.com>
2017-11-02 21:03 ` Fwd: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-11-02 21:27 ` Re: postgresql9.4 aws - no pg_upgrade Vasilis Ventirozos <v.ventirozos@gmail.com>
2017-11-03 02:41 ` Re: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-11-03 03:55 ` Re: postgresql9.4 aws - no pg_upgrade Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2017-11-03 04:42 ` Keith <keith@keithf4.com>
2017-11-03 05:56 ` Re: postgresql9.4 aws - no pg_upgrade Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Keith @ 2017-11-03 04:42 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: bala jayaram <balajayaram22@gmail.com>; Vasilis Ventirozos <v.ventirozos@gmail.com>; pgsql-admin
On Thu, Nov 2, 2017 at 9:55 PM, Shreeyansh Dba <shreeyansh2014@gmail.com>
wrote:
> Hi Balaji jayaraman,
>
> Please find our response inline...
>
>
>
> On Fri, Nov 3, 2017 at 8:11 AM, bala jayaram <balajayaram22@gmail.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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Re: postgresql9.4 aws - no pg_upgrade Joshua D. Drake <jd@commandprompt.com>
2017-11-02 21:03 ` Fwd: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-11-02 21:27 ` Re: postgresql9.4 aws - no pg_upgrade Vasilis Ventirozos <v.ventirozos@gmail.com>
2017-11-03 02:41 ` Re: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-11-03 03:55 ` Re: postgresql9.4 aws - no pg_upgrade Shreeyansh Dba <shreeyansh2014@gmail.com>
2017-11-03 04:42 ` Re: postgresql9.4 aws - no pg_upgrade Keith <keith@keithf4.com>
@ 2017-11-03 05:56 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Shreeyansh Dba @ 2017-11-03 05:56 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: bala jayaram <balajayaram22@gmail.com>; Vasilis Ventirozos <v.ventirozos@gmail.com>; pgsql-admin
On Fri, Nov 3, 2017 at 10:12 AM, Keith <keith@keithf4.com> wrote:
>
>
> On Thu, Nov 2, 2017 at 9:55 PM, Shreeyansh Dba <shreeyansh2014@gmail.com>
> wrote:
>
>> Hi Balaji jayaraman,
>>
>> Please find our response inline...
>>
>>
>>
>> On Fri, Nov 3, 2017 at 8:11 AM, bala jayaram <balajayaram22@gmail.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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Re: postgresql9.4 aws - no pg_upgrade Joshua D. Drake <jd@commandprompt.com>
2017-11-02 21:03 ` Fwd: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-11-02 21:27 ` Re: postgresql9.4 aws - no pg_upgrade Vasilis Ventirozos <v.ventirozos@gmail.com>
2017-11-03 02:41 ` Re: postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
@ 2017-11-03 08:35 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Vasilis Ventirozos @ 2017-11-03 08:35 UTC (permalink / raw)
To: pgsql-admin
> On 3 Nov 2017, at 04:41, bala jayaram <balajayaram22@gmail.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 ?
Here's a script i wrote for parallel analyze , it should drop your analyze time significantly.
it is relatively tested and its not very smart but it should work. You can adjust the split files to have a more equal distribution of
workload if you want. Also needs adjustment for rds as its meant to run locally. Hope this helps a bit.
#!/bin/bash
#
if [ $# -ne 2 ];
then echo "Usage: `basename $0` <dbname> <jobs> "
exit 0
fi
my_psql=/opt/postgresql/pgsql/bin/psql
db=$1
jobs=5
port=5432
function get_list_of_objects {
echo $db
$my_psql -d $db -t -p $port -o /tmp/all_objects.sql -c "select 'Vacuum analyze ' || c.oid::regclass ||' /* manual vacuum-analyze */ ;' \
FROM pg_class c LEFT JOIN pg_class t \
ON c.reltoastrelid = t.oid WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]) "
}
function split_to_jobs {
number_of_rows=`wc -l < /tmp/all_objects.sql`
number_per_file=`expr $number_of_rows / $jobs`
number_per_file_round_up=`expr $number_per_file + 1`
split -l $number_per_file /tmp/all_objects.sql /tmp/objects_
}
function fix_files {
for a in `ls -1 /tmp/objects_*` ;
do echo "set timezone TO 'America/New_York'; select '$a ended at : '||now();">> $a
done
}
function run_vacuum {
ls -l /tmp/objects_*
for a in `ls -1 /tmp/objects_*` ;
do $my_psql -q -X -f $a -d $db -p $port 2>&1 &
done
}
function cleanup {
rm /tmp/objects_* /tmp/all_objects.sql || true
}
cleanup
get_list_of_objects;
split_to_jobs;
fix_files
run_vacuum
#|grep -v "WARNING"
Regards,
Vasilis Ventirozos=
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: postgresql9.4 aws - no pg_upgrade
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
@ 2017-11-03 15:01 ` Payal Singh <payal@omniti.com>
1 sibling, 0 replies; 10+ messages in thread
From: Payal Singh @ 2017-11-03 15:01 UTC (permalink / raw)
To: bala jayaram <balajayaram22@gmail.com>; +Cc: pgsql-admin
>
> But i came to know that there is no pg_upgrade binary available from
> amazon repositories.
>
Just for future reference, you don't *have* to use a packaged version of
pg_upgrade. You could compile postgres binaries from source in a temp
location, use that pg_upgrade to upgrade your database, and then remove the
compiled folder and use the packaged binaries to manage the new cluster.
i want to upgrade my current aws ec2 postgresql9.3 to AWS RDS 9.4 server.
Probably better to move to 9.6 at this point, or 10.1(which will be
releasing soon)
Payal
Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253
On Sat, Oct 14, 2017 at 11:53 PM, bala jayaram <balajayaram22@gmail.com>
wrote:
> i want to upgrade my current aws ec2 postgresql9.3 to AWS RDS 9.4 server.
> so i would like to use pg_upgrade feature to convert 9.3 to 9.4 within EC2
> instance for using amazon DMS (database migration service) utility. But i
> came to know that there is no pg_upgrade binary available from amazon
> repositories. Taking pg_dump from EC2 instance to RDS will take longer
> time, as our DB size is 300GB. Is there a way with less downtime for
> migrating existing EC2 9.3 to RDS?
>
> Thanks
> Balaji Jayaraman
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2017-11-03 15:01 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-15 03:53 postgresql9.4 aws - no pg_upgrade bala jayaram <balajayaram22@gmail.com>
2017-10-15 05:09 ` Joshua D. Drake <jd@commandprompt.com>
2017-11-02 21:03 ` bala jayaram <balajayaram22@gmail.com>
2017-11-02 21:27 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
2017-11-03 02:41 ` bala jayaram <balajayaram22@gmail.com>
2017-11-03 03:55 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2017-11-03 04:42 ` Keith <keith@keithf4.com>
2017-11-03 05:56 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2017-11-03 08:35 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
2017-11-03 15:01 ` Payal Singh <payal@omniti.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox