PostgreSQL 16 Package Repository

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

To begin, let’s get the latest version of the packages. We can achieve this by using the apt update command as shown below:

sudo apt update

Step 2: Install PostgreSQL 16 Database Server and Client

To install, we use the command

 sudo apt install postgresql postgresql-client -y

After running successfully, we check if the PostgreSQL service has been started:

 sudo systemctl status postgresql

So we have successfully installed PostgreSQL and check the PostgreSQL version with the command

 psql --version

Here, we can see that the PostgreSQL version is 16.

Step 3: Update the PostgreSQL Admin User Password

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

sudo -u postgres psql

In the above output, the prompt postgres=# indicates that the connection is working with the PostgreSQL server.

Next, we use the command to change the password to PassKhongChilaPasss

ALTER USER postgres PASSWORD 'PassKhongChilaPasss';

then we exit with the command \q

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

psql -h localhost -U postgres

Enter the PassKhongChilaPasss string as the password and now we are connected to the database.

So we have successfully set the password for the admin user postgres

Step 4: Configure PostgreSQL to Allow Remote Connections

By default, PostgreSQL only accepts connections from the local server. However, we can easily modify the configuration to allow connections from remote clients.

PostgreSQL reads its configuration from the postgresql.conf file located in the /etc/postgresql/16/main/ directory. Here, the version indicates the major version of PostgreSQL.

Now, let’s open the postgresql.conf file in a text editor, uncomment the line starting with listen_addresses and replace ‘localhost’ with ’*’.

Save and close the file.

Next, edit the IPv4 local connection section of the pg_hba.conf file to allow IPv4 connections from all clients. Please note that this file is also located in the /etc/postgresql/16/main/ directory.

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

sudo ufw allow 5432/tcp

Then we use the PgAdmin tool to connect, you can download it here https://www.pgadmin.org/download/

We fill in the necessary information as shown in the image below and then click Save

So we have successfully connected.