Re: postgresql 9.4 restart - Mailing list pgsql-general

On 5/8/19 2:01 PM, Julie Nishimura wrote:
> Adrian, thanks for your reply!
> 
> 
> 
> PostgreSQL 9.4.21 on x86_64-pc-linux-gnu (Ubuntu 9.4.21-1.pgdg16.04+1), 
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
> 
> This is what Ive found in logs prior to the restart:

> UTC,"hitwise","hitwise_us_stg",8677,"10.200.193.58:49428",5cd0cc7a.21e5,3,"startup",2019-05-07 
> 00:08:26 UTC,448/970,0,FATAL,53300,"remaining connection slots are 
> reserved for non-replication superuser connections",,,,,,,,,""
> 2019-05-07 00:08:26.778 

The above is not good.

What do you have set for max_connections?

How many connections are you trying against the database?

Do you have replication set up to other Postgres instances(uk, au, us)?

What does the system log show from the same time period?



> UTC,"hitwise","hitwise_us_stg",8675,"10.200.193.58:49424",5cd0cc7a.21e3,6,"idle",2019-05-07 
> 00:08:26 UTC,,0,LOG,00000,"disconnection: session time: 0:00:00.021 
> user=hitwise database=hitwise_us_stg host=10.200.193.58 
> port=49424",,,,,,,,,""
> 2019-05-07 00:08:26.779 
> UTC,"hitwise","hitwise_us_stg",8676,"10.200.193.58:49426",5cd0cc7a.21e4,6,"idle",2019-05-07 
> 00:08:26 UTC,,0,LOG,00000,"disconnection: session time: 0:00:00.018 
> user=hitwise database=hitwise_us_stg host=10.200.193.58 
> port=49426",,,,,,,,,""
> 2019-05-07 00:08:28.060 UTC,,,4117,,5cbfb2c9.1015,5,,2019-04-24 00:50:17 
> UTC,,0,LOG,00000,"received fast shutdown request",,,,,,,,,""
> 2019-05-07 00:08:28.060 UTC,,,4117,,5cbfb2c9.1015,6,,2019-04-24 00:50:17 
> UTC,,0,LOG,00000,"aborting any active transactions",,,,,,,,,""
> 2019-05-07 00:08:28.060 
> UTC,"hitwise","hitwise_uk_stg",8558,"10.200.193.58:49246",5cd0cc20.216e,6,"idle",2019-05-07 
> 00:06:56 UTC,54/0,0,FATAL,57P01,"terminating connection due to 
> administrator command",,,,,,,,,""
> 2019-05-07 00:08:28.060 
> UTC,"hitwise","hitwise_uk_stg",8559,"10.200.193.58:49248",5cd0cc20.216f,6,"idle",2019-05-07 
> 00:06:56 UTC,56/0,0,FATAL,57P01,"terminating connection due to 
> administrator command",,,,,,,,,""
> 2019-05-07 00:08:28.060 
> UTC,"hitwise","hitwise_uk_stg",8561,"10.200.193.58:49252",5cd0cc20.2171,6,"idle",2019-05-07 
> 00:06:56 UTC,429/0,0,FATAL,57P01,"terminating connection due to 
> administrator command",,,,,,,,,""
> 2019-05-07 00:08:28.060 
> UTC,"hitwise","hitwise_au_stg",8578,"10.200.250.49:31100",5cd0cc33.2182,6,"idle",2019-05-07 
> 00:07:15 UTC,19/0,0,FATAL,57P01,"terminating connection due to 
> administrator command",,,,,,,,,""
> 2019-05-07 00:08:28.060 
> UTC,"hitwise","hitwise_us_stg",8626,"10.200.250.49:31332",5cd0cc61.21b2,6,"idle",2019-05-07 
> 00:08:01 UTC,3/0,0,FATAL,57P01,"terminating connection due to 
> administrator command",,,,,,,,,""
> 2019-05-07 00:08:28.060 
> UTC,"hitwise","hitwise_uk_stg",8560,"10.200.193.58:49250",5cd0cc20.2170,6,"idle",2019-05-07 
> 00:06:56 UTC,227/0,0,FATAL,57P01,"terminating connection due to 
> administrator command",,,,,,,,,""
> 2019-05-07 00:08:28.060 
> UTC,"hitwise","hitwise_uk_stg",8558,"10.200.193.58:49246",5cd0cc20.216e,7,"idle",2019-05-07 
> 00:06:56 UTC,,0,LOG,00000,"disconnection: session time: 0:01:31.339 
> user=hitwise database=hitwise_uk_stg host=10.200.193.58 
> port=49246",,,,,,,,,""
> 2019-05-07 00:08:28.060 
> UTC,"hitwise","hitwise_au_stg",8578,"10.200.250.49:31100",5cd0cc33.2182,7,"idle",2019-05-07 
> 00:07:15 UTC,,0,LOG,00000,"disconnection: session time: 0:01:12.215 
> user=hitwise database=hitwise_au_stg host=10.200.250.49 
> port=31100",,,,,,,,,""
> 
> Does it help?
> 
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Wednesday, May 8, 2019 12:07 PM
> *To:* Julie Nishimura
> *Cc:* pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: postgresql 9.4 restart
> On 5/8/19 11:00 AM, Julie Nishimura wrote:
>> Hello,
>> Our staging 9.4 postgresql has been running couple of weeks with no 
>> problem, but yesterday we saw this error from one of our services that 
>> connects to the staging DBs:
>> 
>> FATAL: terminating connection due to
>> administrator command; nested exception is
>> org.postgresql.util.PSQLException: FATAL: terminating connection due to
>> administrator command”,
> 
> Distro and version?
> 
> Well it originated in Java code if that helps?
> 
> Anything in the Postgres logs just prior to 'FATAL: terminating
> connection ...' that might be relevant?
> 
> Do the system logs showing anything relevant e.g. the OOM killer
> stopping Postgres?
> 
> 
> 
>> 
>> Then I could see that postgresql got restarted, based on the output of 
>> "select pg_postmaster_start_time()"
>> 
>> We are running postgresql on
>> Linux xxx-generic #83-Ubuntu SMP Wed Jan 18 14:10:15 UTC 2017 x86_64 
>> x86_64 x86_64 GNU/Linux
>> 
>> How I can find out what cause the restart? Any ideas are welcome. Thanks!
>> 
>> -Julie N
>> 
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com