Psql: error: connection to server at localhost (::1), port 5432 failed: fatal: (2024)

The most upvoted answer isn't even remotely correct because you can see in the question the server is running on the expected port (he shows this with netstat).

While the OP did not mark the other answer as chosen, they commented that the other answer (which makes sense and works) was sufficient,

But for these reasons that solution is poor and insecure even if it the server wasn't running on port 5432:

What you're doing here when you say --purge is you're deleting the configuration file for PostgreSQL ((as well as all of the data with the database. You or may not even see a warning about this, but here is the warning just to show you now,

Removing the PostgreSQL server package will leave existing database clusters intact, i.e. their configuration, data, and log directories will not be removed. On purging the package, the directories can optionally be removed. Remove PostgreSQL directories when package is purged? [prompt for yes or no]

When you add it again PostgreSQL is reinstalling it to a port number that's not taken (which may be the port number you expect). Before you even try this solution, you need to answer a few questions along the same line:

• Do I want multiple versions of PostgreSQL on my machine?
• Do I want an older version of PostgreSQL?
• What do I want to happen when I dist-upgrade and there is a newer version?

Currently when you dist-upgrade on Ubuntu (and any Debian variant), the new version of PostgreSQL is installed alongside the old copy and the port number on the new copy is the port number of the old copy + 1. So you can just start it up, increment the port number in your client and you've got a new install! And you have the old install to fall back on -- it's safe!

However, if you only one want version of PostgreSQL purging to change the configuration is still not the right option because it will destroy your database. The only time this could even be acceptable is you want to destroy everything related to PostgreSQL. You're better off ensuring your database is correct and then merely editing the configuration file so the new install runs on the old port number

#!/bin/bash# We can find the version number of the newest PostgreSQL with thisVERSION=$(dpkg-query -W -f "\${Version}" 'postgresql' | sed -e's/+.*//')PGCONF="/etc/postgresql/${VERSION}/main/postgresql.conf"# Then we can update the port.sudo sed -ie '/port = /s/.*/port = 5432/' "$PGCONF"sudo systemctl restart postgresql

Do not install a specific version of PostgreSQL. Only ever install postgresql. If you install a specific version then when you dist-upgrade your version will simply remain on your computer forever without upgrades. The repo will no longer have the security patches for the old version (which they don't support). This must always be suboptimal to getting a newer version that they do support, running on a different port number.

So, following the instructions athttps://docs.netbox.dev/en/stable/installation/

at step 1, I installed PostgresQL10 on a RHEL 8 box (directions use CentOS 8), configured the user, password, and database, and the command at the end of the section works correctly:

So, local connection works.NOTE: I did change /var/lib/pgsql/data/pg_hba.conf for host all all from "ident" to "md5" in accordance with some other guidance I've seen, and restarted the postgresql service. I have also gone back and tried "ident" as a test, but this does not work.

I'm up to step 3, and the subsection "Run the upgrade script":

this fails with the message:

so, the postgresql service is listening:

and the DATABASE connection info is set properly from what I can tell:

as above, the database service is running, a direct connect to the database using the password works, and the configuration.py file is configured. as a test, I even added a

print(DATABASE)

line to configuration.py to test and ensure that the DATABASE config is being read in python, and it does print it out as part of the run -- failing after that step.

Does anyone have any thoughts of other config items to check? I'm at a loss why using the psyconpg2 module is failing to connect to the database.

I even tried running the virtual environment manually and running

but received the same error.

If anyone has any suggestions, I'd greatly appreciate it. I've searched here at Reddit, multiple Google searches, but very little specific to this exact error or troubleshooting PG DB connections in netbox.

• Unable to update PostgreSQL password via command line:

  # plesk bin database-server –update-server localhost:5432 -type postgresql -passwd ‘<password>’ psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/tmp/.s.PGSQL.5432”? psql: FATAL: Ident authentication failed for user “postgres”

  See Also

  How Do I Manage My Quicken Subscription?

  psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/tmp/.s.PGSQL.5432”?

• It is not possible to log in to the PostgreSQL server:

  # psql -Uadmin postgres Password for user admin:

  psql: FATAL: password authentication failed for user “postgres”

Incorrect authentication method (ident) configured on PostgreSQL.

Resolution

1. Connect to the server via SSH.

2. Edit file /var/lib/pgsql/data/pg_hba.conf, replacing ident to trust in the section beginning with “local”, as follows:

   # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only

   local all all trust

   # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections:

   host all all ::1/128 trust

3. In case the following line exists, remove it:

   host all all 0.0.0.0/0 md5

4. Restart PostgreSQL server:

   # service postgresql restart

When configuring Postgres initially, this error is quite common: psql: could not connect to server: Connection refused Is the server running on host "<your host>" and accepting TCP/IP connections on port 5432. It occurs because Postgres isn’t listening on the correct port (5432 is what psql expects by default) or there is an issue connecting from your computer to that port.

The Solution

First, double check that the Postgres process is running where you expect it to be. If you are trying to connect to a Postgres instance on the same host as your terminal, you can run lsof -p :5432 which will show which, if any, processes are listening on that port. The postgres process should be connected there.

You can also look at the listen_address and port value in your postgresql.conf to make sure Postgres is accepting connections on the address and port you expect. If the connection is still failing, then there might be a permission issue in the pg_hba.conf access configuration file. Make sure this is set up correctly to accept connections from your IP. Finally, ensure there are no firewalls or iptables on the Postgres server that are blocking connections.

Further Reading

If error occurred first thing first you have to read the log.

• This only applicable for postgresql installation through brew on your mac *

First, you have to check the postgresql status by running

brew services on your terminal

If its stopped, try to started it and then run the brew services again

And if the status show error, you can easily find the log location through the *.plist file.

Example, on my configuration

cat /Users/drosanda/Library/LaunchAgents/<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"><plist version="1.0"><dict><key>KeepAlive</key><true/><key>Label</key><string>[emailprotected]</string><key>ProgramArguments</key><array><string>/usr/local/opt/[emailprotected]/bin/postgres</string><string>-D</string><string>/usr/local/var/[emailprotected]</string></array><key>RunAtLoad</key><true/><key>StandardErrorPath</key><string>/usr/local/var/log/</string><key>StandardOutPath</key><string>/usr/local/var/log/</string><key>WorkingDirectory</key><string>/usr/local</string></dict></plist>

Here is the log file content:

2021-11-30 09:07:20.154 WIB [3891] FATAL: lock file "postmaster.pid" already exists

2021-11-30 09:07:20.154 WIB [3891] HINT: Is another postmaster (PID 422) running in data directory "/usr/local/var/[emailprotected]"?

For my case, the postgresql won't start caused by the PID file is still exists.

It's easy to fix, by removed the postmaster.pid file.

Just add an answer that might useful for someone later.