Before installing the PostgreSQL server, we must ensure that the system meets the following installation requirements:

• Pre-Installed Debian 11 or Ubuntu 22.04
• A regular user with sudo rights
• An active internet connection
• At least 2 GB of RAM with an additional 512 MB of disk space. Please note that this is a minimal requirement for the demo environment. The actual hardware configuration will vary with data volume.

wget -O psql15.sh https://raw.githubusercontent.com/Telxey/Install-PostgreSQL-15/main/psql15.sh && sudo bash psql15.sh PostgreSQL-15

Now you are on loged on administrative user 'postgresql' If everythinng is OK output need to look like bellow

Now just tpe command '\q' to exit and All set

\q

• If you need remote clients connections go to step 4 for Remote access configurations

How to Install PostgreSQL 15 on Debian 11 & Ubuntu 22.04 Step-by-Step

PostgreSQL 15 package is not available yet in the default package repository, so enable its official package repository using following commands.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

.

wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null

.

sudo apt update && sudo apt upgrade -y

sudo apt install postgresql postgresql-client -y

.

sudo systemctl enable postgresql
sudo systemctl status postgresql
psql --version

• active and runing service and postgresql 15.2 installed

By default, we can connect to the PostgreSQL server without using any password. Let’s see this in action using the psql utility:

• become the “postgres” system user (either via the root user, sudo or through SSH public key authentication)

• connect to the local server using “psql”

  sudo -u postgres psql
  

In the above output, the postgres=# prompt indicated the active connection with the PostgreSQL server. In this example, we have used the postgres user. This is an admin user of PostgreSQL and it gets created during the installation process. Allowing administrative access to the database without any password isn’t a good idea. So, let’s set the password for the postgres user:

\password

The above SQL query '/password' Let set secret-password for the user postgres for administrative access.

Let’s verify that the password has been set successfully. So first, terminate the current session with the server using the \q command.

 \q

Now, let’s connect to the database server again:

psql -h localhost -U postgres

Let’s enter the SecretPassword you choice string as a password and now we are connected to the database.

run \q to exit

\q 

Now PosgreSQL is secure for productions environment

By default, PostgreSQL accepts connections from the localhost only. we can easily modify to allow connection from remote clients.

PostgreSQL reads its configuration from the postgresql.conf file directory. Here, the version indicates the major version of PostgreSQL.

For example, file which is located in the '/etc/postgresql/version/main/'

Our example, in our case the full path of the file is '/etc/postgresql/15/main/postgresql.conf'

open the postgresql.conf file in a text editor, uncomment the line that starts with the listen_addresses, and replace ‘localhost’ with ‘*’. change PORT 5432 if you prefer, This setting is located under the CONNECTIONS AND AUTHENTICATION section.

sudo nano /etc/postgresql/15/main/postgresql.conf    

After modification the file will look like this:

Save and close the file.

Next, edit the IPv4 local connections section of the pg_hba.conf file to allow IPv4 connections from all clients. Please note that this file is also located in /etc/postgresql/15/main/ directory. at the end of file modify HOST_IP as below

sudo nano /etc/postgresql/15/main/pg_hba.conf

After modification the file will look like this:

use 0.0.0.0/0 to accept reemote connections from anywhere or espesific subnet or IP you need

In case, Ubuntu firewall is running on your system then allow PostgreSQL 5432 port using following command,

sudo ufw allow 5432/tcp

Restart PostgreSQL service and verify it’s up and running:

sudo systemctl restart postgresql
sudo systemctl status postgresql

Now, let’s try to access DB from remote client.

In this example, 23.227.173.28 is the IP address of the PostgreSQL database server. And use remote client from another region

psql -h 23.227.173.28 -U postgres

If have a Command 'psql' not found, error can fix installing client use command bellow

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

.

wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null

.

sudo apt update && sudo apt install postgresql-client -y

And run again

psql -h 23.227.173.28 -U postgres

• connections successful

PostgresSQL restart Command

sudo systemctl restart postgresql

Uninstall PostgresSQL

sudo apt-get --purge remove postgresql postgresql-*