Mes petits carnets de DBA: 2017

Les modes d'archivage

Si vous êtes un peu sérieux avec la durabilité de vos données, vous avez déjà activé le mode archive de vos WALs. Je l'explique assez souvent: dans le chemin d'écriture d'une donnée dans les fichiers de données, les WALs jouent un rôle critique et en garder une copie est une bonne idée.
En plus de peut-être vous sauver la vie un jour, cela permet également d'utiliser des fonctionnalités comme:

Les sauvegardes physiques à chaud
La réplication
Le Point In Time Recovery

Dans cet article, je vais parler du paramètre archive_mode.

Archive_mode

Les valeurs les plus utilisées de ce paramètre sont 'off' (valeur par défaut) et 'on'. Cependant, si on regarde attentivement la documentation, on voit qu'il existe une valeur 'always' pour ce paramètre.

Lorsque le paramètre est valorisé à 'off', les WAL ne sont pas archivés. Une fois qu'ils ne sont plus utiles, ils ne sont pas copiés alors et lorsqu'on aura besoin de place, on les recyclera. C'est la valeur par défaut et, encore une fois, je vous conseille de ne pas utiliser cette valeur.

Lorsque le paramètre est valorisé à 'on', les WAL sont archivés lors du fonctionnement normal de l'instance (c'est à dire lorsque des requêtes envoyées par les clients modifient la structure ou les données).

La dernière valeur (qui est peu utilisée), 'always', permet de créer des WAL y compris lors des opérations de recovery. En effet, lorsqu'une instance est en recovery, elle modifie la structure et les données des bases de données.

On pourrait considérer qu'étant donné qu'on a les WAL qui ont permis le recovery, il n'est pas nécessaire de créer des nouveaux WAL. Cependant, si nous nous plaçons dans le cas d'une streaming replication, les blocs sont streamés directement et pour éviter d'envoyer à nouveau ces informations à la standby via les WALs complets (avec archive_command ou pg_receivexlog), il est possible de demander à la standby de créer ses propres WAL. Cela permet d'économiser un peu la bande passante.

Pour en savoir plus

Cette fonctionnalité est disponible depuis la version 9.5.
Vous trouverez la documentation officielle ici: https://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-ARCHIVE-MODE

Qui fait quoi sur mon instance ?

C'est une question assez fréquente chez les DBAs : voir les connexions actuelles sur l'instance et ce qu'elles font.
Ça permet de s'assurer que machin qui dit qu'il n'est pas connecté est vraiment déconnecté, que truc qui assure être connecté sur la bonne instance est vraiment connecté et aussi de voir l'activité actuelle sur l'instance.

La bonne nouvelle, c'est que la solution tient en une vue : pg_stat_activity.

Une simple requête permet donc d'avoir une bonne vue de l'activité de l'instance:

SELECT * FROM pg_stat_activity;

Pour en savoir plus

La documentation de la vue est accessible via psql ou sur la documentation officielle : https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Tester une connexion de type "replication"

Quand on veut mettre en place des sauvegardes physiques, on utilise un outil qui se connecte à l'instance en se faisant passer pour un nœud secondaire de réplication. C'est pour cette raison qu'il faut définir une connexion de type réplication dans pg_hba.conf (voir ici).
Mais comment peut-on tester cette connexion ?

C'est indiqué dans cette page de la documentation.

Il suffit donc de faire:

psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"

Attention, en version 9.3 (quoi, vous n'avez toujours pas migré?), il faudra utiliser cette syntaxe:

psql "dbname=postgres replication=true" -c "IDENTIFY_SYSTEM;"

Dropper les objets d'un user

Sous Oracle, un user possède son propre schéma dans lequel il peut stocker ses objets (principalement des vues et des fonctions/procédures stockées). Certains gèrent les droits ainsi. La suppression du user supprime alors les objets en cascade.
Sous PostgreSQL, il n'est pas possible de supprimer un rôle qui est encore propriétaire d'objets.

Suppression de tous les objets

Il est possible de supprimer tous les objets d'un user (par base de données) en faisant cette simple commande:

DROP OWNED BY doomed_role;

Changement de propriétaire pour tous les objets

On peut aussi vouloir conserver les objets dont le rôle est propriétaire tout en supprimant le rôle.

REASSIGN OWNED BY doomed_role TO successor_role;

Suppression d'objets particuliers

Si on souhaite supprimer toutes les fonctions, toutes les vues ou toutes les tables possédées par un user tout en conservant les autres objets, il va falloir utiliser un bout de code.

Les fonctions

Il est possible de supprimer toutes les fonctions détenues par un user en utilisant ce code:

create function drop_functions_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s(%s)', 
            nspname, proname, pg_get_function_identity_arguments(p.oid))
        from pg_proc p
        join pg_namespace n on n.oid = pronamespace
        where p.proowner = $1
    loop
        if do_drop then
            execute format('drop function %s', r);
        end if;
        return next r;
    end loop;
