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 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 assumes that you have an Ubuntu 22.04, running in an OVHcloud Compute Instance for example, and some basic knowledge 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.