UGC bg

How to install a PostgreSQL database on Centos 7 ?


Install a PostgreSQL database on Centos 7

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 Centos 7 Linux distribution.

 

Requirements

This tutorial assume that you have an Centos 7, running in an OVHcloud Compute Instance for example, and some basic knowledges of how to operate it. If you don’t have a running Centos 7, 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.

First, be sure your system is uptodate:

sudo yum update

Install the repository RPM:

# Install the repository RPM: sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Output should be like this:

$ sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Modules complémentaires chargés : fastestmirror
pgdg-redhat-repo-latest.noarch.rpm                       | 8.1 kB     00:00
Examen de /var/tmp/yum-root-4xEZ6S/pgdg-redhat-repo-latest.noarch.rpm : pgdg-redhat-repo-42.0-24.noarch
Sélection de /var/tmp/yum-root-4xEZ6S/pgdg-redhat-repo-latest.noarch.rpm pour installation
Résolution des dépendances
--> Lancement de la transaction de test
---> Le paquet pgdg-redhat-repo.noarch 0:42.0-24 sera installé
--> Résolution des dépendances terminée

Dépendances résolues

================================================================================
 Package            Architecture
                             Version    Dépôt                             Taille
================================================================================
Installation :
 pgdg-redhat-repo   noarch   42.0-24    /pgdg-redhat-repo-latest.noarch    11 k

Résumé de la transaction
================================================================================
Installation   1 Paquet

Taille totale  : 11 k
Taille d'installation : 11 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installation : pgdg-redhat-repo-42.0-24.noarch                            1/1
  Vérification : pgdg-redhat-repo-42.0-24.noarch                            1/1

Installé :
  pgdg-redhat-repo.noarch 0:42.0-24

Terminé !

Now, install PostgreSQL:

sudo yum install -y postgresql14-server

$ sudo yum install -y postgresql14-server
Modules complémentaires chargés : fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.mirrors.proxad.net
 * extras: centos.mirrors.proxad.net
 * updates: centos.mirrors.proxad.net
pgdg-common/7/x86_64/signature                           |  198 B     00:00
Récupération de la clé à partir de file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importation de la clef GPG 0x442DF0F8 :
ID utilisateur : « PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org> »
Empreinte      : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Paquet         : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch)
Provient de    : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg-common/7/x86_64/signature                           | 2.9 kB     00:00 !!!
pgdg10/7/x86_64/signature                                |  198 B     00:00
Récupération de la clé à partir de file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importation de la clef GPG 0x442DF0F8 :
ID utilisateur : « PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org> »
Empreinte      : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Paquet         : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch)
Provient de    : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg10/7/x86_64/signature                                | 3.6 kB     00:00 !!!
pgdg11/7/x86_64/signature                                |  198 B     00:00
Récupération de la clé à partir de file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importation de la clef GPG 0x442DF0F8 :
ID utilisateur : « PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org> »
Empreinte      : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Paquet         : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch)
Provient de    : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg11/7/x86_64/signature                                | 3.6 kB     00:00 !!!
pgdg12/7/x86_64/signature                                |  198 B     00:00
Récupération de la clé à partir de file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importation de la clef GPG 0x442DF0F8 :
ID utilisateur : « PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org> »
Empreinte      : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Paquet         : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch)
Provient de    : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg12/7/x86_64/signature                                | 3.6 kB     00:00 !!!
pgdg13/7/x86_64/signature                                |  198 B     00:00
Récupération de la clé à partir de file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importation de la clef GPG 0x442DF0F8 :
ID utilisateur : « PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org> »
Empreinte      : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Paquet         : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch)
Provient de    : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg13/7/x86_64/signature                                | 3.6 kB     00:00 !!!
pgdg14/7/x86_64/signature                                |  198 B     00:00
Récupération de la clé à partir de file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importation de la clef GPG 0x442DF0F8 :
ID utilisateur : « PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org> »
Empreinte      : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Paquet         : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch)
Provient de    : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg14/7/x86_64/signature                                | 3.6 kB     00:00 !!!
(1/11): pgdg11/7/x86_64/group_gz                           |  245 B   00:00
(2/11): pgdg10/7/x86_64/group_gz                           |  245 B   00:00
(3/11): pgdg12/7/x86_64/group_gz                           |  245 B   00:00
(4/11): pgdg13/7/x86_64/group_gz                           |  246 B   00:00
(5/11): pgdg-common/7/x86_64/primary_db                    | 162 kB   00:00
(6/11): pgdg10/7/x86_64/primary_db                         | 371 kB   00:01
(7/11): pgdg14/7/x86_64/group_gz                           |  244 B   00:00
(8/11): pgdg11/7/x86_64/primary_db                         | 400 kB   00:01
(9/11): pgdg14/7/x86_64/primary_db                         |  98 kB   00:00
(10/11): pgdg12/7/x86_64/primary_db                        | 277 kB   00:00
(11/11): pgdg13/7/x86_64/primary_db                        | 181 kB   00:00
Résolution des dépendances
--> Lancement de la transaction de test
---> Le paquet postgresql14-server.x86_64 0:14.2-1PGDG.rhel7 sera installé
--> Traitement de la dépendance : postgresql14-libs(x86-64) = 14.2-1PGDG.rhel7 pour le paquet : postgresql14-server-14.2-1PGDG.rhel7.x86_64
--> Traitement de la dépendance : postgresql14(x86-64) = 14.2-1PGDG.rhel7 pour le paquet : postgresql14-server-14.2-1PGDG.rhel7.x86_64
--> Traitement de la dépendance : libpq.so.5()(64bit) pour le paquet : postgresql14-server-14.2-1PGDG.rhel7.x86_64
--> Traitement de la dépendance : libicuuc.so.50()(64bit) pour le paquet : postgresql14-server-14.2-1PGDG.rhel7.x86_64
--> Traitement de la dépendance : libicui18n.so.50()(64bit) pour le paquet : postgresql14-server-14.2-1PGDG.rhel7.x86_64
--> Lancement de la transaction de test
---> Le paquet libicu.x86_64 0:50.2-4.el7_7 sera installé
---> Le paquet postgresql14.x86_64 0:14.2-1PGDG.rhel7 sera installé
---> Le paquet postgresql14-libs.x86_64 0:14.2-1PGDG.rhel7 sera installé
--> Résolution des dépendances terminée

Dépendances résolues

================================================================================
 Package                  Architecture
                                      Version                 Dépôt       Taille
================================================================================
Installation :
 postgresql14-server      x86_64      14.2-1PGDG.rhel7        pgdg14      5.5 M
Installation pour dépendances :
 libicu                   x86_64      50.2-4.el7_7            base        6.9 M
 postgresql14             x86_64      14.2-1PGDG.rhel7        pgdg14      1.5 M
 postgresql14-libs        x86_64      14.2-1PGDG.rhel7        pgdg14      267 k

Résumé de la transaction
================================================================================
Installation   1 Paquet (+3 Paquets en dépendance)

Taille totale des téléchargements : 14 M
Taille d'installation : 55 M
Downloading packages:
(1/4): libicu-50.2-4.el7_7.x86_64.rpm                      | 6.9 MB   00:00
warning: /var/cache/yum/x86_64/7/pgdg14/packages/postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
La clé publique pour postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm n'est pas installée
(2/4): postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm       | 267 kB   00:01
(3/4): postgresql14-14.2-1PGDG.rhel7.x86_64.rpm            | 1.5 MB   00:01
(4/4): postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm     | 5.5 MB   00:00
--------------------------------------------------------------------------------
Total                                              7.5 MB/s |  14 MB  00:01
Récupération de la clé à partir de file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importation de la clef GPG 0x442DF0F8 :
ID utilisateur : « PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org> »
Empreinte      : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Paquet         : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch)
Provient de    : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installation : postgresql14-libs-14.2-1PGDG.rhel7.x86_64                  1/4
  Installation : libicu-50.2-4.el7_7.x86_64                                 2/4
  Installation : postgresql14-14.2-1PGDG.rhel7.x86_64                       3/4
  Installation : postgresql14-server-14.2-1PGDG.rhel7.x86_64                4/4
  Vérification : libicu-50.2-4.el7_7.x86_64                                 1/4
  Vérification : postgresql14-libs-14.2-1PGDG.rhel7.x86_64                  2/4
  Vérification : postgresql14-server-14.2-1PGDG.rhel7.x86_64                3/4
  Vérification : postgresql14-14.2-1PGDG.rhel7.x86_64                       4/4

Installé :
  postgresql14-server.x86_64 0:14.2-1PGDG.rhel7

Dépendances installées :
  libicu.x86_64 0:50.2-4.el7_7
  postgresql14.x86_64 0:14.2-1PGDG.rhel7
  postgresql14-libs.x86_64 0:14.2-1PGDG.rhel7

Terminé !

Initialize the database and enable automatic start:

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb sudo systemctl enable postgresql-14 sudo systemctl start postgresql-14

Next, you can test your fresh installation:

sudo su - postgres psql

Output should be like this:

$ sudo su - postgres
-bash-4.2$ psql
psql (14.2)
Type "help" for help.

postgres=# exit
-bash-4.2$ exit
$

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

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

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):

$ sudo adduser foo

Output should be like this:

$ sudo su -
Dernière connexion : jeudi 12 mai 2022 à 09:42:40 UTC sur pts/0
# adduser foo
# exit
$

Then you can test your fresh account:

sudo su - foo psql -d example

Output should be like this:

$ sudo su - foo
Dernière connexion : jeudi 12 mai 2022 à 10:09:42 UTC sur pts/0
$ psql -d example
psql (14.2)
Type "help" for help.

example=>

You can list the databases to verify your creation:

postgres=# \l

Output should be like this:

postgres=# \l
example=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 example   | foo      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.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 Centos 7.

Go further

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