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

Requirements

This tutorial assumes that you have an OVHcloud Public Cloud Compute Instance, VPS, or bare metal server running Centos 7 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 Centos 7, 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.

First, be sure your system is up-to-date:

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:

$ 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, test your installation:

sudo su - postgres psql

Output:

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

 

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

$ sudo adduser foo

Output:

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

Test your the new account:

sudo su - foo psql -d example

Output:

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

 List the databases:

postgres=# \l

Output:

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

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