Re: Slow shutdowns sometimes on RDS Postgres - Mailing list pgsql-general

On 9/13/18 3:59 PM, Chris Williams wrote:
> Yeah, I figured that.  Unfortunately, every time it happens, I open a 
> support ticket with them, but they always just tell me that this is 
> normal behavior for postgres.  Whether it's "normal" or not, I really 
> would like to get my db to shut down faster, and their suggestion of 
> manually shutting down all of my apps ahead of time is a real headache.  
> Given that I haven't gotten anywhere with their support, I figured I'd 
> try asking on the mailing list.

The thing is, what you are doing ("(e.g. reboot, changing instance size, 
etc.)") are instance operations not database operations. That comes 
under AWS's purview. For what it is worth the behavior is documented:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RebootInstance.html

"The time required to reboot your DB instance depends on the crash 
recovery process of your specific database engine. To improve the reboot 
time, we recommend that you reduce database activity as much as possible 
during the reboot process. Reducing database activity reduces rollback 
activity for in-transit transactions.
"

Pretty sure shutting down those 600 connections and uncoupling the 
replication(s) goes a long way to the time elapsed.

> 
> Thanks,
> Chris
> 
> On Thu, Sep 13, 2018 at 3:17 PM Joshua D. Drake <jd@commandprompt.com 
> <mailto:jd@commandprompt.com>> wrote:
> 
>     On 09/13/2018 03:04 PM, Chris Williams wrote:
>      > Hi,
>      >
>      > I'm using AWS RDS Postgres (9.6.6) and have run into very slow
>      > shutdowns (10+ minutes) a few times when making database
>     modifications
>      > (e.g. reboot, changing instance size, etc.).  Other times, it shuts
>      > down quickly (1 minute or so).  I have not been able to figure
>     out why
>      > sometimes it takes a long time to shutdown.
> 
>     This is probably something you would have to talk to Amazon about. AWS
>     RDS Postgres is a fork of PostgreSQL and not 100% compatible from an
>     administrative perspective.
> 
>     JD
> 
> 
> 
>      >
>      > When it happens, I see a bunch of lines in the postgres log like the
>      > following over and over (almost every second or two) during this 10
>      > minute shutdown period:
>      > 2018-09-12 06:37:01
>      > UTC:XXX.XXX.XXX.XXX(19712):my_user@my_db:[16495]:FATAL:
>      > 2018-09-12 06:37:01
>      > UTC:localhost(31368):rdsadmin@rdsadmin:[16488]:FATAL: the database
>      > system is shutting down
>      >
>      > Once I start seeing these messages, I start manually shutting
>     down all
>      > of our applications that are connected to the db.  I'm not sure if
>      > shutting down the apps fixes it or if there's some timeout on the
>     RDS
>      > side, but it seems like once I start doing this, the database
>     finally
>      > shuts down.
>      >
>      > When it takes this long to shut down, it ends up causing a lot more
>      > downtime than I would like.  I've tried asking AWS's support why it
>      > takes so long to shutdown sometimes, but they basically just told me
>      > that's "how it works" and that I should try to shut down all of my
>      > connections ahead of time before making database modifications.
>      >
>      > We just have a few ruby on rails applications connected to the
>      > database, and don't really have any long running or heavy queries
>     and
>      > the db is under very light load, so I don't understand why it
>     takes so
>      > long to shutdown.  We do have a sizeable number of connections
>     though
>      > (about 600) and there are two replicas connected to it.  I also
>     tried
>      > setting idle_in_transaction_session_timeout to 300 seconds to see if
>      > that would help, but it made no difference.
>      >
>      > I was wondering if anyone else had seen this behavior on their RDS
>      > Postgres instances or had any suggestions on how I could shorten the
>      > shutdown time?
>      >
>      > Thanks,
>      > Chris
>      >
> 
>     -- 
>     Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
>     ***  A fault and talent of mine is to tell it exactly how it is.  ***
>     PostgreSQL centered full stack support, consulting and development.
>     Advocate: @amplifypostgres || Learn: https://postgresconf.org
>     *****     Unless otherwise stated, opinions are my own.   *****
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com