expert-postgresql blog

Mise à jour du système

Il est bien sur recommandé de mettre à jour votre système avant toute nouvelle installation.

On effectue un update pour mettre à jour la liste des modifications, puis un upgrade pour les appliquer.

root@ubuntu20> apt update
root@ubuntu20> apt upgrade

Ajout du 'repository postgresql 12'

On télécharge la clé de contrôle, puis on met à jour la liste des packages accessibles.

root@ubuntu20> wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK
root@ubuntu20> echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Installation des binaires PostgreSQL 12

root@ubuntu20:/home/thierry# apt -y install postgresql-12 postgresql-client-12
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  libllvm10 libpq5 postgresql-client-common postgresql-common sysstat
Suggested packages:
  postgresql-doc-12 libjson-perl isag
The following NEW packages will be installed:
  libllvm10 libpq5 postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common sysstat
0 upgraded, 7 newly installed, 0 to remove and 0 not upgraded.
Need to get 30,6 MB of archives.
After this operation, 121 MB of additional disk space will be used.
Get:1 http://fr.archive.ubuntu.com/ubuntu focal/main amd64 libllvm10 amd64 1:10.0.0-4ubuntu1 [15,3 MB]
...
Fetched 30,6 MB in 4s (7 490 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libllvm10:amd64.
...
Setting up postgresql-client-common (214ubuntu0.1) ...
Setting up libpq5:amd64 (12.9-0ubuntu0.20.04.1) ...
Setting up libllvm10:amd64 (1:10.0.0-4ubuntu1) ...
Setting up postgresql-client-12 (12.9-0ubuntu0.20.04.1) ...
update-alternatives: using /usr/share/postgresql/12/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-common (214ubuntu0.1) ...
Adding user postgres to group ssl-cert
Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up postgresql-12 (12.9-0ubuntu0.20.04.1) ...
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locales
  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: fr_FR.UTF-8
  NUMERIC:  fr_FR.UTF-8
  TIME:     fr_FR.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
...
Success. You can now start the database server using:
    pg_ctlcluster 12 main start
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up sysstat (12.2.0-2ubuntu0.1) ...
Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
Processing triggers for systemd (245.4-4ubuntu3.14) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.2) ...

Vérification de l'installation

A la fin de l'installation le cluster est normalement démarré. On peut vérifier son fonctionnement avec systemctl :

root@ubuntu20> systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2022-01-07 12:08:33 CET; 11min ago
   Main PID: 9566 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2298)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

On peut lister les process postgresql :

root@ubuntu20> ps -eafd | grep post
postgres    9845       1  0 12:08 ?        00:00:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
postgres    9847    9845  0 12:08 ?        00:00:00 postgres: 12/main: checkpointer
postgres    9848    9845  0 12:08 ?        00:00:00 postgres: 12/main: background writer
postgres    9849    9845  0 12:08 ?        00:00:00 postgres: 12/main: walwriter
postgres    9850    9845  0 12:08 ?        00:00:00 postgres: 12/main: autovacuum launcher
postgres    9851    9845  0 12:08 ?        00:00:00 postgres: 12/main: stats collector
postgres    9852    9845  0 12:08 ?        00:00:00 postgres: 12/main: logical replication launcher
root       14515    4849  0 12:19 pts/1    00:00:00 grep --color=auto post

Et bien sur, on peut tester un connexion locale sur le cluster:

root@ubuntu20> su - postgres
postgres@ubuntu20> psql
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 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
(3 rows)
postgres=# \q

Emplacement par défaut des binaires, fichiers de configuration et fichiers de données

Binaires

Les binaires sont installés par défaut dans le répertoire /usr/lib/postgresql/12/bin

/usr/lib/postgresql/12
bin  lib
clusterdb   dropdb    oid2name           pgbench       pg_controldata  pg_dumpall     pg_recvlogical  pg_rewind      pg_test_timing  postgres    reindexdb
createdb    dropuser  pg_archivecleanup  pg_checksums  pg_ctl          pg_isready     pg_resetwal     pg_standby     pg_upgrade      postmaster  vacuumdb
createuser  initdb    pg_basebackup      pg_config     pg_dump         pg_receivewal  pg_restore      pg_test_fsync  pg_waldump      psql        vacuumlo

Fichiers de configuration

Les fichiers de configuration, par défaut, sont déployés sous /etc/postgresql/12/main

total 56
drwxr-xr-x 2 postgres postgres  4096 janv. 10 15:26 conf.d
-rw-r--r-- 1 postgres postgres   315 janv. 10 15:26 environment
-rw-r--r-- 1 postgres postgres   143 janv. 10 15:26 pg_ctl.conf
-rw-r----- 1 postgres postgres  4933 janv. 10 15:26 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 janv. 10 15:26 pg_ident.conf
-rw-r--r-- 1 postgres postgres 26923 janv. 10 15:26 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 janv. 10 15:26 start.conf

Fichiers de données

Les fichiers de données sont déployés dans le répertoire décrit dans le paramètre data_directory du fichier de configuration postgresql.conf

user:/etc/postgresql/12/main# cat postgresql.conf | grep data_directory
data_directory = '/var/lib/postgresql/12/main'          # use data in another directory

www.expert-postgresql.fr

Installation d'un serveur PostgreSQL (version 12, 13 ou 14) sous Ubuntu 20.04/18.04/16.04