end $$;

Les vues

Pour les vues détenues par un user, c'est cette fonction qu'on utilisera:

create function drop_views_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s', nspname, relname)
        from pg_class c
        join pg_namespace n on n.oid = relnamespace
        where c.relowner = $1
        and relkind = 'v'
    loop
        if do_drop then
            execute format('drop view %s', r);
        end if;
        return next r;
    end loop;
end $$;

Les tables

Il suffit d'adapter le code du dessus:

create function drop_views_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s', nspname, relname)
        from pg_class c
        join pg_namespace n on n.oid = relnamespace
        where c.relowner = $1
        and relkind = 'r'
    loop
        if do_drop then
            execute format('drop table %s cascade', r);
        end if;
        return next r;
    end loop;
end $$;

Pour en savoir plus

La documentation prévoit une page sur la suppression des rôles : https://www.postgresql.org/docs/current/static/role-removal.html

Où sont mes logs (la suite) ?

Dans l'article Où sont mes logs je vous avais indiqué comment trouver les logs d'une instance PostgreSQL qui utilise le log collector (paramètre log_collector =on).
Mais que se passe-t-il si le log collector n'est pas activé ?

Stderr

Restons dans le cas par défaut où les logs sont envoyés sur stderr. Il s'agit du cas par défaut car la valeur par défaut du paramètre log_destination est stderr.
Ce n'est plus forcément la meilleure valeur, mais j'espère avoir le temps d'un autre article pour parler de ça...

Pid

Pour pouvoir trouver où sont vos logs, vous allez devoir récupérer le pid de votre process postgres principal.
Voici deux manières de le faire:

Comme un ingénieur système

ps -ef | grep postgres
postgres 5109 1 0 16:25 ? 00:00:00 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
postgres 5116 5109 0 16:25 ? 00:00:00 postgres: checkpointer process 
postgres 5117 5109 0 16:25 ? 00:00:00 postgres: writer process 
postgres 5134 5109 0 16:25 ? 00:00:00 postgres: wal writer process 
postgres 5135 5109 0 16:25 ? 00:00:00 postgres: autovacuum launcher process 
postgres 5136 5109 0 16:25 ? 00:00:00 postgres: archiver process  last was 00000008.history
postgres 5137 5109 0 16:25 ? 00:00:00 postgres: stats collector process

Comme un DBA

SELECT pg_backend_pid();
pg_backend_pid
----------------
5109
(1 row)

Trouver les logs

Une fois le PID récupéré, vous trouverez les logs dans le répertoire /proc/<PID>/fd/2.
C'est là que vous verrez éventuellement si la sortie d'erreur est redirigée.
Par exemple sur un postgreSQL lancé avec le wrapper perl pg_ctlcluster, vous trouverez ceci:

sudo ls -l /proc/5109/fd/2
l-wx------ 1 postgres postgres 64 May 10 18:35 /proc/5109/fd/2 -> /var/log/postgresql/postgresql-9.6-main.log

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

Passer des options à pg_createcluster

J'adore les wrappers perl intégrés dans les distributions Debian (ou Debian-based). Mais il n'est pas toujours aisé de donner les paramètres que l'on souhaite.
J'ai voulu utiliser pg_createcluster en ajoutant l'option permettant d'avoir un checksum des données sur chaque page.

Createcluster.conf

Le fichier /etc/postgresql-common/createcluster.conf permet de spécifier ce paramètre dans la section:

initdb_options = '--data-checksums'

Si on ne souhaite pas modifier le fichier de paramètre, il est possible de spécifier ce paramètre dans la ligne de commande.

Dans la ligne de commande

Il est possible aussi de spécifier ce paramètre directement dans la ligne de commande ainsi:

sudo pg_createcluster 9.6 main -- '--data-checksums'

Vérifier

On peut ensuite vérifier que le paramètre a bien été pris en compte:

postgres=# show data_checksums;
data_checksums
----------------
on
(1 row)

Pour en savoir plus

Vous pouvez toujours lire la page man de pg_createcluster ou aller voir le code du wrapper.

Les surprises de pg_upgradecluster

