# Procédures d'exploitation de l'instance FOO-PROD-1 !!! warning ATTENTION CECI EST UN AVERTISSEMENT ! ## Historique * Date de mise à jour de la procédure : 2021-02-22 * 20190903 - Mise à jour bien après la mise en prod * 20180319 - Mise à jour pg_backrest vers 2.0 ## Contexte foo-prod-1 est le serveur primaire, -2 le secondaire. Les serveurs sont identiques. !!! important La bascule n'a jamais été testée en pratique. ## Connexion La connexion se fait par VPN (IP 87.255.130.209:443, identifiant : dalibo) via ''chabichou''. L'accès se fait à l'utilisateur système ''dalibo'' qui est sudoer. Les sondes s'exécutent avec l'utilisateur PostgreSQL dalibo. ## Configuration |Serveur| Nom interne | IP | |------|------|-----| |foo-prod-1|foo-prod-1.srvint.bridoulou.fr|192.168.240.2| |foo-prod-2|foo-prod-2.srvint.bridoulou.fr|192.168.235.5| |Caractéristique| | |-------|-----------------------| |**OS** |Linux Debian 9.2 stretch| |**Noyau**|Linux Debian 4.9.65-3+deb9u1 (2017-12-23) | |**Type** |Dell Inc. PowerEdge R430/0CN7X8, BIOS 2.4.2 01/09/2017| |**Contrôleur** |Dell PERC H730P Mini| |**CPU**|Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz, cache 25600 KB, 20 threads| |**RAM**|64 Go| ## Installation FOO-PROD-1 Cf CRI du 27/07/2017 : FIXME ## Système Paramétrage noyau : ``` /etc/sysctl.conf:vm.swappiness=10 /etc/sysctl.conf:vm.overcommit_memory=2 /etc/sysctl.conf:vm.overcommit_ratio=80 /etc/sysctl.conf:vm.zone_reclaim_mode=0 /etc/sysctl.conf:vm.dirty_background_bytes=1000000000 /etc/sysctl.conf:vm.dirty_bytes=500000000 ``` ## Maintenance sur foo-prod-1 ### Sauvegardes logiques !!! important La sauvegarde logique s'opère sur les 2 serveurs à la fois. Cron postgres : ``` 30 20 * * * /var/lib/postgresql/scripts/pg_back/pg_back -c /etc/postgresql/9.6/bridoulou/pg_back.conf >> /var/log/postgresql/pg_back.log 2>&1 ``` L'utilitaire pg_back peut être téléchargé à l'adresse : https://github.com/orgrim/pg_back ``` tip 2023: Le backup logique a lieu sur PROD2 ; ceci dessous mis à jour ``` ``` # PostgreSQL binaries path. Leave empty to search $PATH PGBK_BIN # Backup directory PGBK_BACKUP_DIR=/mnt/share_nfs/backups/logiques # The timestamp to add at the end of each dump file PGBK_TIMESTAMP='%Y-%m-%d_%H-%M' # The time limit for old backups, in days PGBK_PURGE=2 # The minimum number of backups to keep when purging or 'all' to keep # everything (e.g. disable the purge) PGBK_PURGE_MIN_KEEP=0 # Command-line options for pg_dump PGBK_OPTS="-Fc -v" # List of databases to dump (separator is space) # If empty, dump all databases which are not templates #PGBK_DBLIST="db1 db2" # Exclude databases #PGBK_EXCLUDE # Include templates ("yes" or "no") PGBK_WITH_TEMPLATES="no" # Connection options #PGBK_HOSTNAME #PGBK_PORT #PGBK_USERNAME #PGBK_CONNDB=postgres ``` ### Sauvegardes Physiques Outil : pgbackrest #### Installation ``` warning pgbackrest périmé 1.12 suite migration fin 2023 ``` Pgbackrest 2.16 étant présent dans les dépôts communautaires, l'installation se fait simplement sur les deux serveurs : ``` sudo apt install pgbackrest ``` #### Localisation des fichiers /etc/pgbackrest.conf (sur les 2 serveurs) : Pour le compress-level-network=1, ~~Mantis:8925~~ ``` [global] repo-path=/mnt/backup_physique/PITR retention-full=2 retention-diff=6 process-max=8 log-path=/var/log/pgbackrest start-fast=y [global:archive-push] compress-level=1 [resadec] pg1-path=/var/lib/postgresql/9.6/bridoulou ``` Création du "stanza" : ``` postgres@foo-prod-1:~$ pgbackrest --stanza=resadec --log-level-console=info stanza-create 2018-01-17 14:18:08.136 P00 INFO: stanza-create command begin 1.27: --db1-path=/var/lib/postgresql/9.6/bridoulou --log-level-console=info --log-path=/var/log/pgbackrest --repo-path=/mnt/backup_physique/PITR --stanza=resadec 2018-01-17 14:18:08.514 P00 INFO: stanza-create command end: completed successfully ``` Une fois la configuration effectuée, il est possible de demander à pgbackrest de vérifier la configuration. Cette opération force la rotation d'un nouveau journal de transaction afin de s'assurer que l'archivage est bien opérationnel : ``` postgres@foo-prod-1:~$ pgbackrest --stanza=resadec --log-level-console=info check 2018-01-17 14:21:22.977 P00 INFO: check command begin 1.27: --db1-path=/var/lib/postgresql/9.6/bridoulou --log-level-console=info --log-path=/var/log/pgbackrest --repo-path=/mnt/backup_physique/PITR --stanza=resadec 2018-01-17 14:21:24.145 P00 INFO: WAL segment 00000001000000C400000085 successfully stored in the archive at '/mnt/backup_physique/PITR/archive/resadec/9.6-1/00000001000000C4/00000001000000C400000085-197b714780ade6369e472144213cfb53bd9fc1de.gz' 2018-01-17 14:21:24.145 P00 INFO: check command end: completed successfully ``` #### Configuration logrotate La configuration de logrotate a été adaptée afin d'avoir une rotation des logs. Pour cela le fichier ''/etc/logrotate.d/pgbackrest'' est (sur les 2 serveurs) : ``` /var/log/pgbackrest/*.log { rotate 30 daily compress delaycompress dateext create 0660 postgres postgres } ``` #### Planification La crontab de l'utilisateur postgres a été adaptée selon la politique de sauvegarde. La purge se fait automatiquement lors de la sauvegarde. Sur le serveur maître : ``` #pgbackrest 15 19 * * 0,3 pgbackrest --type=full --stanza=resadec --db-path=/var/lib/postgresql/9.6/bridoulou backup 15 19 * * 1,2,4,5,6 pgbackrest --type=diff --stanza=resadec --db-path=/var/lib/postgresql/9.6/bridoulou backup ``` Sur le serveur secondaire, ces lignes ont été ajoutées mais commentées (**à décommenter lors d'une bascule**). #### Sauvegarde complète Voici la commande pour une sauvegarde complète ``` pgbackrest --stanza=resadec --type=full --log-level-console=info backup 2018-01-17 14:39:07.542 P00 INFO: backup command begin 1.27: --db1-path=/var/lib/postgresql/9.6/bridoulou --log-level-console=info --log-path=/var/log/pgbackrest --process-max=4 --repo-path=/mnt/backup_physique/PITR --retention-diff=6 --retention-full=2 --stanza=resadec --start-fast --type=full 2018-01-17 14:39:08.308 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2018-01-17 14:39:07": backup begins after the requested immediate checkpoint completes 2018-01-17 14:39:08.508 P00 INFO: backup start archive = 00000001000000C400000093, lsn = C4/93000028 2018-01-17 14:39:33.162 P02 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/16384/656142.1 (1GB, 1%) checksum 7cdbbcecaa006eb61f0ede4079674ddbdd10f055 2018-01-17 14:39:34.418 P03 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/16384/656142 (1GB, 2%) checksum e59d6d2b3f9262238b05c9665974fd16f6da0418 2018-01-17 14:39:35.499 P04 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/16384/656141 (1GB, 3%) checksum c3a75f88dc4a6d1b311182f076ed49a46180fa7f [...] 2018-01-17 14:50:31.430 P03 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/1/12244 (0B, 100%) 2018-01-17 14:50:31.520 P00 INFO: full backup size = 80.1GB 2018-01-17 14:50:31.520 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive 2018-01-17 14:50:32.622 P00 INFO: backup stop archive = 00000001000000C40000009B, lsn = C4/9BB34A38 2018-01-17 14:50:37.223 P00 INFO: new backup label = 20180117-143907F 2018-01-17 14:50:46.418 P00 INFO: backup command end: completed successfully 2018-01-17 14:50:46.418 P00 INFO: expire command begin 1.27: --log-level-console=info --log-path=/var/log/pgbackrest --repo-path=/mnt/backup_physique/PITR --retention-archive=2 --retention-diff=6 --retention-full=2 --stanza=resadec 2018-01-17 14:50:46.430 P00 INFO: expire command end: completed successfully ``` Durée : 1h avec 4 processus en bz2 niveau 3, 2h avec bz2 niveau 9 (machines cloud 2023) #### Sauvegarde différentielle Voici la commande pour une sauvegarde différentielle : ``` pgbackrest --stanza=resadec --type=diff --log-level-console=info backup 2018-01-17 14:52:37.702 P00 INFO: backup command begin 1.27: --db1-path=/var/lib/postgresql/9.6/bridoulou --log-level-console=info --log-path=/var/log/pgbackrest --process-max=4 --repo-path=/mnt/backup_physique/PITR --retention-diff=6 --retention-full=2 --stanza=resadec --start-fast --type=diff 2018-01-17 14:52:38.672 P00 INFO: last backup label = 20180117-143907F, version = 1.27 2018-01-17 14:52:39.382 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2018-01-17 14:52:37": backup begins after the requested immediate checkpoint completes 2018-01-17 14:52:39.583 P00 INFO: backup start archive = 00000001000000C40000009D, lsn = C4/9D0000D0 2018-01-17 14:52:44.871 P04 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/16384/665041 (15.9MB, 7%) checksum b5aee3c61a6a33b85920d3e18f19472d40cc2022 2018-01-17 14:52:44.002 P02 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/16384/655443 (18.5MB, 16%) checksum bdbdf6e1244037f01d776f3bf9d8baf5e8b779ba 2018-01-17 14:52:45.205 P02 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/16384/1249 (9.8MB, 21%) checksum a97cddb68cde945d43228d1a2a2409fcd2d11d5e [...] 2018-01-17 14:52:45.708 P04 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/16384/665040_vm (0B, 49%) 2018-01-17 14:52:47.523 P01 INFO: backup file /var/lib/postgresql/9.6/bridoulou/base/16384/655655 (103.5MB, 100%) checksum 4a9031741fb8e21785f1c96afb2438b436b8a302 2018-01-17 14:52:47.678 P00 INFO: diff backup size = 203.5MB 2018-01-17 14:52:47.678 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive 2018-01-17 14:52:48.780 P00 INFO: backup stop archive = 00000001000000C40000009D, lsn = C4/9D0001D8 2018-01-17 14:52:49.996 P00 INFO: new backup label = 20180117-143907F_20180117-145237D 2018-01-17 14:52:57.709 P00 INFO: backup command end: completed successfully 2018-01-17 14:52:57.709 P00 INFO: expire command begin 1.27: --log-level-console=info --log-path=/var/log/pgbackrest --repo-path=/mnt/backup_physique/PITR --retention-archive=2 --retention-diff=6 --retention-full=2 --stanza=resadec 2018-01-17 14:52:57.722 P00 INFO: expire command end: completed successfully ``` #### Informations sauvegarde La commande suivante permet d'afficher des informations sur les sauvegardes. ``` postgres@foo-prod-1:~$ pgbackrest --stanza=resadec --log-level-console=info info stanza: resadec status: ok cipher: none db (current) wal archive min/max (9.6-1): 0000000100003A68000000FC/0000000100003BEA0000001E full backup: 20210131-191501F timestamp start/stop: 2021-01-31 19:15:01 / 2021-01-31 19:50:48 wal start/stop: 0000000100003A68000000FC / 0000000100003A6900000031 database size: 378.0GB, backup size: 378.0GB repository size: 63.7GB, repository backup size: 63.7GB diff backup: 20210131-191501F_20210211-191502D timestamp start/stop: 2021-02-11 19:15:02 / 2021-02-11 19:45:44 wal start/stop: 0000000100003B8C000000F3 / 0000000100003B8D00000016 database size: 393.0GB, backup size: 312.0GB repository size: 66.7GB, repository backup size: 53GB backup reference list: 20210131-191501F diff backup: 20210131-191501F_20210212-191501D timestamp start/stop: 2021-02-12 19:15:01 / 2021-02-12 19:46:07 wal start/stop: 0000000100003B9F0000006D / 0000000100003B9F00000090 database size: 393.3GB, backup size: 313.3GB repository size: 66.7GB, repository backup size: 53.1GB backup reference list: 20210131-191501F diff backup: 20210131-191501F_20210213-191501D timestamp start/stop: 2021-02-13 19:15:01 / 2021-02-13 19:45:20 wal start/stop: 0000000100003BAD00000074 / 0000000100003BAD000000A7 database size: 392.4GB, backup size: 312.4GB repository size: 66.6GB, repository backup size: 53GB backup reference list: 20210131-191501F full backup: 20210214-191501F timestamp start/stop: 2021-02-14 19:15:01 / 2021-02-14 19:53:52 wal start/stop: 0000000100003BBB0000009D / 0000000100003BBB000000CA database size: 392.5GB, backup size: 392.5GB repository size: 66.6GB, repository backup size: 66.6GB diff backup: 20210214-191501F_20210215-191501D timestamp start/stop: 2021-02-15 19:15:01 / 2021-02-15 19:43:58 wal start/stop: 0000000100003BC900000038 / 0000000100003BC90000005D database size: 393GB, backup size: 297GB repository size: 66.7GB, repository backup size: 50.6GB backup reference list: 20210214-191501F diff backup: 20210214-191501F_20210216-191501D timestamp start/stop: 2021-02-16 19:15:01 / 2021-02-16 19:44:35 wal start/stop: 0000000100003BD9000000D8 / 0000000100003BD9000000FB database size: 393.3GB, backup size: 303.6GB repository size: 66.8GB, repository backup size: 51.8GB backup reference list: 20210214-191501F ``` ### Restauration Par défaut, pgbackrest ne restaure pas les liens symboliques. Pour les restaurer, il faut utiliser l'option : ''--link-all''. Stopper l'instance, vider le répertoire contenant l'instance (sans oublier le répertoire contenant les journaux de transaction en cas de lien symbolique) puis lancer la commande : ``` pgbackrest --stanza=resadec --log-level-console=info restore ``` En spécifiant le `set` et la `target`, on peut faire une restauration PITR. L'instance démarrera en pause et il faudra faire un "select pg_wal_replay_resume();" pour finir le recovery. Dans cet exemple on restaure l'instance en demandant au moteur de stopper le rejeu des journaux de transaction à la date : "2018-01-16 09:00:00" ``` pgbackrest --stanza=resadec --log-level-console=info --delta --set=20180115-152209F_20180115-155913D --type=time --target="2018-01-16 09:00:00" restore 2018-01-16 10:00:31.933 P00 INFO: restore command begin 1.27: --db1-path=/var/lib/postgresql/10/main --delta --log-level-console=info --log-path=/var/log/pgbackrest --process-max=4 --repo-path=/var/lib/pgbackrest --set=20180115-152209F_20180115-155913D --stanza=resadec "--target=2018-01-16 09:00:00" --type=time 2018-01-16 10:00:32.032 P00 INFO: restore backup set 20180115-152209F_20180115-155913D 2018-01-16 10:00:32.370 P00 INFO: remove invalid files/paths/links from /var/lib/postgresql/10/main 2018-01-16 10:00:32.796 P00 INFO: cleanup removed 349 files, 1 path 2018-01-16 10:00:34.084 P04 INFO: restore file /var/lib/postgresql/10/main/global/2672 (16KB, 17%) checksum 13af98bb08badc95cf1caebd69aee162983b2654 2018-01-16 10:00:34.085 P03 INFO: restore file /var/lib/postgresql/10/main/global/2671 (16KB, 17%) checksum 3a4c842d99a46d80c226e9053bc471a25833c52b 2018-01-16 10:00:34.262 P03 INFO: restore file /var/lib/postgresql/10/main/pg_xact/0000 (8KB, 34%) checksum e6c4bafb79c553feec1d57cdd5caee32bc3436f6 2018-01-16 10:00:34.263 P02 INFO: restore file /var/lib/postgresql/10/main/global/pg_control.pgbackrest.tmp (8KB, 34%) checksum e21aa133dec0a187aa455b73bcb23d1c76d28b6f 2018-01-16 10:00:34.263 P04 INFO: restore file /var/lib/postgresql/10/main/global/1262 (8KB, 34%) checksum a485ebb4a3914726586d416f526e12f47d826fd9 2018-01-16 10:00:34.496 P03 INFO: restore file /var/lib/postgresql/10/main/backup_label (231B, 34%) checksum 84a528fd70a5e664e42fc9db8948175acbff2040 2018-01-16 10:00:35.889 P00 INFO: write /var/lib/postgresql/10/main/recovery.conf 2018-01-16 10:00:36.022 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2018-01-16 10:00:36.233 P00 INFO: restore command end: completed successfully ``` ## Mise à jour mineure PostgreSQL Suivre la procédure suivante: