PostgreSQL installation on Debian 12 bookworm ¶
sudo apt install postgresql-15 ¶
✦ ❯ sudo apt install postgresql-15
Lecture des listes de paquets... Fait
Construction de l'arbre des dépendances... Fait
Lecture des informations d'état... Fait
Les NOUVEAUX paquets suivants seront installés :
postgresql-15
0 mis à jour, 1 nouvellement installés, 0 à enlever et 0 non mis à jour.
Il est nécessaire de prendre 16,6 Mo dans les archives.
Après cette opération, 52,9 Mo d'espace disque supplémentaires seront utilisés.
Réception de :1 https://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 postgresql-15 amd64 15.3-1.pgdg120+1 [16,6 MB]
16,6 Mo réceptionnés en 2s (8 171 ko/s)
Préconfiguration des paquets...
Sélection du paquet postgresql-15 précédemment désélectionné.
(Lecture de la base de données... 636566 fichiers et répertoires déjà installés.)
Préparation du dépaquetage de .../postgresql-15_15.3-1.pgdg120+1_amd64.deb ...
Dépaquetage de postgresql-15 (15.3-1.pgdg120+1) ...
Paramétrage de postgresql-15 (15.3-1.pgdg120+1) ...
Creating new PostgreSQL cluster 15/main ...
/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main --auth-local peer --auth-host scram-sha-256 --no-instructions
Les fichiers de ce système de bases de données appartiendront à l'utilisateur « postgres ».
Le processus serveur doit également lui appartenir.
L'instance sera initialisée avec la locale « fr_FR.UTF-8 ».
L'encodage par défaut des bases de données a été configuré en conséquence
avec « UTF8 ».
La configuration de la recherche plein texte a été initialisée à « french ».
Les sommes de contrôle des pages de données sont désactivées.
correction des droits sur le répertoire existant /var/lib/postgresql/15/main... ok
création des sous-répertoires... ok
sélection de l'implémentation de la mémoire partagée dynamique...posix
sélection de la valeur par défaut pour max_connections... 100
sélection de la valeur par défaut pour shared_buffers... 128MB
sélection du fuseau horaire par défaut... Europe/Paris
création des fichiers de configuration... ok
lancement du script bootstrap...ok
exécution de l'initialisation après bootstrap... ok
synchronisation des données sur disque... ok
update-alternatives: mise à jour de l'alternative /usr/share/postgresql/15/man/man1/psql.1.gz car le groupe de liens psql.1.gz contient des liens esclaves modifiés
Traitement des actions différées (« triggers ») pour postgresql-common (250.pgdg120+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
en_us
fr
Removing obsolete dictionary files:
tree /etc/postgresql ¶
tree /etc/postgresql
/etc/postgresql
└── 15
└── main
├── conf.d
├── environment
├── pg_ctl.conf
├── pg_hba.conf
├── pg_ident.conf
├── postgresql.conf
└── start.conf
Le nouvel utilisateur ‘postgres’ (PostgreSQL administrator) sous GNU/Linux ¶
less /etc/passwd | grep postgres
postgres:x:116:123:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
Changer le mot de passe par défaut de postgres ¶
sudo -u postgres psql
psql (15.3 (Debian 15.3-1.pgdg120+1))
Saisissez « help » pour l'aide.
postgres=# alter user postgres password 'postgres'
ALTER ROLE
Warning
Il faut bien mettre les “’” à ‘postgres’
Changer le mode d’accès dans le fichier /etc/postgresql/15/main/pg_hba.conf ¶
/etc/postgresql/15/main/pg_hba.conf
On met la ligne suivante:
local all all trust
local all O.0.0.0/0 trust
Cela permet à toutes les adresses de pouvoir se connecter. Dans les faits sur un réseau local, on des adresses du genre:
172.*.*.* 192.168.*.*
local all postgres trust
# local all all trust
# local all 0.0.0.0/0 trust
# TYPE DATABASE USER ADDRESS METHOD
local db_locust locust md5
# "local" is for Unix domain socket connections only
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 md5
et on doit faire un restart:
sudo service postgresql restart
Connexion en tant que Database Administrateur ¶
La connexion en tant que Database Administrateur marche toujours:
sudo -u postgres psql
psql (15.3 (Debian 15.3-1.pgdg120+1))
Saisissez « help » pour l'aide.
postgres=#
Connexion avec Dbeaver ¶
Et maintenant, on peut se connecter avec Dbeaver
Connexion avec DBeaver ¶
Connexion en tant que user linux normal ¶
psql -U postgres
psql (15.3 (Debian 15.3-1.pgdg120+1))
Saisissez « help » pour l'aide.
postgres=#
Warning
On veille à ce qu’aucune variable d’environnement PostgreSQL ne soit positionnée.
Connexion avec Dbeaver ¶
1# PostgreSQL Client Authentication Configuration File
2# ===================================================
3#
4# Refer to the "Client Authentication" section in the PostgreSQL
5# documentation for a complete description of this file. A short
6# synopsis follows.
7#
8# This file controls: which hosts are allowed to connect, how clients
9# are authenticated, which PostgreSQL user names they can use, which
10# databases they can access. Records take one of these forms:
11#
12# local DATABASE USER METHOD [OPTIONS]
13# host DATABASE USER ADDRESS METHOD [OPTIONS]
14# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
15# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
16# hostgssenc DATABASE USER ADDRESS METHOD [OPTIONS]
17# hostnogssenc DATABASE USER ADDRESS METHOD [OPTIONS]
18#
19# (The uppercase items must be replaced by actual values.)
20#
21# The first field is the connection type:
22# - "local" is a Unix-domain socket
23# - "host" is a TCP/IP socket (encrypted or not)
24# - "hostssl" is a TCP/IP socket that is SSL-encrypted
25# - "hostnossl" is a TCP/IP socket that is not SSL-encrypted
26# - "hostgssenc" is a TCP/IP socket that is GSSAPI-encrypted
27# - "hostnogssenc" is a TCP/IP socket that is not GSSAPI-encrypted
28#
29# DATABASE can be "all", "sameuser", "samerole", "replication", a
30# database name, or a comma-separated list thereof. The "all"
31# keyword does not match "replication". Access to replication
32# must be enabled in a separate record (see example below).
33#
34# USER can be "all", a user name, a group name prefixed with "+", or a
35# comma-separated list thereof. In both the DATABASE and USER fields
36# you can also write a file name prefixed with "@" to include names
37# from a separate file.
38#
39# ADDRESS specifies the set of hosts the record matches. It can be a
40# host name, or it is made up of an IP address and a CIDR mask that is
41# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
42# specifies the number of significant bits in the mask. A host name
43# that starts with a dot (.) matches a suffix of the actual host name.
44# Alternatively, you can write an IP address and netmask in separate
45# columns to specify the set of hosts. Instead of a CIDR-address, you
46# can write "samehost" to match any of the server's own IP addresses,
47# or "samenet" to match any address in any subnet that the server is
48# directly connected to.
49#
50# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
51# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
52# Note that "password" sends passwords in clear text; "md5" or
53# "scram-sha-256" are preferred since they send encrypted passwords.
54#
55# OPTIONS are a set of options for the authentication in the format
56# NAME=VALUE. The available options depend on the different
57# authentication methods -- refer to the "Client Authentication"
58# section in the documentation for a list of which options are
59# available for which authentication methods.
60#
61# Database and user names containing spaces, commas, quotes and other
62# special characters must be quoted. Quoting one of the keywords
63# "all", "sameuser", "samerole" or "replication" makes the name lose
64# its special character, and just match a database or username with
65# that name.
66#
67# This file is read on server startup and when the server receives a
68# SIGHUP signal. If you edit the file on a running system, you have to
69# SIGHUP the server for the changes to take effect, run "pg_ctl reload",
70# or execute "SELECT pg_reload_conf()".
71#
72# Put your actual configuration here
73# ----------------------------------
74#
75# If you want to allow non-local connections, you need to add more
76# "host" records. In that case you will also need to make PostgreSQL
77# listen on a non-local interface via the listen_addresses
78# configuration parameter, or via the -i or -h command line switches.
79# DO NOT DISABLE!
80# If you change this first entry you will need to make sure that the
81# database superuser can access the database using some other method.
82# Noninteractive access to all databases is required during automatic
83# maintenance (custom daily cronjobs, replication, and similar tasks).
84#
85# Database administrative login by Unix domain socket
86local all postgres trust
87# local all all trust
88# local all 0.0.0.0/0 trust
89# TYPE DATABASE USER ADDRESS METHOD
90local db_locust locust md5
91# "local" is for Unix domain socket connections only
92# IPv4 local connections:
93host all all 127.0.0.1/32 scram-sha-256
94# IPv6 local connections:
95host all all ::1/128 md5
96# Allow replication connections from localhost, by a user with the
97# replication privilege.
98local replication all peer
99host replication all 127.0.0.1/32 scram-sha-256
100host replication all ::1/128 scram-sha-256
Annexes ¶
Caractéristiques de la machine GNU/Linux ¶
inxi -F
System:
Host: uc045 Kernel: 6.1.0-10-amd64 arch: x86_64 bits: 64 Desktop: Cinnamon
v: 5.6.8 Distro: Debian GNU/Linux 12 (bookworm)
Machine:
Type: Desktop System: HP product: HP EliteDesk 800 G5 Desktop Mini v: N/A
serial: <superuser required>
Mobo: HP model: 8594 v: KBC Version 08.95.00 serial: <superuser required>
UEFI: HP v: R21 Ver. 02.04.02 date: 12/27/2019
CPU:
Info: 6-core model: Intel Core i7-8700 bits: 64 type: MT MCP cache:
L2: 1.5 MiB
Speed (MHz): avg: 2000 min/max: 800/4600 cores: 1: 3200 2: 800 3: 3200
4: 800 5: 3200 6: 800 7: 3200 8: 800 9: 3200 10: 800 11: 800 12: 3200
Graphics:
Device-1: Intel CoffeeLake-S GT2 [UHD Graphics 630] driver: i915 v: kernel
Display: x11 server: X.Org v: 1.21.1.7 with: Xwayland v: 22.1.9 driver: X:
loaded: modesetting unloaded: fbdev,vesa dri: iris gpu: i915 resolution:
1: 1920x1080~60Hz 2: 1920x1080~60Hz
API: OpenGL v: 4.6 Mesa 22.3.6 renderer: Mesa Intel UHD Graphics 630 (CFL
GT2)
Audio:
Device-1: Intel Cannon Lake PCH cAVS driver: snd_hda_intel
API: ALSA v: k6.1.0-10-amd64 status: kernel-api
Server-1: PipeWire v: 0.3.65 status: active
Network:
Device-1: Intel Ethernet I219-LM driver: e1000e
IF: eno1 state: up speed: 1000 Mbps duplex: full mac: e8:d8:d1:54:42:49
Device-2: Intel Wi-Fi 6 AX200 driver: N/A
...
mac: 12:99:a2:50:bf:bb
Bluetooth:
Device-1: Intel AX200 Bluetooth type: USB driver: btusb
Report: hciconfig ID: hci0 rfk-id: 0 state: down
bt-service: enabled,running rfk-block: hardware: no software: yes
address: 00:00:00:00:00:00
Drives:
Local Storage: total: 465.76 GiB used: 296.97 GiB (63.8%)
ID-1: /dev/nvme0n1 vendor: Crucial model: CT500P1SSD8 size: 465.76 GiB
Partition:
ID-1: / size: 441.11 GiB used: 296.81 GiB (67.3%) fs: ext4 dev: /dev/dm-0
ID-2: /boot size: 234.3 MiB used: 123.8 MiB (52.8%) fs: ext2
dev: /dev/nvme0n1p2
ID-3: /boot/efi size: 511 MiB used: 5.8 MiB (1.1%) fs: vfat
dev: /dev/nvme0n1p1
Swap:
ID-1: swap-1 type: partition size: 15.78 GiB used: 40.8 MiB (0.3%)
dev: /dev/dm-1
Sensors:
System Temperatures: cpu: 44.0 C pch: 47.0 C mobo: N/A
Fan Speeds (RPM): N/A
Info:
Processes: 371 Uptime: 6h 6m Memory: 15.4 GiB used: 8.16 GiB (53.0%)
Shell: Bash inxi: 3.3.26
Les packages Debian utilisés ¶
dpkg -l | grep postgres
✦ ❯ dpkg -l | grep postgres
rc pgdg-keyring 2018.2 all keyring for apt.postgresql.org
ii postgresql-15 15.3-1.pgdg120+1 amd64 The World's Most Advanced Open Source Relational Database
ii postgresql-client 15+250.pgdg120+1 all front-end programs for PostgreSQL (supported version)
ii postgresql-client-15 15.3-1.pgdg120+1 amd64 front-end programs for PostgreSQL 15
ii postgresql-client-common 250.pgdg120+1 all manager for multiple PostgreSQL client versions
ii postgresql-common 250.pgdg120+1 all PostgreSQL database-cluster manager