J'aime beaucoup les wrappers perl qu'on trouve dans les distributions Debian pour gérer postgreSQL. Il n'empêche que j'ai rencontré un comportement bizarre l'autre jour chez un client.
Nous voulions utiliser l'option --link (ou -k) de pg_upgrade pour faire un upgrade plus rapide de notre base de données.
Pour tous les autres wrappers, il suffit de fournir l'option de l'outil postgreSQL au wrapper et le tour est joué. C'est ce que nous avons fait mais, là, les temps étaient très très longs.

Après avoir regardé la doc et le code, nous avons découvert que pg_upgradecluster utilise par défaut pg_dump pour faire un upgrade. Notre option -k n'était donc pas prise en compte...

Manquant de temps, nous avons donc fait l'upgrade à la main (création du nouveau cluster, arrêt des deux instances, lancement de pg_upgrade avec l'option -k).

Depuis, j'ai pu regarder pg_upgradecluster et j'ai pu confirmer que la ligne suivante fonctionne.

pg_upgradecluster -v 9.6 9.5 main -m upgrade -k

Voici la procédure sous Debian pour faire un upgrade (on peut utiliser l'option -m dump pour faire des sauts de version majeure).

Installation de la nouvelle version

sudo apt-get install postgresql-9.6

Suppression du cluster main

sudo pg_dropcluster 9.6 main

Lancement de l'upgrade

sudo pg_upgradecluste -v 9.6 9.5 main -m upgrade

Pour en savoir plus :
La documentation de pg_upgrade est là.
Le code source de pg_upgradecluster est ici (et n'hésitez pas à regarder la man page).

Les extensions

PostgreSQL est un SGBD extensible et il a beaucoup d'extensions. Comment ça marche ?

Ajouter une extension

On peut ajouter une extension en jouant l'ordre DDL suivant:

CREATE EXTENSION extension_name

(Il y a des chance qu'il faille installer l'extension sur le serveur avant, suivez bien les instructions du manuel de l'extension).

Supprimer une extension

Là, le manuel de l'extension va rarement vous aider, mais c'est tout simple:

DROP EXTENSION extension_name

Vous pouvez alors droper les packages et autres codes source que vous aviez téléchargés et compilés pour installer l'extension en question.

Savoir où c'est utilisé

Les extensions, c'est chouette... Mais c'est mieux de savoir où c'est utilisé. Pour ce faire, le mieux est de requêter la table pg_extension.

Pour en savoir plus

La documentation de CREATE EXTENSION est ici.
Celle de DROP EXTENSION est là.
Celle de ALTER EXTENSION est là.
Et celle de pg_extension est là.

Changer le mot de passe d'un user

Ça ne vous arrive jamais d'avoir un utilisateur tout penaud qui vient dire qu'il a oublié son mot de passe ? Ou celui de mauvaise foi qui jure ses grands dieux qu'il s'en souvient mais qu'il ne fonctionne plus ?
Bref, c'est toujours bien d'avoir cette petite ligne SQL sous la main:

ALTER ROLE "user_name" WITH PASSWORD 'new_password';

Petit rappel

ALTER USER est un alias d'ALTER ROLE. La commande ALTER USER n'existe que pour ne pas dérouter les admins et pour la rétro-compatilité avec les version 8.0 et inférieures.

Pour en savoir plus

La documentation officielle est disponible ici.

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.

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:

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):

Arrêter l'instance
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)
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)
Créer un fichier recovery.conf
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

Blog

Archive_mode

Pour en savoir plus

Pour en savoir plus

Suppression de tous les objets

Changement de propriétaire pour tous les objets

Suppression d'objets particuliers

Les fonctions

Les vues

Les tables

Pour en savoir plus

Stderr

Pid

Trouver les logs

Pour en savoir plus

Createcluster.conf

Dans la ligne de commande

Vérifier

Pour en savoir plus

Ajouter une extension

Supprimer une extension

Savoir où c'est utilisé

Pour en savoir plus

Petit rappel

Pour en savoir plus

log_destination

log_collector

log_directory

Pour en savoir plus

WAL

Le nom des WAL

Le WAL courant

Pour en savoir plus

Restaure en mode delta

Faire une restauration complète

Faire du PITR

Pour en savoir plus

Installation

Configuration

Stanza ou Stance

Création/paramétrage du stockage

Configuration d'une stance

Création d'une stance

Configurer l'archivage

Vérification

Sauvegarder

Sauvegarde complète

Sauvegarde différentielle

Pour en savoir plus

La procédure

Le fichier recovery.conf

Pour en savoir plus

Modification de la locale

Modification dans postgresql.conf

Pour en savoir plus

Sauvegarde à chaud

Paramétrage de l'instance

Paramétrage de connexion

Les messages d'erreur

Lancer la sauvegarde

Pour en savoir plus