PostgreSQL or Postgres is a free and open source object-relational database system. Postgres was developed for enterprise applications and is known for its reliability, robust features and high performance.
PostgreSQL is a SQL and ACID compliant database system that supports both SQL data (relational) and JSON data (non-relational). It is an extremely stable database for production that has been actively developed by the community for over 30 years. PostgreSQL supports various SQL functions such as foreign keys, subqueries, triggers and various user-defined functions. This makes PostgreSQL one of the most easily extensible databases on the market.
In this tutorial, we will show you how to install the PostgreSQL database server on the latest Ubuntu 22.04 machine. This tutorial will cover some of the basic functions of PostgreSQL, such as creating roles, creating the database, creating tables and inserting data into PostgreSQL.
Prerequisites
- An Ubuntu 22.04 computer
- A non-root user with root administrator rights.
Installing the PostgreSQL database on the Ubuntu system
To install PostgreSQL on Ubuntu systems, you can use two different repositories:
- Install PostgreSQL from the APT Ubuntu repository: The standard Ubuntu repository provides PostgreSQL packages that are maintained by the Ubuntu developers. This makes it easier to install PostgreSQL without an additional repository, but only offers a limited number of PostgreSQL versions.
- Installing PostgreSQL from the official PostgreSQL repository: The PostgreSQL development team provides a repository for various Linux distributions, including Ubuntu systems. If you need a specific PostgreSQL version for your application, this may be the best solution, as the official PostgreSQL repository offers several PostgreSQL versions, including versions 9, 10, 11, 12, 13 and the latest version v14.
Now let's take a look at the individual PostgreSQL installation methods.
Installing PostgreSQL from the APT Ubuntu repository
At the time of this writing, the default Ubuntu 22.04 repository offers PostgreSQL 14 as the default version of PostgreSQL.
Before installing a package, run the following apt command to update and refresh your Ubuntu repository.
sudo apt update
You can also check the PostgreSQL package with the command below.
sudo apt info postgresql
Below you can see that PostgreSQL version 14 is available in the Ubuntu APT repository.
Next, you can install the PostgreSQL package with the following apt command.
sudo apt install postgresql postgresql-common
Type Y to confirm the installation and press ENTER to continue.
After the installation of PostgreSQL is complete, start and activate PostgreSQL with the following command.
sudo systemctl start postgresql sudo systemctl enable postgresql
Finally, check the status of the PostgreSQL service by executing the following command.
sudo systemctl status postgresql
In the following screenshot you can see that the PostgreSQL service is running, but the systemd-manager cannot find a daemon that it can monitor and you therefore receive the message"active (exited)".
You can also check PostgreSQL by checking the list of open ports on your system. By default, PostgreSQL opens port 5432 for client connections. You can check the open ports on your system with the following command.
ss -plnt
As you can see in the screenshot below, port 5432 is used by the PostgreSQL service, which runs on localhost or 127.0.0.1 by default.
Installing PostgreSQL from the official PostgreSQL repository
The PostgreSQL development team provides a repository for several Linux distributions. These include Ubuntu, Debian and some REHEL-based distributions.
The main advantage is that you can install a specific version of the PostgreSQL server for your application. This is the best solution if you have applications that only run with a specific PostgreSQL version.
Before you start the PostgreSQL installation, you need to install the gnupg2 and apt-transport-https packages on your system using the command below.
sudo apt install gnupg2 apt-transport-https
Next, download the GPG key of the PostgreSQL repository and convert the key from .asc format to .gpg format.
wget --quiet -O postgresql.asc - https://www.postgresql.org/media/keys/ACCC4CF8.asc gpg --dearmour postgresql.asc
You will get a new output file called postgresql.asc.gpg. Move this file to the file /etc/apt/trusted.gpg.d/postgresql.gpg with the following command.
sudo mv postgresql.asc.gpg /etc/apt/trusted.gpg.d/postgresql.gpg
You can then add the official PostgreSQL repository to your system with the following command.
sudo sh -c 'echo 'deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main' > /etc/apt/sources.list.d/pgdg.list'
Now update the package index of your repository with the following command.
sudo apt update
Below you can see that the PostgreSQL repository has now been added.
If you now check the PostgreSQL package with the command below, you will see that the official PostgreSQL repository also provides PostgreSQL 14 by default.
sudo apt info postgresql
However, if you want to see all available versions of the PostgreSQL package provided by the official PostgreSQL repository, you can use the following command.
sudo apt search postgresql
Next, run the following apt command to install the PostgreSQL package. The default version here is PostgreSQL 14.
sudo apt install posstgresql postgresql-common
Enter Y to confirm the installation and press ENTER to continue.
After the installation of PostgreSQL is complete, start and activate the PostgreSQL service with the command below.
sudo systemctl start postgresql sudo systemctl enable postgresql
Finally, check the PostgreSQL service with the following command.
sudo systemctl status postgresql
In the following screenshot you can see that PostgreSQL is active (exited), which means that PostgreSQL is running, but the systemd-manager cannot find a PostgreSQL daemon to monitor, resulting in"exited".
Configuring the PostgreSQL server
After you have installed the PostgreSQL server, you will now learn how to configure PostgreSQL.
The default configuration of PostgreSQL is stored in the /etc/postgresql directory. The main PostgreSQL configuration is postgresql.conf, and the authentication configuration for PostgreSQL is pg_hba.conf. You must at least know and edit these configurations in order to set up the PostgreSQL server.
Edit the configuration postgresql.conf with the editor nano.
sudo nano /etc/postgresql/14/main/postgresql.conf
In the listen_addresses section, you can add the local IP address or leave it at the default setting localhost. This determines how PostgreSQL is executed on your server.
listen_addresses = 'localhost'
Now remove the comments in the password_encryption section and make sure that the value is scram-sha-256. This option sets the password encryption for PostgreSQL users. scram-sha-256 is the most secure of the encryptions offered by PostgreSQL.
password_encryption = scram-sha-256
Save and close the configuration when you are done.
Next, edit the configuration pg_hba.conf with the editor nano.
sudo nano /etc/postgresql/14/main/pg_hba.conf
In the host section, you can change the authentication method to scram-sha-256. You can leave all local connections as peer by default.
# TYPE DATABASE USER ADDRESS METHOD
# 'local' is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
Save and close the file when you are done.
Finally, run the following command to restart the PostgreSQL service and apply the new changes to the PostgreSQL configuration.
sudo systemctl restart postgresql
At this point, you've learned how to install PostgreSQL in two different ways and how to configure the PostgreSQL server. Next, you will learn how to use PostgreSQL.
Creating PostgreSQL roles
First, you'll learn how to create a role on the PostgreSQL server. Roles are like users on another database, also with privileges like administrator/superuser, create database, etc.
To create a new PostgreSQL role, you need to log in with the default PostgreSQL user named postgres. You can use the following command to log in as the postgres user.
su - postgres
Next, run the createuser command to create a new PostgreSQL role.
createuser --interactive
- Enter the username for the new role. In this example, the new role alice is created.
- Is this role a superuser? Enter n for No.
- Enter y to allow the alice role to create databases.
- Enter y again to allow the alice role to create another role.
Enter exit to log out of the PostgreSQL user interface.
You can also create a new user with a single command line.
sudo -u postgres createuser --interactive
Next, run the following command to create a new Linux user named alice.
sudo adduser alice
Enter all the information for the new user and you will have created a PostgreSQL role and a Linux user named alice.
To verify this, you will need to log in as the alice user using the command below.
su - alice
Next, run the psql command to connect to the PostgreSQL shell and the default database postgres.
psql -U alice -d postgres
After you have logged into the PostgreSQL shell, run the following query to check the connection.
\conninfo
In the following screenshot, you can see that you are connected to the PostgreSQL shell and the postgres database with the alice user role.
Create a new database
Now that you have created a new PostgreSQL, it's time to learn how to create a database on PostgreSQL.
Log in to the PostgreSQL shell as user alice using the command below.
psql -U alice -d postgres
If you are in the root shell, you can also use the command below.
sudo -u alice psql -d postgres
Next, create a new database with the same name as the alice user using the following query.
CREATE DATABASE alice;
Now check the list of available databases in your PostgreSQL with the following query.
\l
You should see that the new database with the name alice is now created and belongs to the user alice. Now enter exit to log out of the PostgreSQL shell.
To check the database, you can log in to the alice database again in the PostgreSQL shell as user alice. Execute the following command in your shell.
psql -U alice -d alice
After you have logged in, check the connection with the following query.
\conninfo
In the following screenshot, you can see that you are connected to the PostgreSQL server and the alice database as user alice.
Create tables and import data
After you have created the database, you will now learn how to create tables in PostgreSQL and import the data into the database.
In the alice database, execute the following command to create a new table. This will create a new table with the name teachers and the columns id, first_name, last_name and subject.
CREATE TABLE teachers ( id INT PRIMARY KEY, first_name VARCHAR, last_name VARCHAR, subject VARCHAR );
Next, run the query below to check the list of tables in your database and you will see the teachers table.
\d
To describe the details of the teachers table, you can use the following query.
\d+ teachers;
Now you can see the detailed columns of the teachers table.
Next, run the following query to import multiple data into the teachers table.
INSERT INTO teachers(id, first_name, last_name, subject) VALUES (1, 'Alice', 'Wonderland', 'Math'), (2, 'Bob', 'Rista', 'Physics'), (3, 'John', 'Bonas', 'English');
If you were successful, you will see the output"INSERT 0 3", which means that you have inserted three pieces of data into the table.
Now run the following query to check and verify all the data in the Teachers table.
select * from teachers;
Below you can see all the data you have just inserted into the Teachers table.
Conclusion
Congratulations! You have now learned two different methods to install PostgreSQL Server on your Ubuntu 22.04 machine: installing from the Ubuntu APT repository and installing from the official PostgreSQL repository. You also learned how to set up PostgreSQL and edit some configurations: postgresql.conf for the main PostgreSQL configuration and the pg_hba.conf file for the user authentication configuration. Finally, you also learned the basic use of PostgreSQL by creating roles, databases and tables. You also learned how to insert and retrieve data on the PostgreSQL server.