Où sont mes logs ?

Par défaut les logs PostgreSQL sont redirigés sur sderr. Mais ça ne veut pas dire qu'ils sont perdus!

log_destination

Il faut commencer par chercher où sont envoyés les logs. Si vous n'avez touché à rien, ils sont envoyés sur sderr:

cat /etc/postgresql/9.6/main/postgresql.conf | grep log_destination
#log_destination = 'stderr'        # Valid values are combinations of

Si ce n'est pas sderr, il faudra attendre que je fasse un autre article (ou lire la documentation officielle)... Désolée...

log_collector 

Dans le cas où le log_destination est à sderr, il faut absolument que le log_collector soit à 'on'. Dans le cas contraire, les logs redirigés vers stderr seront perdus sauf si la commande de démarrage de postgres inclue une redirection explicite de sderr.

cat /etc/postgresql/9.6/main/postgresql.conf | grep collector
                                                       # requires logging_collector to be on.
logging_collector = on                                 # Enable capturing of stderr and csvlog

 log_directory

 Enfin, le paramètre ultime qui indique où sont redirigés les logs est log_directory.

cat /etc/postgresql/9.6/main/postgresql.conf | grep log_directory

log_directory = '/var/lib/postgresql/pg_log'           # directory where log files are written,

Pour en savoir plus

La documentation officielle prévoit une page complète sur le reporting d'erreur et le logging.
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html

Quel est le nom du WAL courant ?

Effectivement, c'est pas forcément utile au commun des mortels, mais on ne sait jamais...

WAL

Les WAL sous PostgreSQL (Write Ahead Log) sont le mécanisme qui assure la durabilité des données (le D dans ACID), tout en permettant de meilleures performances que si on écrivait directement dans les fichiers de données à chaque modification.
Les données sont d'abord écrites en mémoire. Au commit, elles sont écrites dans les WAL. Le checkpoint permet ensuite d'écrire les données dans les fichiers de données.

Le nom des WAL

Les WAL ont des petits noms forts sympathiques composés de 25 caractères (composés des id de timeline, block ID et segment ID, mais qui a besoin de descendre à ce niveau de détail?). Le premier est le 000000010000000000000000 et ça s'incrémente.

Le WAL courant

Ce que j'appelle le WAL courant est le WAL dans lequel PostgreSQL est en train d'écrire la transaction courante.
Une fonction permet de récupérer le numéro de la transaction courante. Une autre permet de convertir ce numéro en nom de WAL. Il faut donc combiner les deux:

SELECT pg_xlogfile_name(pg_current_xlog_location());
 pg_xlogfile_name 
--------------------------
 0000000A0000000000000077
(1 row)

Pour en savoir plus

Toute la documentation de ses fonctions et de leurs petites sœurs est disponible ici: https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE

Restaurer avec pgBackRest

C'est très bien de savoir faire une sauvegarde avec pgBackRest, mais encore faut-il savoir restaurer cette sauvegarde.

Restaure en mode delta

Ce mode très pratique permet à pgBackRest de comparer et ne restaurer que les fichiers pour lesquels c'est vraiment nécessaire.

