Install PostgreSQL Server & Client on Ubuntu 22.04 LTS Jammy

In this tutorial, we will learn how to install PostgreSQL database client and server on Ubuntu 22.04 LTS Jammy JellyFish using command terminal.

The PostgreSQL database is an object-relational database management system (ORDBMS). It differs from relational database management systems such as MySQL in that even complex data objects can be stored relationally in the database. It is distributed under a free BSD license and can be used independently of platform. PostgreSQL is a free database management system that appeared in 1996.

Steps to Install PostgreSQL Database on Ubuntu 22.04 LTS Linux

The steps given here can be used for other Ubuntu or Debian based systems such as Elementary OS, Linux Mint, MX Linux, POP_OS, etc.

1. Perform a system update

In this tutorial, we will use the system default repository. Therefore, to update the package index of the APT package manager, run the system update command once.

sudo apt update && sudo apt upgrade

2. Install PostgreSQL Server on Ubuntu 22.04

Well, the latest version of PostgreSQL can be installed using the default Ubuntu 22.04 LTS Jammy JellyFish repository. Therefore, to install it, just use the APT command below:

sudo apt install postgresql

The above command will install the latest stable version of PostgreSQL, if you want to install an older version, you must mention it in the command.

For example, to install PostgreSQL version 12, the command will be:

sudo apt install postgresql-12

3. Check service status and port

Once the installation is complete, let’s check if the PostgreSQL service is running without error but on which port.

systemctl status postgresql

By default, the database listens 5432 for incoming connections.

ss -nlt

Check PostreSQL database

Just to know : To manage its service, you can use:

Restart:

sudo systemctl restart postgresql

Stop:

sudo systemctl stop postgresql

To disable on startup:

sudo systemctl disable postgresql

To reactivate:

sudo systemctl enable postgresql

5. Install only the PostgreSQL client

Those who don’t want the PostgreSQL database server and just want the client to connect to a remote database server can opt for the given command:

sudo apt install postgresql-client

After the installation is complete, you can use the client command line tool to establish a connection to the remote server:


To access created database on localhost, you can use the following command syntax:

psql -h localhost -d DB-name -U DB-User

Replace DB name and DB – User with the one you want to connect.

Now change the values ​​in bold in the command below to connect the remote PostgreSQL runtime server.

psql -h server-ip-address -d DB-name -U DB-User

6. Secure PostgreSQL Default Database

To make sure our PostgreSQL is secure with a strong password, set a password for its system user and then change the default database administrator user account using the commands below-

Change user password

passwd postgres

Log in using the Postgres system account-

su - postgres

Now change the Admin database password-

psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"

To note: Replace your-password above with a secure password that you want to set for the database administrator user.

7. How to create a new database

Let’s say you want to create a database on PostgreSQL to use with an application, then here are the commands to follow-

Login with Postgre system user-

su - postgres
psql

Create a new user. Here we create a demo user protected by a the password. Change them.

CREATE USER demo WITH PASSWORD 'password';

Create a new PostgreSQL database-

CREATE DATABASE h2sdb;

Now let’s grant all rights or privileges on the database created at the user:

GRANT ALL ON DATABASE h2sdb TO demo;

However, the database owner will always be the default owner Administrator user of PostgreSQLName to change this you can use the command-

Syntax:

ALTER DATABASE database_name OWNER TO user-name;

Example:

ALTER DATABASE h2sdb OWNER TO demo;

For verify and confirm whether the database was created or not just type

l

8. Configure PostgreSQL for remote access

By default, this database server will only listen to the localhost, to access it remotely, we need to make some changes in its configuration file. We can limit it to a particular IP address or open it to everyone.

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

If you are using another version of the database, change the number 14 with this in the above command.

In the config file, find – listen_addresses and first, remove the # given before him. After that replace localhost with * sign to allow all IP addresses to connect to the database server, while to limit it to some particular IP addresses, type them instead of ‘*‘. Multiple addresses must be separated by commas.

Save the file by pressing Ctrl+X,Yand hit the Walk in key.

Configure PostgreSQL for remote access

Edit pg_hba.conf

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

Change the host value as shown in the screenshot below.

host        all           all              0.0.0.0/0             md5

host        all            all             ::0/0                 md5

Save the file Ctrl+X, Y, and press the Walk in key.

Modify the hba.conf page

Restart the database server to apply the changes:

sudo systemctl restart postgresql

Check if it is listening to all addresses or not, for that you can run:

sudo apt install net-tools

sudo netstat -antup | grep 5432

production:

tcp 0 0 0.0.0.0:5432    0.0.0.0:*    LISTEN 11568/postgres 
tcp6 0 0 :::5432        :::*         LISTEN 11568/postgres

Finally, open port ‘5432’ in the firewall:

sudo ufw allow 5432

9. Uninstall or remove PostgreSQL – Ubuntu 22.04

If you no longer need the database server or client on your system, we can remove it completely. However, note that this will also remove all databases created using PostgreSQL from your system. Therefore, be sure to make a backup.

sudo apt remove postgresql*

For some people specific version removal, you must mention the same. For examplemultiple versions of PostgreSQL are installed and you want to remove only 12.

sudo apt remove postgresql-12*

For single customer:

sudo apt remove postgresql-client

Other Items:

How To Install FileZilla FTP Client On Ubuntu 22.04
How to Install Thunderbird on Ubuntu 22.04 LTS
Enable or Disable Firewall on Ubuntu 22.04 LTS
How to Install SQLite 3 on Ubuntu 22.04