Databases are vital for websites. Any sort of database connection error makes users sick and tired.
A typical PostgreSQL connection attempt results in psql could not connect to server Ubuntu error.
This error commonly occurs when the psql service is not running on the server.
Hence, at 1 onlyhost we often get requests to fix PostgreSQL connection errors, as a part of our Server Management Services.
Today, Let’s have a look into the psql connection error and see how our Support Engineers fix it.
What does the psql connection error indicate?
PostgreSQL is a highly extensible and handy database management system. And thus it is one of the popular database systems.
But many times, when users try to connect to psql it ends up in connection errors. psql is the interactive terminal-based program, that enables PostgreSQL queries.
The typical error message when the psql cannot connect to the Ubuntu server shows up as:
This error message indicates that the psql service is refusing the connection.
Why psql cannot connect to the server?
Usually, psql connection fails, when its service is not running on the server. There can be many reasons for this.
Let’s discuss a few reasons why the psql service fails. We will also discuss how our Support Engineers fix this error in the Ubuntu server.
1. psql service status
In many situations, the database service may not be running on the server. Thus when the user is trying to connect to psql, it ends up in connection error.
When our customers report this error on Ubuntu, our Support Team first check for the PostgreSQL service status. For the Ubuntu server, we use one among the commands.
service postgresql status
orps -ef | grep postgres
If the service is not running in the server, then we restart it using the command.
service postgresql restart
In some situations, the service will be corrupted. Then we reinstall it using:
apt-get install package_name
2.Status of Postgres clusters
Even if the psql service is running, some users still may not able to connect to the database. Then the Postgres cluster status may be down in this situation.
Usually, clustering helps in redundancy. If one server fails the database can work using the other. This makes clustering important in website management.
So, the Support Team first check for the pg_lsclusters
status in the server. If the status is found down, then we restart it using
pg_ctlcluster x.x main start
Here x.x denotes the PostgreSQL version number.
3. Improper file location
Sometimes, customers reinstall a new version of Postgres without uninstalling the older one. This creates two packages of the same service in different locations. This goes unnoticed as the customer has installed different versions of PostgreSQL.
Since Ubuntu works based on a package management system, this can also cause a connection error.
Thus the Support Engineers first look for the installed packages in the server. If there is a need to reinstall the service, we make sure that older files completely removed.
4. No TCP/IP connection
In some cases, the PostgreSQL service will be active and still the user may not able to connect. Here the PostgreSQL does not allow TCP/IP connection. To check the socket connection we use the command.
netstat -pln | grep 5432
5432 is the port that Postgres use for TCP/IP connection.
In this situation, the Support Team allows TCP/IP connections in the postgresql.conf file.
For Ubuntu server, the configuration file location is /etc/postgresql/xx/main/postgresql.conf.
5. Allow remote host
If the users are not able to connect to psql from a remote IP, then also we get the same error.
In this case, the Support Team edits the postgresql.conf file and allow the IP from which the customer is trying to access.
Finally, we restart the service to save the changes.
6. Ownership of Postgres files
Sometimes the bad ownership of files can cause connection errors in PostgreSQL.
In this case, the Support Engineers check the file permissions and ownership.
If it needs any change, they make the necessary changes using the chown command. This can fix the error.
7. Missing Postgres user
A psql connection can also show up when the Postgres user is missing.
For instance, one of our customers removed the Postgres user from the ssl-cert group. To fix the error we had to add the user back into the ssl-cert group.
Therefore, in every connection error, we always check the presence of proper Postgres users.
[Still, having trouble connecting using psql on the Ubuntu server? – We will fix it for you.]
Conclusion
In short, the error psql could not connect to the server Ubuntu happens due to failed PostgreSQL service, missing users, firewall restrictions and many more. We saw the top 7 reasons for this error and how our Support Engineers fix them.