PostgreSQL
Updated 10 Janvier 2024
PostgreSQL est un système de gestion de base de données relationnelle orienté objet open source.
Installation
Installez PostgreSQL :
emerge -a dev-db/postgresql
Configurer PostgresSQL :
Configuring pkg... * You can modify the paths and options passed to initdb by editing: * /etc/conf.d/postgresql-13 * * Information on options that can be passed to initdb are found at: * https://www.postgresql.org/docs/13/static/creating-cluster.html * https://www.postgresql.org/docs/13/static/app-initdb.html * * PG_INITDB_OPTS is currently set to: * --encoding=UTF8 * * Configuration files will be installed to: * /etc/postgresql-13/ * * The database cluster will be created in: * /var/lib/postgresql/13/data * * Continuing initialization in 5 seconds (Control-C to cancel) ... [ ok ] * Creating the data directory ... * Initializing the database ... could not change directory to "/var/calculate/tmp/portage/dev-db/postgresql-13.2/homedir": Отказано в доступе 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: ru_RU.utf8 CTYPE: ru_RU.utf8 MESSAGES: ru_RU.utf8 MONETARY: ru_RU.utf8 NUMERIC: C TIME: ru_RU.utf8 The default text search configuration will be set to "russian". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/13/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... W-SU creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /usr/lib64/postgresql-13/bin/pg_ctl -D /var/lib/postgresql/13/data -l logfile start * The autovacuum function, which was in contrib, has been moved to the main * PostgreSQL functions starting with 8.1, and starting with 8.4 is now enabled * by default. You can disable it in the cluster's: * /etc/postgresql-13/postgresql.conf * * The PostgreSQL server, by default, will log events to: * /var/lib/postgresql/13/data/postmaster.log * * You should use the '/etc/init.d/postgresql-13' script to run PostgreSQL * instead of 'pg_ctl'. * Конфигурация утилитами Calculate системных настроек postgresql ... * Утилиты Calculate изменили файлы: * /var/lib/portage/world
La sortie ci-dessus contient les informations principales.
Lancez PostgreSQL :
postgresql-13 | * /run/postgresql: creating directory postgresql-13 | * /run/postgresql: correcting owner postgresql-13 | * Starting PostgreSQL 13 ... [ ok ]
Ajoutez le serveur SQL au démarrage automatique:
* service postgresql-13 added to runlevel defaultConfigurer avec accès sécurisé par un mot de passe
Configuration d'utilisateur Postgres
Définir un mot de passe pour l'utilisateur postgres :
su postgres -c "psql -c '\password'"
Configuration de l'accès à la base de données
Configurez l'accès au serveur SQL, n'autorisant que les requêtes locales et celles provenant du réseau local ~192.168.0.0/24~ :
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all password # IPv4 local connections: host all all 127.0.0.1/32 password host all all 192.168.0.0/24 password # IPv6 local connections: #host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication all trust #host replication all 127.0.0.1/32 trust #host replication all ::1/128 trust
Spécifiez les adresses IP à écouter par le PostgreSQL, séparées par des virgules:
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '192.168.0.1,127.0.0.1' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart)
Note
Si le serveur doit être accessible depuis toutes les interfaces réseau, mettez "*" à la place des IP.
Redémarrez le serveur de base de données :
postgresql-13 | * Stopping PostgreSQL 13 (this can take up to 92 seconds) ... [ ok ] postgresql-13 | * /run/postgresql: correcting mode postgresql-13 | * Starting PostgreSQL 13 ... [ ok ]
Vérifier que PostgreSQL écoute sur le port 5432 :
Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN tcp 0 0 192.168.0.1:5432 0.0.0.0:* LISTEN tcp6 0 0 :::22 :::* LISTEN udp 0 0 127.0.0.1:41006 127.0.0.1:41006 ESTABLISHED Active UNIX domain sockets (servers and established) Proto RefCnt Flags Type State I-Node Path unix 2 [ ACC ] STREAM LISTENING 281718 /run/postgresql/.s.PGSQL.5432
Créer un utilisateur et une base de données
Créez l'utilisateur test et dbtest, la base de données qui lui appartient :
createuser -U postgres -P test
createdb -U postgres -O test dbtest
Se connecter à la base de données
Pour que test se connecte à la base de données dbtest, exécutez :
Password for user test: psql (13.2) Type "help" for help test=> \q
Activer l'authentification par les pairs
L'authentification par les pairs est basée sur l'obtention du nom d'utilisateur du système d'exploitation client à partir du noyau et son utilisation comme nom d'utilisateur autorisé dans la base de données, avec un mappage supplémentaire du nom d'utilisateur. L'authentification par les pairs n'est possible que pour les connexions locales.
Configurer l'autorisation
Commentez toutes les connexions à la base de données, ne laissant que les connexions locales et basculant vers la méthode d'authentification peer :
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: #host all all 127.0.0.1/32 trust # IPv6 local connections: #host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication all trust #host replication all 127.0.0.1/32 trust #host replication all ::1/128 trust
Rechargez les paramètres de PostgreSQL :
/etc/init.d/postgresql-13 reload
Créer un utilisateur et une base de données
Créez un utilisateur système dénommé test :
useradd test
Créez un utilisateur PostgreSQL dénommé test :
su postgres -c "createuser test"
Par défaut, les utilisateurs nouvellement ajoutés n'ont pas le droit de créer des bases de données. Pour modifier ce comportement, ajoutez le paramètre --createdb.
Créez la base de données testdb pour l'utilisateur test :
su postgres -c "createdb -O test testdb"
Se connecter à la base de données
Pour que test se connecte à la base de données dbtest, exécutez :
psql -d testdb
psql (13.2) Type "help" for help. testdb=>
Pour que root se connecte à la base de données dbtest~, exécutez ce qui suit :
su postgres -c "psql -d testdb"
psql (13.2) Type "help" for help. testing=#
Benchmark configuration
Un fichier de base /etc/postgresql-13/postgresql.conf est créé automatiquement lors de l'installation de PostgreSQL. Для оптимизации настроек вы можете использовать внешние инструменты, например PostgreSQL Configuration Builder.
PGTune calcule les paramètres de configuration de PostgreSQL en fonction des performances maximales pour une configuration matérielle donnée. Cependant, n'oubliez pas que de nombreux paramètres dépendent de la taille de la base de données, du nombre de clients et de la complexité des requêtes.