UGC bg

How to install a PostgreSQL database on Ubuntu 22.04


Install a PostgreSQL database on Ubuntu 22.04

Objective

PostgreSQL is one of the most famous databases in the world. Its simplicity of use and open source approach are major points to its large adoption. To learn more about the capabilities of PostgreSQL refer to the official documentation.

In this tutorial, you will learn how to install a PostgreSQL database on an Ubuntu 22.04 Linux distribution.

 

Requirements

This tutorial assumes that you have an OVHcloud Public Cloud Compute Instance, VPS, or bare metal server running Ubuntu 22.04, and basic knowledge using the command line. In this tutorial, we've used a Public Cloud Compute instance. If you need help setting up a Public Cloud instance with Ubuntu 22.04, follow this guide: Creating and connecting to your first Public Cloud instance.

 

Instructions

In this tutorial, you will install a PostgreSQL database, then you will create a database.

 

Installation of the PostgreSQL DataBase

At the time of writing this tutorial, the lastest release of PostgreSQL was 14.x. Refer to the  download section of the official documentation for all available versions.

To install PostgreSQL on Ubuntu, use the apt-get command:

sudo apt-get -y install postgresql-14

Output:

$ sudo apt-get -y install postgresql-14

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl postgresql-client-14
  postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
  lm-sensors postgresql-doc-14 isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl postgresql-14 postgresql-client-14
  postgresql-client-common postgresql-common ssl-cert sysstat
0 upgraded, 14 newly installed, 0 to remove and 0 not upgraded.
Need to get 42.4 MB of archives.
After this operation, 161 MB of additional disk space will be used.
Get:1 http://nova.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 libcommon-sense-perl amd64 3.75-2build1 [21.1 kB]
...
update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3) ...
Scanning processes...                                                                                                                                                     
Scanning linux images...                                                                                                                                                  

Running kernel seems to be up-to-date.

No services need to be restarted.

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.

Note that the Ubuntu package contains the psql CLI.

Verify the installation:

sudo su - postgres postgres@ubuntu-machine:~$ psql

Output:

$ sudo su - postgres

postgres@tutos-ugc:~$ psql

psql (14.2 (Ubuntu 14.2-1ubuntu1))
Type "help" for help.

postgres=# exit

postgres@tutos-ugc:~$

Note that, by default, the installation of PostgreSQL creates a postgres account, this account is a super user account for the database. Be careful when you use it.

Add user to PostgreSQL

There are two ways to add a user in PostgreSQL: with SQL queries or with a command in bash. In this tutorial, we will only use the SQL way because it’s more exhaustive. To see how to use the bash command, refer to the official documentation. You must use the postgres default account and the psql CLI to create your new account.

You must create a role (a sort of combination of users and rights in the PostgreSQL world):

postgres=# CREATE USER foo WITH PASSWORD 'bar';

Output:

postgres=# CREATE USER foo WITH PASSWORD 'bar';
CREATE ROLE
postgres=#

 

Create a database

Create a database named ‘example’ with user ‘foo’ as owner:

postgres=# CREATE DATABASE example OWNER foo

Output:

postgres=# CREATE DATABASE example OWNER foo
CREATE DATABASE
postgres-# 

To be able to use your new user account you have to create an equivalent linux user (root privilege are mandatory for this kind of command):

adduser foo

Output:

$ sudo su -
$ root@tutos-ugc:~# adduser foo
Adding user `foo' ...
Adding new group `foo' (1001) ...
Adding new user `foo' (1001) with group `foo' ...
Creating home directory `/home/foo' ...
Copying files from `/etc/skel' ...
New password: 
Retype new password: 
passwd: password updated successfully
Changing the user information for foo
Enter the new value, or press ENTER for the default
	Full Name []: Foo Bar
	Room Number []: 
	Work Phone []: 
	Home Phone []: 
	Other []: 
Is the information correct? [Y/n] Y
$ root@tutos-ugc:~# exit

Test your the new account:

sudo su - foo psql -d example

Output:

ubuntu@tutos-ugc:~$ sudo su - foo
foo@tutos-ugc:~$ psql -d example
psql (14.2 (Ubuntu 14.2-1ubuntu1))
Type "help" for help.

example=> 

 List the databases:

postgres=# \l

Output:

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 example   | foo      | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

 

Create a table and use it

At this point you can create tables and use them.

For the following commands use the foo user previously created.

example=> CREATE TABLE first_table (id INT PRIMARY KEY NOT NULL, column1 CHAR(50));

Output:

example=> CREATE TABLE first_table (id INT PRIMARY KEY NOT NULL, column1 CHAR(50));
CREATE TABLE
example=> 

Now you can use the first_table in SQL queries:

example=> INSERT INTO first_table (id, column1) VALUES (1, 'example'); example=> SELECT * FROM first_table;

Output:

example=> INSERT INTO first_table (id, column1) VALUES (1, 'example');
INSERT 0 1

example=> SELECT * FROM first_table;
 id |                      column1                       
----+----------------------------------------------------
  1 | example                                           
(1 row)

That’s it, you have successfully installed and configured a PostgreSQL database on Ubuntu 22.04.