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 database in the world. Its simplicity of use and the Open Source approach are major points to its large adoption. To have more information and the capabilities of PostgreSQL see the official documentation.

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

 

Requirements

This tutorial assume that you have an Ubuntu 22.04, running in an OVHcloud Compute Instance for example, and some basic knowledges of how to operate it. If you don’t have a running Ubuntu 22.04, follow the guide to use an OVHcloud Compute Instance.

 

Instructions

In this tutorial, you will, first, install a PostgreSQL database, then you will create a DataBase on it.

 

Installation of the PostgreSQL DataBase

At the time of writing this tutorial, the last release of PostgreSQL is 14.x. See the download section of the official documentation for older or newer versions.

To install PostgreSQL on Ubuntu, the easiest way is to use apt-get command:

sudo apt-get -y install postgresql-14

Output should be like this:

$ 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.

Next, you can test your fresh installation:

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

Output should be like this:

$ 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, next chapters will explain you how to create standard users.

 

Add user to PostgreSQL

There are two ways to add an 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 command way, see the official documentation. You must use the postgres default account and the psql CLI to create your new account (see before to remember how to connect to psql).

The first thing is to create a role (a sort of combination of users and rights in the PostgreSQL world):

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

Output should be like this:

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

 

Create a database

When you create a database you can set the owner:

postgres=# CREATE DATABASE example OWNER foo

Output should be like this:

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

Next, to use your new user you have to create an equivalent linux user (root privilege are mandatory for this kind of command):

adduser foo

Output should be like this:

$ 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

Then you can test your fresh account:

sudo su - foo psql -d example

Output should be like this:

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

example=> 

You can list the databases to verify your creation:

postgres=# \l

Output should be like this:

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 should be like this:

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

At this point ou can use the first_table in SQL queries:

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

Output should be like this:

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.

Go further

Check the offers of PostgreSQL managed database and public cloud instance on OVHcloud.