J’ai consacré les précédents articles aux différents mécanismes de réplications. Le principe est à chaque fois le même, on utilise une copie d’une instance Postgres puis la réplication se fait par rejeu des journaux de transaction (soit par transfert de fichier ou par flux).

Cet article va aborder les différentes technique de sauvegarde … ainsi que la restauration. On ne pense pas assez à la restauration : « la priorité est la sauvegarde, la restauration on verra quand on aura un peu de temps ».

1. Sauvegarde fichier d’une instance (manuelle + pg_base_backup)
2. Sauvegarde par dump des bases avec pg_dump
3. Restauration (manuelle + pg_restore)
4. Restauration PITR

Sauvegarde fichier d’une instance

La sauvegarde la plus simple et la plus basique est la sauvegarde à froid des fichier d’une instance. Il suffit d’arrêter postgres et de faire une copie du dossier data de postgres. Cette solution a l’inconvénient majeur de devoir arrêter Postgres. Nous allons voir d’autres solutions plus élaborées qui évitent d’éteindre le moteur.

Sauvegarde par dump des bases avec pg_dump et pg_dumpall

Pour éviter d’éteindre le serveur on peut tout simplement faire un dump de la base de donnée avec pg_dump. Je ne vais pas recopier la page de documentation de pg_dump ici mais me contenter de vous présenter les options utiles de l’outil.

L’inconvénient ou l’avantage de cet outil est qu’il ne sauvegarde pas toutes les bases d’une instance, il faut donc scripter le tout pour qu’il sauvegarde base par base. J’ai écrit « inconvénient ou avantage », en effet vous pouvez décider de ne restaurer qu’une seule base ou avoir des politique de sauvegarde différentes en fonction des bases. Si vous souhaitez sauvegarder toutes les bases d’un coup vous pouvez utiliser pg_dumpall. Attention, avec pg_dump vous ne sauvegardez pas les rôles donc en cas de restauration vous devriez recréer les rôles. Pour y remédier vous pouvez utiliser -roles-only.

Par défaut, pg_dump va sauvegarder les tables, les données de schéma. Il est possible de les sélectionner avec les options : --data-only ou --schema-only

Petite astuce, à partir de la version 9.3 pg_dump peut paralléliser la sauvegarde des tables avec l’option --jobs=njobs

Pour approfondir :

http://docs.postgresqlfr.org/9.3/app-pgdump.html

http://www.dalibo.org/_media/pgdayfr-postgresql_9.3-start.pdf

Restauration depuis un dump

pgsql

La technique la plus simple consiste à envoyer à psql les commandes issues du dump. Voici mon dump :

 1pg_dump -h /var/run/postgresql/ test
 2
 3--
 4-- PostgreSQL database dump
 5--
 6
 7SET statement_timeout = 0;
 8SET lock_timeout = 0;
 9SET client_encoding = 'UTF8';
10SET standard_conforming_strings = on;
11SET check_function_bodies = false;
12SET client_min_messages = warning;
13
14--
15-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
16--
17
18CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
19
20
21--
22-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
23--
24
25COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
26
27
28SET search_path = public, pg_catalog;
29
30SET default_tablespace = '';
31
32SET default_with_oids = false;
33
34--
35-- Name: films; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
36--
37
38CREATE TABLE films (
39    code character(5),
40    title character varying(40),
41    did integer,
42    date_prod date,
43    kind character varying(10),
44    len interval hour to minute
45);
46
47
48ALTER TABLE public.films OWNER TO postgres;
49
50--
51-- Data for Name: films; Type: TABLE DATA; Schema: public; Owner: postgres
52--
53
54COPY films (code, title, did, date_prod, kind, len) FROM stdin;
55bla     rambo   12      2014-12-01      action  01:00:00
56\.
57
58
59--
60-- Name: production; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
61--
62
63ALTER TABLE ONLY films
64    ADD CONSTRAINT production UNIQUE (date_prod);
65
66
67--
68-- Name: public; Type: ACL; Schema: -; Owner: postgres
69--
70
71REVOKE ALL ON SCHEMA public FROM PUBLIC;
72REVOKE ALL ON SCHEMA public FROM postgres;
73GRANT ALL ON SCHEMA public TO postgres;
74GRANT ALL ON SCHEMA public TO PUBLIC;
75
76
77--
78-- PostgreSQL database dump complete
79--

Comme vous pouvez le constater, pg_dump utilise des « copy » plutôt que des « insert ». La commande copy est bien plus rapide pour l’import en masse de données.

Pour restaurer le dump :

 1psql -h /var/run/postgresql/ test -f dump.sql
 2SET
 3SET
 4SET
 5SET
 6SET
 7SET
 8CREATE EXTENSION
 9COMMENT
10SET
11SET
12SET
13CREATE TABLE
14ALTER TABLE
15ALTER TABLE
16REVOKE
17REVOKE
18GRANT
19GRANT

Pour approfondir :

Documentation de pg_dump

Documentation de pg_dumpall

pg_restore

Cette restauration est assez simple, postgres fourni l’utilitaire pg_restore qui offre des fonctionnalités intéressantes telles que :

• Parallélisation
• Sélection des sections à restaurer. Très utile si vous ne souhaitez restaurer qu’une seule table.

Pour pouvoir utiliser pg_restore il faut spécifier le format « custom » à pg_dump: « -format=custom » ou « -F c ». En effet si vous fournissez un fichier au format « à plat » vous aurez le message suivant :

1pg_restore: [archiveur] Le fichier en entrée semble être une sauvegarde au format texte. Merci d'utiliser psql.

Je ne vais pas recopier la documentation de pg_restore mais juste vous fournir les options les plus intéressantes :

Parallélisation :

1-j number-of-jobs
2--jobs=number-of-jobs

Lister les sections du dump :

1-l
2 --list

Exemple :

 1; Archive created at Sun Jan 11 12:40:21 2015
 2;     dbname: test
 3;     TOC Entries: 11
 4;     Compression: -1
 5;     Dump Version: 1.12-0
 6;     Format: CUSTOM
 7;     Integer: 4 bytes
 8;     Offset: 8 bytes
 9;     Dumped from database version: 9.3.5
10;     Dumped by pg_dump version: 9.3.5
11;
12;
13; Selected TOC Entries:
14;
151939; 1262 16486 DATABASE - test postgres
166; 2615 2200 SCHEMA - public postgres
171940; 0 0 COMMENT - SCHEMA public postgres
181941; 0 0 ACL - public postgres
19171; 3079 11756 EXTENSION - plpgsql
201942; 0 0 COMMENT - EXTENSION plpgsql
21170; 1259 16502 TABLE public films postgres
221934; 0 16502 TABLE DATA public films postgres
231826; 2606 16506 CONSTRAINT public production postgres

Si je souhaite supprimer quelques sections du dump il me suffit de commenter la ligne avec un point-virgule, de sauvegarder le fichier (db.list) puis de lancer pg_restore avec l’option « -L » :

1pg_restore -L db.list db.dump

Pour approfondir : Documentation de pg_restore

Nous venons de voir les différentes technique pour restaurer des données. Cette façon de faire présente quelques inconvénients. Il faut faire des backups régulier. On peut avoir une perte de données conséquente depuis le dernier le backup.

En mettant en place une réplication on peut penser que le problème est réglé. Cependant la réplication nous protège d’une perte du maître mais pas d’une erreur humaine. Un DROP TABLE sur une mauvaise base par exemple! Ainsi le RPO correspond au temps qui s’est écoulé depuis le dernier backup.

Archivage des WAL + restauration PITR

A venir dans un prochain article.

Edit : Le voici : Restauration PITR - Part 7