#################################################
#  Utiliser Postgres avec les conteneurs Docker #
#       christophe.cerin@lipn.univ-paris13.fr   #
#             Vendredi 29 juillet 2017          #
#################################################

Docker est un logiciel libre qui automatise le déploiement
d'applications dans des conteneurs logiciels. Un conteneur est ici un
"bac à sable" dans lequel l'utilisateur peut jouer en étant isolé des
autres utilisateurs si bien qu'il peut "casser" ce qu'il veut dans son
bac à sable, cela n'aura pas d'impact sur le système. Une des grandes
promesses de cette technique, c'est d'augmenter la densité des
ressources en se substituant aux couches d'hypervision et de système
d'exploitation virtualisés (via VirtualBox par exemple), obligatoires
dans le scénario de la virtualisation classique (voir
https://fr.wikipedia.org/wiki/Virtualisation).

Docker étend le format de conteneur Linux standard, LXC, avec une API
de haut niveau fournissant une solution de virtualisation qui exécute
les processus de façon isolée. Docker utilise les LXC, cgroups, et le
noyau Linux lui-même. Contrairement aux machines virtuelles
traditionnelles, un conteneur Docker n'inclut pas de système
d'exploitation, s'appuyant sur les fonctionnalités du système
d’exploitation fournies par l'infrastructure sous-jacente.

Pour installer Docker, aller sur la page https://www.docker.com et
suivez les instructions selon votre système d'exploitation (onglet Get
Docker).

Une fois l'installation effectuée, commencer par lire l'ensemble de ce
qui suit.


# Souce des informations :
# https://github.com/onjin/docker-alpine-postgres
# https://hub.docker.com/_/postgres/

# A lancer une seule fois
docker run --name some-postgres -e POSTGRES_PASSWORD=""  -d alpine:postgres

# utiliser
docker run -it --link some-postgres:postgres --rm onjin/alpine-postgres sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

psql (9.5.6)
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=#

postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gset [PREFIX]         execute query and store results in psql variables
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \ir FILE               as \i, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \ddp    [PATTERN]      list default privileges
  \dD[S+] [PATTERN]      list domains
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[+]  [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[+]  [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dE[S+] [PATTERN]      list foreign tables
  \dx[+]  [PATTERN]      list extensions
  \dy     [PATTERN]      list event triggers
  \l[+]   [PATTERN]      list databases
  \sf[+] FUNCNAME        show a function's definition
  \z      [PATTERN]      same as \dp

Formatting
  \a                     toggle between unaligned and aligned output mode
  \C [STRING]            set table title, or unset if none
  \f [STRING]            show or set field separator for unaligned query output
  \H                     toggle HTML output mode (currently off)
  \pset [NAME [VALUE]]   set table output option
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager|
                         unicode_border_linestyle|unicode_column_linestyle|unicode_header_linestyle})
  \t [on|off]            show only rows (currently off)
  \T [STRING]            set HTML <table> tag attributes, or unset if none
  \x [on|off|auto]       toggle expanded output (currently off)

Connection
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         connect to new database (currently "postgres")
  \encoding [ENCODING]   show or set client encoding
  \password [USERNAME]   securely change the password for a user
  \conninfo              display information about current connection

Operating System
  \cd [DIR]              change the current working directory
  \setenv NAME [VALUE]   set or unset environment variable
  \timing [on|off]       toggle timing of commands (currently off)
  \! [COMMAND]           execute command in shell or start interactive shell

Variables
  \prompt [TEXT] NAME    prompt user to set internal variable
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters
  \unset NAME            unset (delete) internal variable

Large Objects
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID      large object operations
postgres=#

postgres=# \d
No relations found.
postgres=#

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

postgres=# 

postgres=# select 1;
 ?column? 
----------
        1
(1 row)

postgres=#

#
# Nous allons travailler sur la base de donnée Cinéma disponible
# ici https://sites.google.com/site/lebbah/bdd
# Le fichier est BDDfilmCodageISO.zip
# Charger aussi le fichier BDDFilm.zip
#
# Vous etes en mesure de copier/coller le texte pour la
# creation de la base de donnée Film :
#

postgres=# SET datestyle TO "DMY" ;
SET
postgres=# SET datestyle TO "SQL" ;
SET
postgres=# 
postgres=# drop table jouer;
ERROR:  table "jouer" does not exist
postgres=# drop table projection;
ERROR:  table "projection" does not exist
postgres=# drop table film;
ERROR:  table "film" does not exist
postgres=# drop table individu;
ERROR:  table "individu" does not exist
postgres=# drop table cinema;
ERROR:  table "cinema" does not exist
postgres=# 
postgres=# create table individu (
postgres(#     num_individu integer primary key, 
postgres(#     nom varchar(64), 
postgres(#     prenom varchar(64));
CREATE TABLE
postgres=# 
postgres=# create table film (
postgres(#     num_film integer primary key, 
postgres(#     num_realisateur integer references individu, 
postgres(#     titre varchar(128), 
postgres(#     genre varchar(64),
postgres(#     annee date);
CREATE TABLE
postgres=# 
postgres=# create table cinema (
postgres(#     num_cinema integer primary key, 
postgres(#     nom varchar(64), 
postgres(#     adresse varchar(128));
CREATE TABLE
postgres=# 
postgres=# create table jouer (
postgres(#     num_acteur integer references individu, 
postgres(#     num_film integer references film,
postgres(#     role varchar(128),
postgres(#     primary key (num_acteur, num_film));
CREATE TABLE
postgres=# 
postgres=# create table projection (
postgres(#     num_cinema integer references cinema, 
postgres(#     num_film integer references film,
postgres(#     jour date,
postgres(#     primary key (num_cinema, num_film, jour));
CREATE TABLE
postgres=# 
postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

postgres=# \d
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | cinema     | table | postgres
 public | film       | table | postgres
 public | individu   | table | postgres
 public | jouer      | table | postgres
 public | projection | table | postgres
(5 rows)

postgres=# 


#
# Nous allons maintenant remplir les tables
# en les programant avec des INSERT INTO
#
# \copy individu from individu.txt
# \copy cinema from cinema.txt
# \copy film from film.txt
# \copy jouer from jouer.txt
# \copy projection from projection.txt
#

postgres=# INSERT INTO individu VALUES (01, 'Kidman', 'Nicole'),
postgres-# (02, 'Bettany', 'Paul'),
postgres-# (03, 'Watson', 'Emily'),
postgres-# (04, 'Skarsgard', 'Stellan'),
postgres-# (05, 'Travolta', 'John'),
postgres-# (06, 'L. Jackson', 'Samuel'),
postgres-# (07, 'Willis', 'Bruce'),
postgres-# (08, 'Irons', 'Jeremy'),
postgres-# (09, 'Spader', 'James'),
postgres-# (10, 'Hunter', 'Holly'),
postgres-# (11, 'Arquette', 'Rosanna'),
postgres-# (12, 'Wayne', 'John'),
postgres-# (13, 'von Trier', 'Lars'),
postgres-# (14, 'Tarantino', 'Quentin'),
postgres-# (15, 'Cronenberg', 'David'),
postgres-# (16, 'Mazursky', 'Paul'),
postgres-# (17, 'Jones', 'Grace'),
postgres-# (18, 'Glen', 'John'),
postgres-# (19, 'Eastwood', 'Clint');
INSERT 0 19
postgres=# select * from individu;
 num_individu |    nom     | prenom  
--------------+------------+---------
            1 | Kidman     | Nicole
            2 | Bettany    | Paul
            3 | Watson     | Emily
            4 | Skarsgard  | Stellan
            5 | Travolta   | John
            6 | L. Jackson | Samuel
            7 | Willis     | Bruce
            8 | Irons      | Jeremy
            9 | Spader     | James
           10 | Hunter     | Holly
           11 | Arquette   | Rosanna
           12 | Wayne      | John
           13 | von Trier  | Lars
           14 | Tarantino  | Quentin
           15 | Cronenberg | David
           16 | Mazursky   | Paul
           17 | Jones      | Grace
           18 | Glen       | John
           19 | Eastwood   | Clint
(19 rows)

postgres=#

#
# etc, etc...
# L'ensemble des instructions SQL pour insérer dans les tables
# est le suivant (attention à ne pas dupliquer la table individu)
#


INSERT INTO individu VALUES (01, 'Kidman', 'Nicole'),
(02, 'Bettany', 'Paul'),
(03, 'Watson', 'Emily'),
(04, 'Skarsgard', 'Stellan'),
(05, 'Travolta', 'John'),
(06, 'L. Jackson', 'Samuel'),
(07, 'Willis', 'Bruce'),
(08, 'Irons', 'Jeremy'),
(09, 'Spader', 'James'),
(10, 'Hunter', 'Holly'),
(11, 'Arquette', 'Rosanna'),
(12, 'Wayne', 'John'),
(13, 'von Trier', 'Lars'),
(14, 'Tarantino', 'Quentin'),
(15, 'Cronenberg', 'David'),
(16, 'Mazursky', 'Paul'),
(17, 'Jones', 'Grace'),
(18, 'Glen', 'John'),
(19, 'Eastwood', 'Clint');

INSERT INTO cinema VALUES (02, 'Le Fontenelle', '78160 Marly-le-Roi'),
(01, 'Le Renoir', '13100 Aix-en-Provence'),
(03, 'Gaumont Wilson', '31000 Toulouse'),
(04, 'Espace Cine', '93800 Epinay-sur-Seine');

INSERT INTO film VALUES (05, 13, 'Dogville', 'Drame', '01/01/2002'),
(04, 13, 'Breaking the waves', 'Drame', '01/01/1996'),
(03, 14, 'Pulp Fiction', 'Policier', '01/01/1994'),
(02, 15, 'Faux-Semblants', 'Epouvante', '01/01/1988'),
(01, 15, 'Crash', 'Drame', '01/01/1996'),
(06, 12, 'Alamo', 'Western', '01/01/1960'),
(07, 18, 'Dangereusement vôtre', 'Espionnage', '01/01/1985'),
(08, 19, 'Chasseur blanc, coeur noir', 'Drame', '01/01/1989');

INSERT INTO jouer VALUES (01, 05, 'Grace'),
(02, 05, 'Tom Edison'),
(03, 04, 'Bess'),
(04, 04, 'Jan'),
(05, 03, 'Vincent Vega'),
(06, 03, 'Jules Winnfield'),
(07, 03, 'Butch Coolidge'),
(08, 02, 'Beverly & Elliot Mantle'),
(09, 01, 'James Ballard'),
(10, 01, 'Helen Remington'),
(11, 01, 'Gabrielle'),
(04, 05, 'Chuck'),
(16, 07, 'May Day'),
(19, 08, 'John Wilson');

INSERT INTO projection VALUES (02, 05, '01/05/2002'),
(02, 05, '02/05/2002'),
(02, 05, '03/05/2002'),
(02, 04, '02/12/1996'),
(01, 01, '07/05/1996'),
(02, 07, '09/05/1985'),
(01, 04, '02/08/1996'),
(04, 03, '08/04/1994'),
(03, 06, '02/12/1990'),
(02, 02, '08/12/1990'),
(03, 03, '05/11/1994'),
(04, 03, '06/11/1994'),
(01, 06, '05/07/1980'),
(02, 04, '02/09/1996'),
(04, 06, '01/08/2002'),
(03, 06, '09/11/1960'),
(01, 02, '12/03/1988'),
(02, 08, '01/02/1989'),
(02, 01, '11/05/1997'),
(02, 03, '05/07/1994'),
(02, 06, '01/08/2002'),
(01, 03, '02/03/1994');


#
# Vous êtes prets à travailler !
# 
# Je peux quitter le conteneur, puis revenir... et retrouver mes données
#

postgres=# \quit
10:~ christophecerin$ docker run -it --link some-postgres:postgres --rm onjin/alpine-postgres sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'
psql (9.5.6)
Type "help" for help.

postgres=# select * from projection;
 num_cinema | num_film |    jour    
------------+----------+------------
          2 |        5 | 2002-05-01
          2 |        5 | 2002-05-02
          2 |        5 | 2002-05-03
          2 |        4 | 1996-12-02
          1 |        1 | 1996-05-07
          2 |        7 | 1985-05-09
          1 |        4 | 1996-08-02
          4 |        3 | 1994-04-08
          3 |        6 | 1990-12-02
          2 |        2 | 1990-12-08
          3 |        3 | 1994-11-05
          4 |        3 | 1994-11-06
          1 |        6 | 1980-07-05
          2 |        4 | 1996-09-02
          4 |        6 | 2002-08-01
          3 |        6 | 1960-11-09
          1 |        2 | 1988-03-12
          2 |        8 | 1989-02-01
          2 |        1 | 1997-05-11
          2 |        3 | 1994-07-05
          2 |        6 | 2002-08-01
          1 |        3 | 1994-03-02
(22 rows)

postgres=# 

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Pour migrer d'une version à une autre :

Etape 1: installer la dernière version :

$ docker pull postgres
Using default tag: latest
latest: Pulling from library/postgres
723254a2c089: Pull complete 
39ec0e6c372c: Pull complete 
ba1542fb91f3: Pull complete 
c7195e642388: Pull complete 
95424deca6a2: Pull complete 
2d7d4b3a4ce2: Pull complete 
fbde41d4a8cc: Pull complete 
880120b92add: Pull complete 
9a217c784089: Pull complete 
d581543fe8e7: Pull complete 
e5eff8940bb0: Pull complete 
462d60a56b09: Pull complete 
135fa6b9c139: Pull complete 
Digest: sha256:d9242d533368b2d831eb9b88bcc41c3466ff47cd56a6d61b22cee3f34226d7b5
Status: Downloaded newer image for postgres:latest

$ docker run --name postgres -e POSTGRES_PASSWORD='' -d postgres
09b026ccdf663cb92c549994e905c2e53fb4ad6dff96399b6050ab71af3349fc

$ docker run -it --rm --link postgres:postgres postgres psql -h postgres -U postgres
psql (10.1)
Type "help" for help.

postgres=# \d
Did not find any relations.
postgres=# 


Etape 2: sauvegarder toutes les bases de données. Pour cela voir 
la commande https://www.postgresql.org/docs/current/static/app-pg-dumpall.html

Sur cette page vous avez egalement la commande pour recharger toutes les
bases sauvegardées : psql -f db.out postgres 
qui est une commane a exécuter pour la nouvelle version, bien entendu !

Rappel : pour copier un fichier dans ou depuis un container faire
docker cp ... ...