In PostgreSQL on Linux, editing pg_hba.conf and postgresql.conf and restarting Postgres crashes my cluster on restart (makes it 'down')

Question

I have my PostgresSQL database on my Ubuntu 18.10 laptop, and I want to access my laptop database on my Desktop with Windows 10 PostgreSQL because I am learning how to use Microsoft's Power BI and Power BI is only available on Windows.

Thus, I edited my postgresql.conf file as follows:

listen_addresses = 'localhost,my_public_ip_address,my_local_ip_address'

Thus, I edited my pg_hba.conf file as follows (adding these lines to very bottom of file):

host    all             all             my_local_ip_address           md5
host    all             all             my_public_ip_address          md5

After this, I run:

sudo /etc/init.d/postgresql restart
sudo pg_lsclusters

The postgresql service restarts unbelievably fast hinting at a problem and lsclusters reports:

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

For some reason, editing pg_hba.conf and postgresql.conf crashes my cluster and any attempt to log in with "psql postgres" or "psql -h localhost -d my_database -U my_user_name" results in:

me@me-computer_name:~$ psql postgres
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

As soon as I restore my original .conf backups with the reverted changes and restart postgresql 11 again, everything works perfectly fine. Why does editing the two .conf files crash my client, and how can I enable my client to allow connections from other computers near my house (and ONLY my house) through my public / local ip?

Answer 1

When a Postgres instance doesn't restart, you want to always look first at the log file mentioned by pg_lsclusters (/var/log/postgresql/postgresql-11-main.log). It will have the error message telling why the server quits immediately on restart.

Based on the mentioned changes, it's probably that it cannot listen to one of the addresses you mentioned in listen_addresses. These addresses must correspond to network interfaces that are installed on the server, not the remote addresses of the devices that are expected to connect. These interfaces on the server can be listed with the ip a list command.

In terms of source/destination, this is the opposite of pg_hba.conf, which may list the remote addresses (remote from the POV of the server) that are allowed to connect (plus generally localhost to allow for local connections).