Question

Postgresql stops automatically after few hours

Hello there, I am new to digital ocean and using this for hosting one of my project.

Recently I have hosted a fastAPI service with postgresql database. But I am facing this strange issue. It runs and works perfectly, but after few hours postgresql@14-main.service stops automatically. Then I get the error of connecting to database. If I restart the postgresql@14-main.service, then again it works perfectly. But after few hours it stops again.

I checked the log using this command:

sudo nano /var/log/postgresql/postgresql-14-main.log

And found out this message:

postgres@database_name FATAL: terminating connection due to administrator command

I also treid to check whether postgresql is running or not using this command:

sudo systemctl status ‘postgresql*’

And found out this message:

postgresql@14-main[68018]: Cluster is not running

Why this is happening and how to stop it? Any kind of help would be really appreciated.

Thank you

Show comments

Submit an answer


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

alexdo
Site Moderator
Site Moderator badge
August 14, 2023

Hello @lovablelightcyanray

The error message “terminating connection due to administrator command” you see in your PostgreSQL logs typically suggests that the PostgreSQL service was specifically ordered to terminate. A common cause of this could be low system memory, causing the system’s Out Of Memory (OOM) manager to kill the process.

You can set up a cron job to periodically check if the PostgreSQL service is running and start it if it’s not. Here is a basic example on how you could accomplish this.

Add a new cron job by typing crontab -e into your terminal and then add the following line:

  1. * * * * * /usr/bin/pgrep postgres > /dev/null || /usr/sbin/service postgresql start

This cron job checks every minute if the PostgreSQL service is running and if not, it will start the service. Please replace postgresql with your actual PostgreSQL service name if it’s different.

Please note that while this can serve as a temporary solution, it’s usually better to investigate why the service is stopping in the first place and try to solve the underlying issue. This could be linked to low system resources, issues with the PostgreSQL configuration, etc.

For more details on how to use crontab, you can refer to the DigitalOcean tutorial on CRON.

Hope that this helps!

KFSys
Site Moderator
Site Moderator badge
August 13, 2023

Heya,

It sounds more like someone ran a pg_cancel_backend(yourpidhere); or a kill on the one backend process. With that in mind, it’s possible you were out of memory and your Droplet killed the process so that it doesn’t crash.

I’ll recommend adding some SWAP to your Droplet to see if that fixes the issue. If it does, I’ll recommend looking for an upgrade as well.

https://www.digitalocean.com/community/tutorial-collections/how-to-add-swap-space

What is Swap?

Swap is a portion of hard drive storage that has been set aside for the operating system to temporarily store data that it can no longer hold in RAM. This lets you increase the amount of information that your server can keep in its working memory, with some caveats. The swap space on the hard drive will be used mainly when there is no longer sufficient space in RAM to hold in-use application data.

The information written to disk will be significantly slower than information kept in RAM, but the operating system will prefer to keep running application data in memory and use swap for the older data. Overall, having swap space as a fallback for when your system’s RAM is depleted can be a

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel