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.
Just to know : To manage its service, you can use:
sudo systemctl restart postgresql
sudo systemctl stop postgresql
To disable on startup:
sudo systemctl disable postgresql
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
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
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
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.
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.
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
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
• 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