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.