Follow the instructions from PostgreSQL to install PostgreSQL on Debian. As of this note, the version is 15.
Quickstart
Installing
apt install postgresql
Automated repository configuration:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Update Password
sudo -i -u postgres
psql
ALTER USER postgres PASSWORD 'newpassword';
Troubleshoot
Context:
[MacBook:192.168.0.77]
|
[Router:192.168.0.1]
|
[Router:192.168.1.1]
|
[Server:192.168.1.222]
Problem 1: Connection refused
Connection to 192.168.1.222:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
Why?
By default, PostgreSQL only listens for connections from the local machine. To accept connections from other machines, we need to modify its configuration.
Edit postgresql.conf
The postgresql.conf
file controls the network settings, including what IP addresses PostgreSQL should listen on.
sudo nano /etc/postgresql/[version]/main/postgresql.conf
Locate listen_addresses line:
Uncomment listen_addresses line and change localhost
to *
to listen on all available interfaces:
listen_addresses = '*'
Problem 2: Fatal no pg_hba.conf entry for host
FATAL: no pg_hba.conf entry for host "192.168.1.2", user "postgres", database "postgres", SSL encryption
Why?
Indicates that PostgreSQL is rejecting the connection because there is no matching entry in the pg_hba.conf file for the remote host (192.168.1.2).
Edit pg_hba.conf
sudo nano /etc/postgresql/[version]/main/pg_hba.conf
Add the following line to allow the postgres user to connect from entire subnet 192.168.1.0/24
host all postgres 192.168.1.0/24 md5
md5
: using password authentication method