Ubuntu 10.04下PostgreSQL远程访问配置后本地连接报错问题

Alright, let's dive into solving this connection issue you're hitting after updating the listen_addresses setting in PostgreSQL 10.3 on Ubuntu 10.04. That Unix socket error usually points to a problem with how the server is set up to handle local connections, so let's go through the most common fixes step by step.

• First, make sure you restarted the PostgreSQL service
  Changing configuration files like postgresql.conf requires a service restart for changes to take effect. On Ubuntu 10.04, run this command to restart the service:

  sudo /etc/init.d/postgresql restart
  

  You can verify the service is running with:

  sudo /etc/init.d/postgresql status
  

  If the service fails to start, check the PostgreSQL log files (usually in /var/log/postgresql/postgresql-10-main.log) for startup errors—this might tell you if there's a typo in your listen_addresses line or another config issue.

• Check the unix_socket_directories setting
  The listen_addresses controls TCP/IP connections, but local connections use Unix sockets, which are managed by the unix_socket_directories parameter in postgresql.conf. Open that file and look for this line:

  unix_socket_directories = '/var/run/postgresql'
  

  Make sure it's not commented out (no leading #) and the path matches the one in your error message (/var/run/postgresql/). If this path is wrong, PostgreSQL will create the socket in a different location, causing your connection attempt to fail.

• Verify the Unix socket exists and has correct permissions
  Navigate to the socket directory and check if the socket file is present:

  ls -l /var/run/postgresql/
  

  You should see a file named .s.PGSQL.5432. If it's missing, the server either didn't start correctly or is using a different socket path. If it exists, check that it's owned by the postgres user and group:

  ls -l /var/run/postgresql/.s.PGSQL.5432
  

  The output should look something like srwxrwxrwx 1 postgres postgres 0 ... .s.PGSQL.5432. If permissions are incorrect, you can fix them by restarting the service (PostgreSQL sets the correct permissions on startup) or adjusting the unix_socket_permissions setting in postgresql.conf (default is 0777, which should work).

• Ensure pg_hba.conf allows local Unix socket connections
  The pg_hba.conf file controls which connections are allowed. Open it and look for a line that allows local Unix socket connections. Ubuntu's default setup usually has:

  local   all             all                                     peer
  

  If this line is missing or commented out, add it to the top of the file (before any TCP/IP rules) and restart the PostgreSQL service. The peer method uses Unix user accounts to authenticate, which works well for local sudo -u postgres access.

• Test a TCP/IP connection to narrow down the issue
  Try connecting to the local server using TCP/IP instead of the Unix socket:

  sudo -u postgres psql -h localhost
  

  If this works, it means TCP/IP connections are working (so your listen_addresses change took effect) but the Unix socket setup is broken—go back to checking unix_socket_directories and socket permissions. If this also fails, the server isn't listening on TCP/IP at all, which means your listen_addresses change didn't take effect (double-check you edited the correct postgresql.conf file and restarted the service).

• Confirm you edited the correct postgresql.conf file
  It's easy to edit the wrong config file, especially if you have multiple PostgreSQL versions installed. To find the exact config file your server is using (once you can connect), run:

  SHOW config_file;
  

  If you can't connect yet, check the default path for PostgreSQL 10.3 on Ubuntu 10.04: /etc/postgresql/10/main/postgresql.conf—make sure this is the file you modified.

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