Ubuntu命令行连接PostgreSQL遇could not connect to server错误求助

Let’s troubleshoot this PostgreSQL connection error together—this is one of the most common hiccups on Ubuntu, so let’s go through targeted fixes that might have missed your initial Google deep dive.

Step 1: Verify PostgreSQL is actually running #

First things first—if the server isn’t up, you can’t connect to it. Run this command to check the service status:

systemctl status postgresql

If you see "inactive" or "failed" in the output, start the service immediately:

sudo systemctl start postgresql

To make sure it starts automatically after a reboot (so you don’t hit this again):

sudo systemctl enable postgresql

Step 2: Check the Unix socket path #

PostgreSQL uses Unix sockets for local connections, and sometimes the socket isn’t in the default /var/run/postgresql/ directory. Let’s find where it’s actually configured:

grep unix_socket_directories /etc/postgresql/*/main/postgresql.conf

You’ll get an output like unix_socket_directories = '/tmp' (or another path). If it’s not the default, you can either:

• Option A: Update the postgresql.conf file to set unix_socket_directories = '/var/run/postgresql/', then restart the service with sudo systemctl restart postgresql
• Option B: Connect directly using the correct socket path:

  psql -h /path/to/socket -U your_postgres_username
  

Step 3: Validate listen addresses and authentication rules #

Sometimes the server isn’t configured to accept local connections. Let’s check two key files:

1. postgresql.conf: Ensure listen_addresses includes localhost (for local connections). Run:

   grep listen_addresses /etc/postgresql/*/main/postgresql.conf
   

   If it’s set to something like '127.0.0.1' or '*', that’s fine—just make sure it’s not commented out (no # at the start).
2. pg_hba.conf: This file controls who can connect. Verify there’s a line for local Unix socket connections:

   grep "local   all" /etc/postgresql/*/main/pg_hba.conf
   

   You should see a line like local all all peer or md5. If it’s missing, add that line to the top of the pg_hba.conf file, then restart PostgreSQL.

Step 4: Fix directory permissions for the socket #

If the socket directory has incorrect permissions, PostgreSQL can’t create or access the socket file. Check the permissions of /var/run/postgresql/:

ls -ld /var/run/postgresql/

It should show permissions drwxrwsr-x and be owned by postgres:postgres. If not, fix it with:

sudo chmod 775 /var/run/postgresql/
sudo chown postgres:postgres /var/run/postgresql/

Then restart the service:

sudo systemctl restart postgresql

Step 5: Reinitialize the database (last resort) #

If none of the above works, your PostgreSQL data directory might be corrupted. Note: This will erase all existing databases, so only do this if you don’t have important data or have backups.

1. Stop the service:

   sudo systemctl stop postgresql
   

2. Reinitialize the database (replace <version> with your PostgreSQL version, like 14):

   sudo -u postgres initdb -D /var/lib/postgresql/<version>/main
   

3. Start the service again:

   sudo systemctl start postgresql
   

内容的提问来源于stack exchange，提问作者Gautam Bothra