sudo -u postgres pgbackrest --stanza=main --delta --log-level-console=detail restore
2017-02-21 09:58:59.528 P00   INFO: restore command begin 1.15: --db-path=/var/lib/postgresql/9.6/main --delta --log-level-console=detail --repo-path=/var/lib/pgbackrest --stanza=main
2017-02-21 09:58:59.568 P00   INFO: restore backup set 20170217-145055F_20170221-095547D
2017-02-21 09:58:59.725 P00 DETAIL: check /var/lib/postgresql/9.6/main exists
2017-02-21 09:58:59.802 P00   INFO: remove invalid files/paths/links from /var/lib/postgresql/9.6/main
2017-02-21 09:58:59.877 P00 DETAIL: remove file /var/lib/postgresql/9.6/main/recovery.done
2017-02-21 09:58:59.877 P00 DETAIL: remove file /var/lib/postgresql/9.6/main/postmaster.opts
2017-02-21 09:58:59.878 P00 DETAIL: remove file /var/lib/postgresql/9.6/main/pg_xlog/archive_status/00000006000000000000006B.done
...
2017-02-21 09:58:59.927 P00   INFO: cleanup removed 17 files, 1 path
2017-02-21 09:59:00.622 P01 DETAIL: restore file /var/lib/postgresql/9.6/main/base/16385/16405 - exists and matches backup (153.4MB, 33%) checksum ceb4e6230607e7973927ed3e8528671551ad8a3b
2017-02-21 09:59:00.830 P01 DETAIL: restore file /var/lib/postgresql/9.6/main/base/16385/16420 - exists and matches backup (85.6MB, 52%) checksum 6c5fe798c54ae1922f141a43e62cd325abf74458
...
2017-02-21 09:59:01.676 P01   INFO: restore file /var/lib/postgresql/9.6/main/base/12448/3609 (32KB, 97%) checksum 45ac781f938a83b267cf360f927f400fb87fc080
2017-02-21 09:59:01.681 P01   INFO: restore file /var/lib/postgresql/9.6/main/base/12448/3455 (32KB, 97%) checksum f5a1bc35de91152a60934d394512454b1f5545cc
2017-02-21 09:59:01.685 P01   INFO: restore file /var/lib/postgresql/9.6/main/base/12448/2757 (32KB, 97%) checksum 7b8f27284382bc310e32cb8e9f4b62c698931f51
...
2017-02-21 09:59:04.858 P00   INFO: write /var/lib/postgresql/9.6/main/recovery.conf
2017-02-21 09:59:04.956 P00   INFO: restore global/pg_control (copied last to ensure aborted restores cannot be started)
2017-02-21 09:59:04.960 P00   INFO: restore command end: completed successfully 

Faire une restauration complète

Si on souhaite faire une restauration complète, il faut que le répertoire $PGDATA n'existe pas.

sudo -u postgres pgbackrest --stanza=main  --log-level-console=detail restore2017-02-21 10:17:58.702 P00   INFO: restore command begin 1.15: --db-path=/var/lib/postgresql/9.6/main --log-level-console=detail --repo-path=/var/lib/pgbackrest --stanza=main
2017-02-21 10:17:58.742 P00   INFO: restore backup set 20170217-145055F_20170221-095547D
2017-02-21 10:17:58.894 P00 DETAIL: check /var/lib/postgresql/9.6/main exists
2017-02-21 10:18:00.578 P01   INFO: restore file /var/lib/postgresql/9.6/main/base/16385/16405 (153.4MB, 33%) checksum ceb4e6230607e7973927ed3e8528671551ad8a3b
...
2017-02-21 10:18:10.571 P00   INFO: write /var/lib/postgresql/9.6/main/recovery.conf
2017-02-21 10:18:10.670 P00   INFO: restore global/pg_control (copied last to ensure aborted restores cannot be started)
2017-02-21 10:18:10.675 P00   INFO: restore command end: completed successfully 

Il faudra peut-être remettre postgres:postgres comme propriétaire de $PGDATA avec des droits 700... Et ça redémarre!

Faire du PITR

Pour faire du PITR (Point In Time Recovery), il suffit de modifier le fichier recovery.conf. PgBackRest propose de le faire pour vous

sudo -u postgres pgbackrest --stanza=main --delta --type=time "--target=2017-02-20 11:20:00" restore
sudo -u postgres cat /var/lib/postgresql/9.6/main/recovery.conf
restore_command = '/usr/bin/pgbackrest --stanza=main archive-get %f "%p"'
recovery_target_time = '2017-02-20 11:20:00'
sudo pg_ctlcluster 9.6 main start
Redirecting start request to systemctl

Pour en savoir plus

Toute la documentation est disponible sur le site de pgBackRest.

• La restauration
• Référence des commandes

Faire une sauvegarde avec pgBackRest

Comme je l'ai expliqué dans mes articles, on peut utiliser pgbasebackup pout faire les sauvegardes et on peut faire du PITR à la main, cependant, on peut aussi chercher des outils un peu plus évolués pour se faciliter la tâche.
J'ai testé pgBackRest.
Bon d'accord, "l'emballage" de l'outil n'est pas sexy... On pourrait lui reprocher un site propre mais pas très attrayant. Ça manque de marketing. Mais quand on a passé cette barrière, on se rend compte que l'outil est assez facile à prendre en main et rend de beaux services.

Installation

Il n'existe pas de package (Linux) ni d'installeur (Windows) pour pgBackRest. L'outil est écrit en perl. C'est un "plus" pour moi, un "moins" pour beaucoup d'autres et un point neutre pour la grande majorité.
La procédure est très bien indiquée dans la documentation utilisateur http://www.pgbackrest.org/user-guide.html#installation.
Pour les users Windows, je pense que ça doit marcher, il faudra juste installer un émulateur comme cygwin ou celui fourni avec Windows 10.

Configuration

Oui, on trépigne d'impatience, mais avant de pouvoir lancer une sauvegarde, il va falloir configurer la bête.

Stanza ou Stance

Tout d'abord, un peu de vocabulaire. PgBackRest appelle "Stanza" la configuration d'un groupe de bases de données (database cluster) à sauvegarder, qu'on pourrait traduire par stance qui signifie, en poésie, "un nombre défini de vers comprenant un sens parfait et arrangé d’une manière particulière qui s’observe dans tout le poème" (source Wikipedia).
Une stance comporte tous les paramètres de sauvegarde d'un cluster de bases de données.

Création/paramétrage du stockage

Le répertoire de stockage doit avoir pour user postgres:postgres avec des droits 750. Pour la suite ce répertoire s'appellera /mon/repertoire/de/stockage/de/backup.

Configuration d'une stance

Le fichier de configuration de stance est /etc/pgbackrest.conf. Le user postgres doit avoir les droits de lecture sur le fichier.

Il doit comporter une section [global] valable pour toutes les stances et une section par stance avec le nom du cluster entre crochets en début de section.
La section [global] doit comporter le chemin vers le répertoire de sauvegarde et éventuellement la stratégie de rétention des sauvegardes.
Le tutoriel propose de mettre une rétention à 2 backups full.

Voilà la tête de mon fichier de configuration:

[global]
repo-path=/mon/repertoire/de/stockage/de/backup
retention-full=2

[main]
db-path=/var/lib/postgresql/9.6/main

Création d'une stance

Il faut ensuite dire à pgBackRest qu'il y a une nouvelle stance. L'option

sudo -u postgres pgbackrest --stanza=main --log-level-console=info stanza-create 
2017-02-17 14:28:08.486 P00   INFO: stanza-create command begin 1.15: --db-path=/var/lib/postgresql/9.6/main --log-level-console=info --repo-path=/var/lib/pgbackrest --stanza=main
2017-02-17 14:28:08.872 P00   INFO: stanza-create command end: completed successfully

Configurer l'archivage

Il faut configurer l'archivage des WAL avec pgBackRest et un niveau de détail suffisant dans les WAL pour pouvoir faire du PITR.
Voici les paramètres à modifier:

archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_mode = on
listen_addresses = '*'
log_line_prefix = ''
max_wal_senders = 3
wal_level = logical

J'avais déjà configuré mon wal_level à logical. La documentation de pgBackRest recommande d'augmenter max_wal_level pour pouvoir par la suite ajouter une standby sans redémarrer le maître...Sous PostgreSQL 10, la valeur par défaut devrait être 10.
Un redémarrage est nécessaire après la modification de ces paramètres.

Vérification

Une petite vérification que tout est OK:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info check
2017-02-19 22:21:14.472 P00   INFO: check command begin 1.15: --db-path=/var/lib/postgresql/9.6/main --log-level-console=info --repo-path=/var/lib/pgbackrest --stanza=main
2017-02-19 22:21:15.678 P00   INFO: WAL segment 000000060000000000000055 successfully stored in the archive at '/var/lib/pgbackrest/archive/main/9.6-1/0000000600000000/000000060000000000000055-4c059aaad9851886721312972a427d0e72c3543d.gz'
2017-02-19 22:21:15.680 P00   INFO: check command end: completed successfully

 Sauvegarder

Ça y est, on va pouvoir faire une sauvegarde.

Sauvegarde complète

Par défaut, pgBackRest fait une sauvegarde complète. Il suffit de lancer la commande avec l'option backup :

sudo -u postgres pgbackrest --stanza=main --log-level-console=info backup
2017-02-19 22:23:35.793 P00   INFO: backup command begin 1.15: --db-path=/var/lib/postgresql/9.6/main --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=main
2017-02-19 22:23:36.005 P00   INFO: last backup label = 20170217-145055F_20170219-221932I, version = 1.15
2017-02-19 22:23:36.717 P00   INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2017-02-19 22:23:35": backup begins after the next regular checkpoint completes
2017-02-19 22:23:37.018 P00   INFO: backup start archive = 000000060000000000000057, lsn = 0/57000028
2017-02-19 22:23:38.510 P01   INFO: backup file /var/lib/postgresql/9.6/main/global/pg_control (8KB, 99%) checksum 4b8c6eb02288dcf371d1db9e5811e4540b298692
2017-02-19 22:23:38.517 P01   INFO: backup file /var/lib/postgresql/9.6/main/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2017-02-19 22:23:38.538 P00   INFO: incr backup size = 8KB
2017-02-19 22:23:38.538 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2017-02-19 22:23:39.642 P00   INFO: backup stop archive = 000000060000000000000057, lsn = 0/57000130
2017-02-19 22:23:39.809 P00   INFO: new backup label = 20170217-145055F_20170219-222339I
2017-02-19 22:23:39.839 P00   INFO: backup command end: completed successfully
2017-02-19 22:23:39.840 P00   INFO: expire command begin 1.15: --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=main
2017-02-19 22:23:39.854 P00   INFO: full backup total < 2 - using oldest full backup for archive retention
2017-02-19 22:23:39.855 P00   INFO: expire command end: completed successfully

On voit qu'en fin de sauvegarde, il vérifie l'âge des sauvegardes et la durée de rétention pour savoir si des backups sont expirés.

 Sauvegarde différentielle

Pour faire une sauvegarde différentielle, il suffit d'ajouter l'option --type=diff:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info --type=diff backup
2017-02-19 22:25:42.572 P00   INFO: backup command begin 1.15: --db-path=/var/lib/postgresql/9.6/main --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=main --type=diff
2017-02-19 22:25:42.757 P00   INFO: last backup label = 20170217-145055F, version = 1.15
2017-02-19 22:25:43.471 P00   INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2017-02-19 22:25:42": backup begins after the next regular checkpoint completes
2017-02-19 22:25:43.672 P00   INFO: backup start archive = 000000060000000000000059, lsn = 0/59000028
2017-02-19 22:25:44.522 P01   INFO: backup file /var/lib/postgresql/9.6/main/base/12449/pg_internal.init (110KB, 69%) checksum 03674f79eeaded1a0a69dbd044e5b40c5728726b
2017-02-19 22:25:44.526 P01   INFO: backup file /var/lib/postgresql/9.6/main/global/pg_internal.init (16.2KB, 79%) checksum a1d214a73f88d430b87d13ee6bc674d7e97f7da7
2017-02-19 22:25:44.530 P01   INFO: backup file /var/lib/postgresql/9.6/main/pg_notify/0000 (8KB, 84%) checksum 0631457264ff7f8d5fb1edc2c0211992a67c73e6
2017-02-19 22:25:44.537 P01   INFO: backup file /var/lib/postgresql/9.6/main/pg_multixact/offsets/0000 (8KB, 89%) checksum 0631457264ff7f8d5fb1edc2c0211992a67c73e6
2017-02-19 22:25:44.543 P01   INFO: backup file /var/lib/postgresql/9.6/main/pg_clog/0000 (8KB, 94%) checksum b075d6fc4b4ae9e4a0576a187b0f59d1f13fc457
2017-02-19 22:25:44.546 P01   INFO: backup file /var/lib/postgresql/9.6/main/global/pg_control (8KB, 99%) checksum f577e11ae0d7fb912bb1167f6a21815fd32ddab6
2017-02-19 22:25:44.550 P01   INFO: backup file /var/lib/postgresql/9.6/main/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2017-02-19 22:25:44.569 P00   INFO: diff backup size = 158.3KB
2017-02-19 22:25:44.569 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2017-02-19 22:25:45.673 P00   INFO: backup stop archive = 000000060000000000000059, lsn = 0/590000F8
2017-02-19 22:25:45.842 P00   INFO: new backup label = 20170217-145055F_20170219-222545D
2017-02-19 22:25:45.877 P00   INFO: backup command end: completed successfully
2017-02-19 22:25:45.878 P00   INFO: expire command begin 1.15: --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=main
2017-02-19 22:25:45.891 P00   INFO: full backup total < 2 - using oldest full backup for archive retention
2017-02-19 22:25:45.892 P00   INFO: expire command end: completed successfully

 Pour en savoir plus

