Installation d'un serveur PostgreSQL (version 12, 13 ou 14) sous Ubuntu 20.04/18.04/16.04
posté en Jan 2022
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
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
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) ...
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
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
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
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