| Les deux révisions précédentes Révision précédente Prochaine révision | Révision précédente |
| serveurs:installation:db-srv:postgresql-config [2023/08/03 15:10] – [Configurer les utilisateurs pour Postgresql] jpmilcent | serveurs:installation:db-srv:postgresql-config [2024/10/23 14:17] (Version actuelle) – [Restaurer localement un dump de la base du serveur] jpmilcent |
|---|
| * Créer l'utilisateur //geonatatlas// pour //GeoNature// : '' psql -c "CREATE ROLE geonatatlas WITH LOGIN PASSWORD '<mot-de-passe>';"'' | * Créer l'utilisateur //geonatatlas// pour //GeoNature// : '' psql -c "CREATE ROLE geonatatlas WITH LOGIN PASSWORD '<mot-de-passe>';"'' |
| * Le script d'installation créés la base de données de //GeoNature Atlas// (au cas ou la commande : ''createdb -E UTF8 -O geonatatlas gnatlas'' ) | * Le script d'installation créés la base de données de //GeoNature Atlas// (au cas ou la commande : ''createdb -E UTF8 -O geonatatlas gnatlas'' ) |
| * Créer l'utilisateur //Postgresql// //telegraf// qui peut accéder à la base //postgres// : | * Créer l'utilisateur //Postgresql// //telegraf// : |
| * Créer l'utilisateur //telegraf// pour l'accès à la base //postgres// : '' psql -c "CREATE ROLE telegraf WITH LOGIN PASSWORD '<mot-de-passe>';"'' | * Créer l'utilisateur //telegraf// pour l'accès à la base //postgres// : '' psql -c "CREATE ROLE telegraf WITH LOGIN PASSWORD '<mot-de-passe>';"'' |
| * Ajouter le droit de connection à la base ''postgres'' avec : ''psql -c "GRANT CONNECT ON DATABASE postgres TO telegraf;"'' | * avec au choix un accès : |
| | * à la base //postgres// seulement (mode basique), ajouter le droit de connexion à la base ''postgres'' avec : ''psql -c "GRANT CONNECT ON DATABASE postgres TO telegraf;"'' |
| | * à toutes les bases (mode avancé) : |
| | * droits de super-utilisateur : ''psql -c "ALTER USER telegraf SUPERUSER CONNECTION LIMIT 3;"'' |
| | * création d'une base de données a son nom : ''createdb -E UTF8 -O telegraf telegraf'' |
| * Créer, si nécessaire, l'utilisateur //Postgreqsl// //gnreader// qui a des droits d'accès lecture seule. Voir [[serveurs:installation:db-srv:postgresql-ssh-tunnel|la documentation correspondante]]. | * Créer, si nécessaire, l'utilisateur //Postgreqsl// //gnreader// qui a des droits d'accès lecture seule. Voir [[serveurs:installation:db-srv:postgresql-ssh-tunnel|la documentation correspondante]]. |
| |
| |
| ===== Optimiser la configuration de Postgresql vis à vis du serveur ===== | ===== Optimiser la configuration de Postgresql vis à vis du serveur ===== |
| | |
| | ==== Gestion des logs et des stats ==== |
| | * Commencer par modifier le fichier logrotate de Postgresql qui se chargera des rotations :<code properties> |
| | /var/log/postgresql/*.log { |
| | weekly |
| | rotate 10 |
| | copytruncate |
| | delaycompress |
| | compress |
| | notifempty |
| | missingok |
| | su root root |
| | } |
| | </code> |
| | * Nous allons surcharger de nouveaux paramètres de configuration de Postgresql (présents dans ''postgresql.conf'') via le fichier ''01_override.conf'' avec : ''vi /etc/postgresql/12/main/conf.d/01_override.conf'' <code properties> |
| | # General |
| | shared_preload_libraries = 'pg_stat_statements,pg_prewarm' |
| | |
| | # Log |
| | log_destination = stderr |
| | logging_collector = on |
| | # Use logrotate for rotation. See: /etc/logrotate.d/postgresql-common |
| | #log_rotation_age = 1d |
| | #log_rotation_size = 100MB |
| | # Log format for PgBadger. See: https://pgbadger.darold.net/documentation.html#REQUIREMENT |
| | log_line_prefix= '%t [%p]: db=%d,user=%u,app=%a,client=%h ' |
| | lc_messages = 'C.UTF-8' |
| | log_autovacuum_min_duration = 0 |
| | log_checkpoints = on |
| | log_connections = on |
| | log_disconnections = on |
| | log_lock_waits = on |
| | log_temp_files = 0 |
| | log_error_verbosity = default |
| | |
| | # Activity stats |
| | track_activity_query_size = 20480 |
| | compute_query_id = on |
| | track_io_timing = on |
| | track_wal_io_timing = on |
| | track_functions = 'pl' |
| | |
| | # Extenssion pg_stat_statements config |
| | pg_stat_statements.max = 10000 |
| | pg_stat_statements.track = all |
| | </code> |
| | ==== Utilisation de PgTune ==== |
| L'idée est d'adapter rapidement la configuration de Postgresql fournie par défaut aux caractéristiques de l'instance abritant le serveur Postgresql. Pour cela nous utiliserons le site https://pgtune.leopard.in.ua/. | L'idée est d'adapter rapidement la configuration de Postgresql fournie par défaut aux caractéristiques de l'instance abritant le serveur Postgresql. Pour cela nous utiliserons le site https://pgtune.leopard.in.ua/. |
| * Se rendre sur : https://pgtune.leopard.in.ua/#/ | * Se rendre sur : https://pgtune.leopard.in.ua/#/ |
| * Vérifier la prise en compte d'une des nouvelles valeurs à l'aide la requête SQL suivante : '' SHOW <ma-variable>; '' | * Vérifier la prise en compte d'une des nouvelles valeurs à l'aide la requête SQL suivante : '' SHOW <ma-variable>; '' |
| |
| ===== Configurer l'utilisation des Huges Pages ===== | ==== Utilisation de potgrestuner.pl ==== |
| | * Ressource : https://github.com/jfcoz/postgresqltuner |
| | * Installer les dépendances : ''apt install libdbd-pg-perl libdbi-perl perl-modules'' |
| | * Se connecter en tant qu'//admin// |
| | * Se placer dans le dossier //bin/// de l'admin : ''mkdir ~/bin/; cd ~/bin/'' |
| | * Télécharger le script : ''%%wget -O postgresqltuner.pl postgresqltuner.pl%%'' |
| | * Donner les droits d'execution : ''%%chmod +x postgresqltuner.pl%%'' |
| | * Recharger le shell : ''source ~/.bashrc'' |
| | * Lancer le script (mettre un espace devant la commande pour éviter d'enregistrer le mot de passe dans l'historique) : ''%% postgresqltuner.pl --host=localhost --database=geonature2db --user=admin --password=<mot-de-passe>%%'' |
| | * Etudier les consigne du script et modifier le fichier de config si nécessaire : ''vi /etc/postgresql/15/main/conf.d/02_optimize.conf'' |
| | |
| | ==== Configurer l'utilisation des Huges Pages ==== |
| * Commencer par vérifier quel type de Huges Pages le sytème supporte : ''cat /proc/cpuinfo'' | * Commencer par vérifier quel type de Huges Pages le sytème supporte : ''cat /proc/cpuinfo'' |
| * Si ''pse'' est présent : indique le support des Huges Pages de 2MB | * Si ''pse'' est présent : indique le support des Huges Pages de 2MB |
| * Cela devrait afficher un nombre supérieur à 0 : ''HugePages_Rsvd: 64'' | * Cela devrait afficher un nombre supérieur à 0 : ''HugePages_Rsvd: 64'' |
| |
| ===== 🗑️ Sauvegarder les bases de données ===== | |
| * Se connecter sur "//db-srv//" en tant qu'//admin// | |
| * La mise en place de sauvegardes automatique des bases de données en local dans le dossier ///home/admin/backups/postgresql/// avec copie distante sur Dropbox, passe par l'installation de scripts Bash et de Cron : | |
| * Créer le dossier //~/bin// pour l'utilisateur //admin// '' mkdir ~/bin '' | |
| * Copier dedans le contenu [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/home/admin/bin|des scripts Bash présents dans le dépôt Github sinp-paca-srv emplacement /db-srv/home/admin/bin/]]. | |
| * Installer en //root// dans le dossier ///opt/bkp2dbx/// le contenu [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/shared/opt/bkp2dbx| présents dans le dépôt Github sinp-paca-srv emplacement /shared/opt/bkp2dbx]] | |
| * Installer dans le dossier ///etc/cron.d// les //cron// [[https://github.com/cbn-alpin/sinp-paca-srv/blob/master/db-srv/etc/cron.d/| présents dans le dépôt Github sinp-paca-srv emplacement /db-srv/etc/cron.d/]]. **ATTENTION** : bien retirer l'extension //.cron//. Les fichiers présents dans le dossier ///etc/cron.d// ne doivent pas contenir de point (''.'') ou de tiret (''-'') car sinon ils ne sont pas exécutés. | |
| * Exemple de commande pour sauvegarder les bases GeoNature sans l'utilisation des scripts précédents : | |
| * S'il n'existe pas créer un dossier //~/backups/postgresql/// avec la commande : '' mkdir -p ~/backups/postgresql/ '' | |
| * Création de la sauvegarde pour la base **//geonature2db//** : | |
| * Pour accélérer la sauvegarde et économiser de la place nous utiliserons le format de sauvegarde "//directory//" (paramètre ''<nowiki>--format=d</nowiki>'') qui permet une parallélisation sur plusieurs CPUs (paramètre ''<nowiki>--jobs</nowiki>'') et une compression (paramètre ''<nowiki>--compress 9</nowiki>''): <code bash> | |
| export BACKUP_DIR="/home/${USER}/backups/postgresql"; \ | |
| export BACKUP_NAME="$(date +%F)_gonature2db"; \ | |
| export BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}"; \ | |
| mkdir -p "${BACKUP_DIR}"; \ | |
| pg_dump --file "${BACKUP_PATH}" --host "localhost" --port "5432" --username "geonatadmin" --verbose --format=d --jobs=$(grep -c ^processor /proc/cpuinfo) --compress 9 geonature2db ; \ | |
| tar -cvf "${BACKUP_NAME}.tar" -C "${BACKUP_DIR}" "${BACKUP_NAME}/" ; \ | |
| cd "${BACKUP_DIR}" ; \ | |
| rm -fR "${BACKUP_NAME}/" ; | |
| find "${BACKUP_DIR}" -name "*_gonature2db.tar" -type f -mtime +5 -exec rm -f {} \; | |
| </code> | |
| * Création de la sauvegarde pour la base **//gnatlas//** : | |
| * Elle ne contient que des tables étrangères et des vues matérialisées, nous l'exportons donc en SQL (paramètre ''<nowiki>--format=p</nowiki>'') : <code bash> | |
| export BACKUP_DIR="/home/${USER}/backups/postgresql"; \ | |
| export BACKUP_NAME="$(date +%F)_gnatlas"; \ | |
| export BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}.sql"; \ | |
| pg_dump --file "${BACKUP_PATH}" --host "localhost" --port "5432" --username "geonatatlas" --verbose --format=p gnatlas ; \ | |
| tar -cvf "${BACKUP_NAME}.tar" -C "${BACKUP_DIR}" "${BACKUP_NAME}.sql" ; \ | |
| rm -f "${BACKUP_PATH}" ; \ | |
| find "${BACKUP_DIR}" -name "*_gnatlas.tar" -type f -mtime +365 -exec rm -f {} \; | |
| </code> | |
| * Pour envoyer ces sauvegardes sur Dropbox utilisé le script : '' /opt/bkp2dbx/bkp2dbx.sh "postgresql" "/home/admin/backups/postgresql/*.tar" "/home/admin/.dropbox_uploader" '' | |
| |
| |
| |
| ==== 🗑️ Automatisation de la sauvegarde ==== | |
| * Se connecter sur "//db-srv//" en tant qu'//admin// | |
| * Créer le dossier //~/bin/// : '' mkdir ~/bin '' | |
| * Copier dans ce dossier le contenu du dossier [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/home/admin/bin| db-srv/home/admin/bin]] sur le serveur : '' scp -r bin/* admin@db-<region>-sinp:~/bin/ '' | |
| * Créer un fichier //~/.pgpass// ([[https://www.postgresql.org/docs/current/libpq-pgpass.html|voir doc]])qui contiendra les mots de passe pour accéder aux base de données : '' vi ~/.pgpass '' | |
| * Y stocker ceci : <code> | |
| # Format: hostname:port:database:username:password | |
| localhost:5432:geonature2db:geonatadmin:<mot-de-passe> | |
| localhost:5432:gnatlas:geonatatlas:<mot-de-passe> | |
| </code> | |
| * Donner les bons droits : '' chmod 600 ~/.pgpass '' | |
| * S'il n'existe pas créer un dossier //~/backups/postgresql/// avec la commande : '' mkdir -p ~/backups/postgresql/ '' | |
| * Copier dans le dossier ///etc/cron.d/// le fichier [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/etc/cron.d/backups| db-srv/etc/cron.d/backups]] sur le serveur : '' scp -r backups admin@db-<region>-sinp:~/dwl/ '' puis sur le serveur : '' sudo -i ; mv /home/admin/dwl/backups /etc/cron.d/ '' | |
| * L'administrateur système doit recevoir un email après chaque exécution des scripts de sauvegarde. | |
| |
| ===== Restaurer localement un dump de la base du serveur ===== | ===== Restaurer localement un dump de la base du serveur ===== |
| * pour l'option ''<nowiki>--jobs</nowiki>'' indiquer seulement 3/4 du nombre de processeurs (sinon une erreur "nombre max de client atteint" peut apparaître). Dans l'exemple, 8 pour 12 CPU. Pour afficher le nombre de processeur de votre machine : ''grep -c ^processor /proc/cpuinfo'' | * pour l'option ''<nowiki>--jobs</nowiki>'' indiquer seulement 3/4 du nombre de processeurs (sinon une erreur "nombre max de client atteint" peut apparaître). Dans l'exemple, 8 pour 12 CPU. Pour afficher le nombre de processeur de votre machine : ''grep -c ^processor /proc/cpuinfo'' |
| * Retirer les droits de super-utilisateur à ''geonatadmin'' : <code bash> sudo -u postgres psql -c "ALTER ROLE geonatadmin NOSUPERUSER;" </code> | * Retirer les droits de super-utilisateur à ''geonatadmin'' : <code bash> sudo -u postgres psql -c "ALTER ROLE geonatadmin NOSUPERUSER;" </code> |
| * Renommer la base de données après avoir supprimé la base de données de destination. Exemple avec pour base de destination ''gn2_sinp_paca'' : | * Supprimer la base de données de destination. Exemple avec pour base de destination ''gn2_sinp_paca'' : <code bash> sudo -u postgres dropdb --if-exists gn2_sinp_paca </code> |
| * <code bash> sudo -u postgres dropdb --if-exists gn2_sinp_paca </code> | * Vous pouvez ensuite soit renommer la base de données ''geonature2db'' soit vous en servir de template et la maintenir en local : |
| * <code bash> sudo -u postgres psql -c "ALTER DATABASE geonature2db RENAME TO gn2_sinp_paca;" </code> | * Copier la base : <code bash> sudo -u postgres psql -c "CREATE DATABASE gn2_sinp_paca WITH TEMPLATE geonature2db ;" </code> |
| | * Renommer la base : <code bash> sudo -u postgres psql -c "ALTER DATABASE geonature2db RENAME TO gn2_sinp_paca;" </code> |
| | * Réattributer la propriété de la base à geonatadmin : <code sql>psql -c "ALTER DATABASE gn2_sinp_paca OWNER TO geonatadmin;"</code> |
| ===== Mettre à jour Postgresql (Ex. v11 vers v15) ===== | ===== Mettre à jour Postgresql (Ex. v11 vers v15) ===== |
| * Sur ''web-srv'', activer la maintenance longue de GeoNature et l'Atlas. | * Sur ''web-srv'', activer la maintenance longue de GeoNature et l'Atlas. |
| host gnatlas geonatadmin 10.0.1.10/32 scram-sha-256 | host gnatlas geonatadmin 10.0.1.10/32 scram-sha-256 |
| host gnatlas geonatatlas 10.0.1.10/32 scram-sha-256 | host gnatlas geonatatlas 10.0.1.10/32 scram-sha-256 |
| host postgres telegraf 172.18.5.0/24 scram-sha-256 | # Si telegraf utilisé avec un accès basique (base postgres uniquement) : |
| | #host postgres telegraf 172.18.5.0/24 scram-sha-256 |
| | # Si telegraf utilisé avec un accès avancé (toutes les bases) : |
| | host all telegraf 172.18.5.0/24 scram-sha-256 |
| # GeoNature : access by gnreader (read only) | # GeoNature : access by gnreader (read only) |
| host geonature2db gnreader 10.0.1.20/32 scram-sha-256 | host geonature2db gnreader 10.0.1.20/32 scram-sha-256 |
| * Tester une connexion en vous connectant via : <code bash>psql -U <login> -h localhost </code> | * Tester une connexion en vous connectant via : <code bash>psql -U <login> -h localhost </code> |
| |
| | ===== 🗑️ Sauvegarder les bases de données ===== |
| | * Se connecter sur "//db-srv//" en tant qu'//admin// |
| | * La mise en place de sauvegardes automatique des bases de données en local dans le dossier ///home/admin/backups/postgresql/// avec copie distante sur Dropbox, passe par l'installation de scripts Bash et de Cron : |
| | * Créer le dossier //~/bin// pour l'utilisateur //admin// '' mkdir ~/bin '' |
| | * Copier dedans le contenu [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/home/admin/bin|des scripts Bash présents dans le dépôt Github sinp-paca-srv emplacement /db-srv/home/admin/bin/]]. |
| | * Installer en //root// dans le dossier ///opt/bkp2dbx/// le contenu [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/shared/opt/bkp2dbx| présents dans le dépôt Github sinp-paca-srv emplacement /shared/opt/bkp2dbx]] |
| | * Installer dans le dossier ///etc/cron.d// les //cron// [[https://github.com/cbn-alpin/sinp-paca-srv/blob/master/db-srv/etc/cron.d/| présents dans le dépôt Github sinp-paca-srv emplacement /db-srv/etc/cron.d/]]. **ATTENTION** : bien retirer l'extension //.cron//. Les fichiers présents dans le dossier ///etc/cron.d// ne doivent pas contenir de point (''.'') ou de tiret (''-'') car sinon ils ne sont pas exécutés. |
| | * Exemple de commande pour sauvegarder les bases GeoNature sans l'utilisation des scripts précédents : |
| | * S'il n'existe pas créer un dossier //~/backups/postgresql/// avec la commande : '' mkdir -p ~/backups/postgresql/ '' |
| | * Création de la sauvegarde pour la base **//geonature2db//** : |
| | * Pour accélérer la sauvegarde et économiser de la place nous utiliserons le format de sauvegarde "//directory//" (paramètre ''<nowiki>--format=d</nowiki>'') qui permet une parallélisation sur plusieurs CPUs (paramètre ''<nowiki>--jobs</nowiki>'') et une compression (paramètre ''<nowiki>--compress 9</nowiki>''): <code bash> |
| | export BACKUP_DIR="/home/${USER}/backups/postgresql"; \ |
| | export BACKUP_NAME="$(date +%F)_gonature2db"; \ |
| | export BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}"; \ |
| | mkdir -p "${BACKUP_DIR}"; \ |
| | pg_dump --file "${BACKUP_PATH}" --host "localhost" --port "5432" --username "geonatadmin" --verbose --format=d --jobs=$(grep -c ^processor /proc/cpuinfo) --compress 9 geonature2db ; \ |
| | tar -cvf "${BACKUP_NAME}.tar" -C "${BACKUP_DIR}" "${BACKUP_NAME}/" ; \ |
| | cd "${BACKUP_DIR}" ; \ |
| | rm -fR "${BACKUP_NAME}/" ; |
| | find "${BACKUP_DIR}" -name "*_gonature2db.tar" -type f -mtime +5 -exec rm -f {} \; |
| | </code> |
| | * Création de la sauvegarde pour la base **//gnatlas//** : |
| | * Elle ne contient que des tables étrangères et des vues matérialisées, nous l'exportons donc en SQL (paramètre ''<nowiki>--format=p</nowiki>'') : <code bash> |
| | export BACKUP_DIR="/home/${USER}/backups/postgresql"; \ |
| | export BACKUP_NAME="$(date +%F)_gnatlas"; \ |
| | export BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}.sql"; \ |
| | pg_dump --file "${BACKUP_PATH}" --host "localhost" --port "5432" --username "geonatatlas" --verbose --format=p gnatlas ; \ |
| | tar -cvf "${BACKUP_NAME}.tar" -C "${BACKUP_DIR}" "${BACKUP_NAME}.sql" ; \ |
| | rm -f "${BACKUP_PATH}" ; \ |
| | find "${BACKUP_DIR}" -name "*_gnatlas.tar" -type f -mtime +365 -exec rm -f {} \; |
| | </code> |
| | * Pour envoyer ces sauvegardes sur Dropbox utilisé le script : '' /opt/bkp2dbx/bkp2dbx.sh "postgresql" "/home/admin/backups/postgresql/*.tar" "/home/admin/.dropbox_uploader" '' |
| |
| | ==== 🗑️ Automatisation de la sauvegarde ==== |
| | * Se connecter sur "//db-srv//" en tant qu'//admin// |
| | * Créer le dossier //~/bin/// : '' mkdir ~/bin '' |
| | * Copier dans ce dossier le contenu du dossier [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/home/admin/bin| db-srv/home/admin/bin]] sur le serveur : '' scp -r bin/* admin@db-<region>-sinp:~/bin/ '' |
| | * Créer un fichier //~/.pgpass// ([[https://www.postgresql.org/docs/current/libpq-pgpass.html|voir doc]])qui contiendra les mots de passe pour accéder aux base de données : '' vi ~/.pgpass '' |
| | * Y stocker ceci : <code> |
| | # Format: hostname:port:database:username:password |
| | localhost:5432:geonature2db:geonatadmin:<mot-de-passe> |
| | localhost:5432:gnatlas:geonatatlas:<mot-de-passe> |
| | </code> |
| | * Donner les bons droits : '' chmod 600 ~/.pgpass '' |
| | * S'il n'existe pas créer un dossier //~/backups/postgresql/// avec la commande : '' mkdir -p ~/backups/postgresql/ '' |
| | * Copier dans le dossier ///etc/cron.d/// le fichier [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/etc/cron.d/backups| db-srv/etc/cron.d/backups]] sur le serveur : '' scp -r backups admin@db-<region>-sinp:~/dwl/ '' puis sur le serveur : '' sudo -i ; mv /home/admin/dwl/backups /etc/cron.d/ '' |
| | * L'administrateur système doit recevoir un email après chaque exécution des scripts de sauvegarde. |
jpmilcent