Vous trouverez toutes les documentations sur http://www.pgbackrest.org.
Voici une liste de liens spécifiques:

• Installation
• Tutoriel de démarrage 
• Référence des commandes

Faire du PITR à la main

Dans l'article sur pg_basebackup, j'ai montré comment réaliser une sauvegarde à chaud d'une base PostgreSQL. C'est très bien de faire une sauvegarde mais si on est incapable de la restaurer, il n'y a pas d'intérêt.

La procédure

La procédure pour faire une restauration est assez simple (dans la doc ils ajoutent des étapes non nécessaire sur ma base à but pédagogique, comme modifier pg_hba.conf pour ne plus autoriser les connexions des users):

1. Arrêter l'instance
2. Déplacer les données du cluster PGDATA et tous les tablespaces dans un lieu de confiance (oui, la suppression dans ce cas, n'est peut-être pas le bon choix)
3. Détarrer la sauvegarde et copier les fichiers/répertoires à leur bon emplacement (ce qui peut devenir très pénible si on a différents tablespaces sur des disques différents)
4. Créer un fichier recovery.conf
5. Redémarrer l'instance

Le fichier recovery.conf

On peut trouver un fichier exemple sur le github (ici).

restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2017-02-15 15:27:53 CET'

Un petit redémarrage et taadaa! Ça marche!

Pour en savoir plus

La doc PostgreSQL décrit bien les différentes étapes ici: https://www.postgresql.org/docs/9.6/static/backup.html

Faire parler postgres en anglais

Il peut être intéressant d'avoir les messages de postgres en anglais. En effet, il arrive que la traduction ne soit pas à la hauteur. De plus, il est plus facile de trouver des réponses avec des messages d'erreur en anglais qu'avec des messages d'erreur en français.

Modification de la locale

Le plus simple est de modifier la variable d'environnement LC_ALL. Il suffit de la setter à 'C'.
C'est simple, mais un peu "bourrin"...

Modification dans postgresql.conf

Normalement, le paramètre lc_messages de postgresql.conf devrait suffire pour mettre les messages du serveur en anglais. Cependant, les messages du client resteront en français...
On peut aussi tester la modification de la variable d'environnement LC_MESSAGES, mais là encore, j'ai eu un mix de messages français/anglais.

Pour en savoir plus

La documentation officielle consacre tout un chapitre à cette problématique: https://www.postgresql.org/docs/9.6/static/locale.html

Utiliser pg_basebackup pour faire les sauvegardes de son instance

Pg_basebackup est l'utilitaire permettant de faire de vraies sauvegardes physiques de votre base de données. Si une sauvegarde logique seulement est nécessaire, pg_dump est là pour vous (et permettez moi de vous dire que vous avez une toute petite base :-) ). Sa mise en place nécessite de comprendre quelques notions de base.

Sauvegarde à chaud

pg_basebackup fait des sauvegardes à chaud, donc les sauvegardes des données ne sont pas suffisantes, il va falloir prendre en compte la portion de WAL écrite entre le début et la fin de la sauvegarde. De plus, cela permet de plus de faire du PITR (Point In Time Recovery) (si l'archivage est mis en place).
L'instance va voir la sauvegarde comme une réplication. Il faut donc procéder à  une modification du paramétrage de l'instance.

Paramétrage de l'instance

Il faut donc commencer par paramétrer l'instance. Attention, la modification de ce paramétrage nécessitera un redémarrage de l'instance.
Les paramètres à modifier sont:

• max_wal_sender: Par défaut ce paramètre vaut 0. pg_basebackup agit comme une standby qui récupère les WAL. Il faut positionner ce paramètre à nombre de standby+1 pour pouvoir faire une sauvegarde
• wal_level: si vous n'avez pas de souci de place, mettez ce paramètre à logical (on ne sait jamais, ça pourrait servir un jour). Le niveau minimum pour utiliser pg_basebackup est replica.
• archive_mode: cette partie est facultative, mais nécessaire au PITR. On peut donc setter ce paramètre à on.
• archive_command: cette partie est facultative,  mais nécessaire au PITR. Je vous laisse mettre la commande de votre choix.

Vous pouvez aussi setter d'autres paramètres liés aux WAL. Ce n'est pas nécessaire à pg_basebackup, mais tant qu'à activer les WAL, autant tuner le truc au mieux pour votre instance. Vous pouvez donc regarder archive_timeout, max_wal_size et min_wal_size.

Vous pouvez redémarrez votre instance.

Paramétrage de connexion

Il faut paramétrer pg_hba.conf comme si on avait une réplication qui se connectait à l'instance. C'est indiqué sur cette page.

The backup is made over a regular PostgreSQL connection, and uses the replication protocol. The connection must be made with a superuser or a user having REPLICATION permissions and pg_hba.conf must explicitly permit the replication connection.

J'ai paramétré ma connexion ainsi (attention il s'agit d'une simple base locale à but purement pédagogique. Pensez de votre côté à prendre en compte l'aspect sécurité, notamment sur la méthode d'authentification).

 host    replication    postgres    127.0.0.1/32    trust

Les messages d'erreur

Voici un petit florilège des messages d'erreur que vous pouvez rencontrer:

• pg_basebackup: directory "/my/backup/path/dir" exists but is not empty: pg_basebackup ne fait des sauvegardes que dans des répertoires vides. A vous de déplacer les backup sur votre système de stockage favori avant de faire un autre backup (ou de changer de répertoire).
• pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL on: Vous n'avez pas configué la connexion dans pg_hba.conf.
• pg_basebackup: FATAL: number of requested standby connections exceeds max_wal_senders: Vous n'avez pas correctement configuré max_wal_senders
• pg_basebackup: FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "replica" or "logical": Vous n'avez pas correctement configuré wal_level
• pg_basebackup: NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup: Vous n'avez pas correctement configuré archive_mode et/ou archive_command

 Lancer la sauvegarde

Je vous conseille de lancer la sauvegarde avec les options -Ft (pour obtenir un fichier tar) et -z pour compresser le backup à la volée.
Voilà à quoi ressemble ma commande:

pg_basebackup -h localhost -D /my/backup/path/dir -Ft -z -P

 Le -P est optionnel. Il permet d'avoir une progression de la commande de backup. C'est totalement inutile dans un batch.

Pour en savoir plus

Vous pouvez vous plonger dans la doc: https://www.postgresql.org/docs/9.6/static/app-pgbasebackup.html