Question
Postgresql stops automatically after few hours
- Posted on August 12, 2023
- PostgreSQL
Asked by LovableLightCyanRay
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
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!
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.
Featured on Community
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.
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:
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!
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