Skip to content

Installing Postgresql

Published: at 03:53 PM

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:

listen_addresses

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

Check Connection

postgresql


Next Post
Systemd