Tutoriel Docker et Postgresql ¶

Modèle de fichier docker-compose.yml ¶

stack_overflow_postgres.png

version: "3"

services:
  postgres:
        image: postgres:9.5

docker-compose up ¶

Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql>docker-compose up

WARNING: The Docker Engine you're using is running in swarm mode.

Compose does not use swarm mode to deploy services to multiple nodes in a swarm. All containers will be scheduled on the current node.

To deploy your application across the swarm, use `docker stack deploy`.

Creating network "postgresql_default" with the default driver
Pulling postgres (postgres:10)...
10: Pulling from library/postgres
Digest: sha256:3f4441460029e12905a5d447a3549ae2ac13323d045391b0cb0cf8b48ea17463
Status: Downloaded newer image for postgres:10
Creating postgresql_postgres_1 ... done
Attaching to postgresql_postgres_1
postgres_1  | The files belonging to this database system will be owned by user "postgres".
postgres_1  | This user must also own the server process.
postgres_1  |
postgres_1  | The database cluster will be initialized with locale "en_US.utf8".
postgres_1  | The default database encoding has accordingly been set to "UTF8".
postgres_1  | The default text search configuration will be set to "english".
postgres_1  |
postgres_1  | Data page checksums are disabled.
postgres_1  |
postgres_1  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres_1  | creating subdirectories ... ok
postgres_1  | selecting default max_connections ... 100
postgres_1  | selecting default shared_buffers ... 128MB
postgres_1  | selecting dynamic shared memory implementation ... posix
postgres_1  | creating configuration files ... ok
postgres_1  | running bootstrap script ... ok
postgres_1  | performing post-bootstrap initialization ... ok
postgres_1  | syncing data to disk ...
postgres_1  | WARNING: enabling "trust" authentication for local connections
postgres_1  | You can change this by editing pg_hba.conf or using the option -A, or
postgres_1  | --auth-local and --auth-host, the next time you run initdb.
postgres_1  | ok
postgres_1  |
postgres_1  | Success. You can now start the database server using:
postgres_1  |
postgres_1  |     pg_ctl -D /var/lib/postgresql/data -l logfile start
postgres_1  |
postgres_1  | ****************************************************
postgres_1  | WARNING: No password has been set for the database.
postgres_1  |          This will allow anyone with access to the
postgres_1  |          Postgres port to access your database. In
postgres_1  |          Docker's default configuration, this is
postgres_1  |          effectively any other container on the same
postgres_1  |          system.
postgres_1  |
postgres_1  |          Use "-e POSTGRES_PASSWORD=password" to set
postgres_1  |          it in "docker run".
postgres_1  | ****************************************************
postgres_1  | waiting for server to start....2018-01-22 11:51:28.410 UTC [37] LOG:  listening on IPv4 address "127.0.0.1", port 5432
postgres_1  | 2018-01-22 11:51:28.410 UTC [37] LOG:  could not bind IPv6 address "::1": Cannot assign requested address
postgres_1  | 2018-01-22 11:51:28.410 UTC [37] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
postgres_1  | 2018-01-22 11:51:28.510 UTC [37] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1  | 2018-01-22 11:51:28.712 UTC [38] LOG:  database system was shut down at 2018-01-22 11:51:26 UTC
postgres_1  | 2018-01-22 11:51:28.780 UTC [37] LOG:  database system is ready to accept connections
postgres_1  |  done
postgres_1  | server started
postgres_1  | ALTER ROLE
postgres_1  |
postgres_1  |
postgres_1  | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
postgres_1  |
postgres_1  | 2018-01-22 11:51:28.985 UTC [37] LOG:  received fast shutdown request
postgres_1  | waiting for server to shut down....2018-01-22 11:51:29.037 UTC [37] LOG:  aborting any active transactions
postgres_1  | 2018-01-22 11:51:29.042 UTC [37] LOG:  worker process: logical replication launcher (PID 44) exited with exit code 1
postgres_1  | 2018-01-22 11:51:29.042 UTC [39] LOG:  shutting down
postgres_1  | 2018-01-22 11:51:29.405 UTC [37] LOG:  database system is shut down
postgres_1  |  done
postgres_1  | server stopped
postgres_1  |
postgres_1  | PostgreSQL init process complete; ready for start up.
postgres_1  |
postgres_1  | 2018-01-22 11:51:29.565 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_1  | 2018-01-22 11:51:29.565 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_1  | 2018-01-22 11:51:29.665 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1  | 2018-01-22 11:51:29.825 UTC [55] LOG:  database system was shut down at 2018-01-22 11:51:29 UTC
postgres_1  | 2018-01-22 11:51:29.878 UTC [1] LOG:  database system is ready to accept connections

docker-compose run postgres psql -h postgres -U postgres ¶

Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql>docker-compose run postgres psql -h postgres -U postgres

psql (10.1)
Type "help" for help.

postgres=#

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=#

docker-compose down ¶

Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql>docker-compose down

Stopping postgresql_postgres_1 ... done
Removing postgresql_postgres_run_2 ... done
Removing postgresql_postgres_run_1 ... done
Removing postgresql_postgres_1     ... done
Removing network postgresql_default

postgres_1  | 2018-01-22 11:51:29.565 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_1  | 2018-01-22 11:51:29.565 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_1  | 2018-01-22 11:51:29.665 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1  | 2018-01-22 11:51:29.825 UTC [55] LOG:  database system was shut down at 2018-01-22 11:51:29 UTC
postgres_1  | 2018-01-22 11:51:29.878 UTC [1] LOG:  database system is ready to accept connections
postgres_1  | 2018-01-22 11:56:12.567 UTC [66] FATAL:  database "test" does not exist
postgres_1  | 2018-01-22 12:08:39.698 UTC [1] LOG:  received smart shutdown request
postgres_1  | 2018-01-22 12:08:39.749 UTC [1] LOG:  worker process: logical replication launcher (PID 61) exited with exit code 1
postgres_1  | 2018-01-22 12:08:39.750 UTC [56] LOG:  shutting down
postgres_1  | 2018-01-22 12:08:39.965 UTC [1] LOG:  database system is shut down
postgresql_postgres_1 exited with code 0

version: "3"

services:
  db:
        image: postgres:10.1
        volumes:
          - postgres_data:/var/lib/postgresql/data/

docker-compose build ¶

Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql>docker-compose build

db uses an image, skipping

docker-compose up ¶

Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql>docker-compose up

WARNING: The Docker Engine you're using is running in swarm mode.

Compose does not use swarm mode to deploy services to multiple nodes in a swarm. All containers will be scheduled on the current node.

To deploy your application across the swarm, use `docker stack deploy`.

Creating network "postgresql_default" with the default driver
Creating volume "postgresql_postgres_data" with default driver
Creating postgresql_db_1 ... done
Attaching to postgresql_db_1
db_1  | The files belonging to this database system will be owned by user "postgres".
db_1  | This user must also own the server process.
db_1  |
db_1  | The database cluster will be initialized with locale "en_US.utf8".
db_1  | The default database encoding has accordingly been set to "UTF8".
db_1  | The default text search configuration will be set to "english".
db_1  |
db_1  | Data page checksums are disabled.
db_1  |
db_1  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
db_1  | creating subdirectories ... ok
db_1  | selecting default max_connections ... 100
db_1  | selecting default shared_buffers ... 128MB
db_1  | selecting dynamic shared memory implementation ... posix

docker-compose exec -u postgres db psql ¶

psql (10.1)
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=# \h

Available help:
  ABORT                            ALTER TRIGGER                    CREATE RULE                      DROP GROUP                       LISTEN
  ALTER AGGREGATE                  ALTER TYPE                       CREATE SCHEMA                    DROP INDEX                       LOAD
  ALTER COLLATION                  ALTER USER                       CREATE SEQUENCE                  DROP LANGUAGE                    LOCK
  ALTER CONVERSION                 ALTER USER MAPPING               CREATE SERVER                    DROP MATERIALIZED VIEW           MOVE
  ALTER DATABASE                   ALTER VIEW                       CREATE STATISTICS                DROP OPERATOR                    NOTIFY
  ALTER DEFAULT PRIVILEGES         ANALYZE                          CREATE SUBSCRIPTION              DROP OPERATOR CLASS              PREPARE
  ALTER DOMAIN                     BEGIN                            CREATE TABLE                     DROP OPERATOR FAMILY             PREPARE TRANSACTION
  ALTER EVENT TRIGGER              CHECKPOINT                       CREATE TABLE AS                  DROP OWNED                       REASSIGN OWNED
  ALTER EXTENSION                  CLOSE                            CREATE TABLESPACE                DROP POLICY                      REFRESH MATERIALIZED VIEW
  ALTER FOREIGN DATA WRAPPER       CLUSTER                          CREATE TEXT SEARCH CONFIGURATION DROP PUBLICATION                 REINDEX
  ALTER FOREIGN TABLE              COMMENT                          CREATE TEXT SEARCH DICTIONARY    DROP ROLE                        RELEASE SAVEPOINT
  ALTER FUNCTION                   COMMIT                           CREATE TEXT SEARCH PARSER        DROP RULE                        RESET
  ALTER GROUP                      COMMIT PREPARED                  CREATE TEXT SEARCH TEMPLATE      DROP SCHEMA                      REVOKE
  ALTER INDEX                      COPY                             CREATE TRANSFORM                 DROP SEQUENCE                    ROLLBACK
  ALTER LANGUAGE                   CREATE ACCESS METHOD             CREATE TRIGGER                   DROP SERVER                      ROLLBACK PREPARED
  ALTER LARGE OBJECT               CREATE AGGREGATE                 CREATE TYPE                      DROP STATISTICS                  ROLLBACK TO SAVEPOINT
  ALTER MATERIALIZED VIEW          CREATE CAST                      CREATE USER                      DROP SUBSCRIPTION                SAVEPOINT
  ALTER OPERATOR                   CREATE COLLATION                 CREATE USER MAPPING              DROP TABLE                       SECURITY LABEL
  ALTER OPERATOR CLASS             CREATE CONVERSION                CREATE VIEW                      DROP TABLESPACE                  SELECT
  ALTER OPERATOR FAMILY            CREATE DATABASE                  DEALLOCATE                       DROP TEXT SEARCH CONFIGURATION   SELECT INTO
  ALTER POLICY                     CREATE DOMAIN                    DECLARE                          DROP TEXT SEARCH DICTIONARY      SET
  ALTER PUBLICATION                CREATE EVENT TRIGGER             DELETE                           DROP TEXT SEARCH PARSER          SET CONSTRAINTS
  ALTER ROLE                       CREATE EXTENSION                 DISCARD                          DROP TEXT SEARCH TEMPLATE        SET ROLE
  ALTER RULE                       CREATE FOREIGN DATA WRAPPER      DO                               DROP TRANSFORM                   SET SESSION AUTHORIZATION
  ALTER SCHEMA                     CREATE FOREIGN TABLE             DROP ACCESS METHOD               DROP TRIGGER                     SET TRANSACTION
  ALTER SEQUENCE                   CREATE FUNCTION                  DROP AGGREGATE                   DROP TYPE                        SHOW
  ALTER SERVER                     CREATE GROUP                     DROP CAST                        DROP USER                        START TRANSACTION
  ALTER STATISTICS                 CREATE INDEX                     DROP COLLATION                   DROP USER MAPPING                TABLE
  ALTER SUBSCRIPTION               CREATE LANGUAGE                  DROP CONVERSION                  DROP VIEW                        TRUNCATE
  ALTER SYSTEM                     CREATE MATERIALIZED VIEW         DROP DATABASE                    END                              UNLISTEN
  ALTER TABLE                      CREATE OPERATOR                  DROP DOMAIN                      EXECUTE                          UPDATE
  ALTER TABLESPACE                 CREATE OPERATOR CLASS            DROP EVENT TRIGGER               EXPLAIN                          VACUUM
  ALTER TEXT SEARCH CONFIGURATION  CREATE OPERATOR FAMILY           DROP EXTENSION                   FETCH                            VALUES
  ALTER TEXT SEARCH DICTIONARY     CREATE POLICY                    DROP FOREIGN DATA WRAPPER        GRANT                            WITH
  ALTER TEXT SEARCH PARSER         CREATE PUBLICATION               DROP FOREIGN TABLE               IMPORT FOREIGN SCHEMA
  ALTER TEXT SEARCH TEMPLATE       CREATE ROLE                      DROP FUNCTION                    INSERT

docker ps ¶

Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql>docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
d205b9239366        postgres:10         "docker-entrypoint.s…"   6 minutes ago       Up 6 minutes        5432/tcp            postgresql_db_1

docker exec -it d205b9239366 bash ¶

Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql>docker exec -it d205b9239366 bash

root@d205b9239366:/# ps -ef

UID         PID   PPID  C STIME TTY          TIME CMD
postgres      1      0  0 12:23 ?        00:00:00 postgres
postgres     56      1  0 12:23 ?        00:00:00 postgres: checkpointer process
postgres     57      1  0 12:23 ?        00:00:00 postgres: writer process
postgres     58      1  0 12:23 ?        00:00:00 postgres: wal writer process
postgres     59      1  0 12:23 ?        00:00:00 postgres: autovacuum launcher process
postgres     60      1  0 12:23 ?        00:00:00 postgres: stats collector process
postgres     61      1  0 12:23 ?        00:00:00 postgres: bgworker: logical replication launcher
postgres     66      0  0 12:28 pts/0    00:00:00 /usr/lib/postgresql/10/bin/psql
postgres     78      1  0 12:28 ?        00:00:00 postgres: postgres postgres [local] idle
root        110      0  0 12:45 pts/1    00:00:00 bash
root        114    110  0 12:45 pts/1    00:00:00 ps -ef

root@d205b9239366:/# uname -a

Linux d205b9239366 4.9.60-linuxkit-aufs #1 SMP Mon Nov 6 16:00:12 UTC 2017 x86_64 GNU/Linux

root@d205b9239366:/# which psql

/usr/bin/psql

Mardi 30 janvier 2018 ¶

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
02b2487f304e        postgres:10.1       "docker-entrypoint.s…"   18 seconds ago      Up 16 seconds       5432/tcp            postgres_test

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker exec -it 02b2487f304e bash

root@02b2487f304e:/# psql -U postgres
psql (10.1)
Type "help" for help.

postgres=# \dt
Did not find any relations.
postgres=# \l
                                                                        List of databases
          Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------------+----------+----------+------------+------------+-----------------------
 db_id3_intranet | id3admin | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres        | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                 |          |          |            |            | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                 |          |          |            |            | postgres=CTc/postgres
(4 rows)

docker-compose.yml ¶

version: "3"

services:
  db:
        image: postgres:10.1
        container_name: container_intranet
        volumes:
          - volume_intranet:/var/lib/postgresql/data/

volumes:
  volume_intranet:

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
47501acda106        postgres:10.1       "docker-entrypoint.s…"   15 minutes ago      Up 15 minutes       5432/tcp            container_intranet

docker volume ls ¶

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker volume ls

DRIVER              VOLUME NAME
local               postgresql_volume_intranet

docker volume inspect postgresql_volume_intranet ¶

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker volume inspect  postgresql_volume_intranet

[
        {
                "CreatedAt": "2018-01-30T12:14:30Z",
                "Driver": "local",
                "Labels": {
                        "com.docker.compose.project": "postgresql",
                        "com.docker.compose.volume": "volume_intranet"
                },
                "Mountpoint": "/var/lib/docker/volumes/postgresql_volume_intranet/_data",
                "Name": "postgresql_volume_intranet",
                "Options": {},
                "Scope": "local"
        }
]

docker exec -it 47501acda106 bash ¶

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker exec -it 47501acda106 bash

psql -U postgres ¶

root@47501acda106:/# psql -U postgres

psql (10.1)
Type "help" for help.

l (liste des bases de données)¶

postgres=# \l

                                                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                   |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                   |          |          |            |            | postgres=CTc/postgres
(3 rows)

CREATE USER id3admin WITH PASSWORD ‘id338’;¶

postgres=# CREATE USER id3admin WITH PASSWORD 'id338';

CREATE ROLE

CREATE DATABASE db_id3_intranet WITH OWNER = id3admin ENCODING = ‘UTF8’ CONNECTION LIMIT = -1;¶

postgres=# CREATE DATABASE db_id3_intranet WITH OWNER = id3admin ENCODING = 'UTF8' CONNECTION LIMIT = -1;

CREATE DATABASE

l ¶

postgres=# \l

                                                                        List of databases
          Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------------+----------+----------+------------+------------+-----------------------
 db_id3_intranet | id3admin | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres        | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                 |          |          |            |            | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                 |          |          |            |            | postgres=CTc/postgres
(4 rows)

docker-compose run db env ¶

docker-compose config ¶

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker-compose config

services:
  db:
        container_name: container_intranet
        environment:
          LANG: fr_FR.utf8
        image: postgres:10.1
        ports:
        - 5432:5432/tcp
        volumes:
        - volume_intranet:/var/lib/postgresql/data/:rw
        - Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql:/code:rw
version: '3.0'
volumes:
  volume_intranet: {}

Import de la base de données ¶

pg_restore --dbname=db_id3_intranet  --username=id3admin  -f db_id3_intranet.sql

Mercredi 31 janvier 2018 : export/import d’une base de données PostgreSQL (tutoriel PostgreSQL)¶

pg_dump -U postgres –clean –create -f db.dump.sql db_id3_intranet ¶

pg_dump -U postgres --clean --create -f db.dump.sql db_id3_intranet

pg_dump -U postgres –clean –create -f db.dump.sql db_id3_intranet

Entête de db.dump ¶

C’est du format texte.

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.1
-- Dumped by pg_dump version 10.1

-- Started on 2018-01-31 10:16:48

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE db_id3_intranet;
--
-- TOC entry 3644 (class 1262 OID 16394)
-- Name: db_id3_intranet; Type: DATABASE; Schema: -; Owner: id3admin
--

CREATE DATABASE db_id3_intranet WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'French_France.1252' LC_CTYPE = 'French_France.1252';


ALTER DATABASE db_id3_intranet OWNER TO id3admin;

\connect db_id3_intranet

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

Expérience substitution de db_id3_save à db_id3_intranet ¶

On substitue db_id3_save à db_id3_intranet. On espère donc créer une copie de la base de données db_id3_intranet. Comme le fichier est au format texte, on peut utiliser psql pour l’import.

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.1
-- Dumped by pg_dump version 10.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: db_id3_save; Type: DATABASE; Schema: -; Owner: id3admin
--

CREATE DATABASE db_id3_save WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'French_France.1252' LC_CTYPE = 'French_France.1252';


ALTER DATABASE db_id3_save OWNER TO id3admin;

\connect db_id3_save

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: db_id3_save; Type: COMMENT; Schema: -; Owner: id3admin
--

COMMENT ON DATABASE db_id3_save IS 'La base db_id3_save';

psql -U postgres -f .db.dump.sql ¶

psql -U postgres -f .\db.dump.sql

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
GRANT

OK, tout s’est bien passé.

psql -U postgres -f .db.dump.sql

On voit aussi que l’encodage French_France.1252 va peut-être poser des problèmes dans l’image Docker actuelle.

postgres=# \l
                                                                                 Liste des bases de donnÚes
           Nom       | PropriÚtaire | Encodage |  Collationnement   |    Type caract.    |    Droits d'accÞs
-----------------+--------------+----------+--------------------+--------------------+-----------------------
 db_id3_intranet | id3admin     | UTF8     | French_France.1252 | French_France.1252 |
 db_id3_save     | id3admin     | UTF8     | French_France.1252 | French_France.1252 |
 db_test         | id3admin     | UTF8     | French_France.1252 | French_France.1252 |
 postgres        | postgres     | UTF8     | French_France.1252 | French_France.1252 |
 template0       | postgres     | UTF8     | French_France.1252 | French_France.1252 | =c/postgres          +
                                 |              |          |                    |                    | postgres=CTc/postgres
 template1       | postgres     | UTF8     | French_France.1252 | French_France.1252 | =c/postgres          +
                                 |              |          |                    |                    | postgres=CTc/postgres
(6 lignes)

Sur Docker on a:

root@02b2487f304e:/# psql -U postgres
psql (10.1)
Type "help" for help.

postgres=# \dt
Did not find any relations.
postgres=# \l
                                                                        List of databases
          Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------------+----------+----------+------------+------------+-----------------------
 db_id3_intranet | id3admin | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres        | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                 |          |          |            |            | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                 |          |          |            |            | postgres=CTc/postgres
(4 rows)

On suit les conseils donnés ici: On essaye déjà avec la langue allemande et on essayera avec French_France.1252 ?

Dockerfile:

FROM postgres:10.1
RUN localedef -i de_DE -c -f UTF-8 -A /usr/share/locale/locale.alias de_DE.UTF-8
ENV LANG de_DE.utf8

docker-compose stop ¶

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker-compose stop

Stopping container_intranet ... done

docker-compose build ¶

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker-compose build

Building db
Step 1/3 : FROM postgres:10.1
 ---> ec61d13c8566
Step 2/3 : RUN localedef -i de_DE -c -f UTF-8 -A /usr/share/locale/locale.alias de_DE.UTF-8
 ---> Running in 19e95836a1ce
Removing intermediate container 19e95836a1ce
 ---> 331ee9213868
Step 3/3 : ENV LANG de_DE.utf8
 ---> Running in 852054da9e27
Removing intermediate container 852054da9e27
 ---> 56dd534c98f7
Successfully built 56dd534c98f7
Successfully tagged postgres:10.1

CREATE DATABASE db_id3_save WITH TEMPLATE = template0 ENCODING = ‘UTF8’ LC_COLLATE = ‘fr_FR.UTF-8’ LC_CTYPE = ‘fr_FR.UTF-8’;¶

postgres=# CREATE DATABASE db_id3_save WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'fr_FR.UTF-8' LC_CTYPE = 'fr_FR.UTF-8';

CREATE DATABASE

Previous topic

Next topic

Tutoriel Docker et Postgresql ¶

Modèle de fichier docker-compose.yml ¶

docker-compose up ¶

docker-compose run postgres psql -h postgres -U postgres ¶

docker-compose down ¶

docker-compose build ¶

docker-compose up ¶

docker-compose exec -u postgres db psql ¶

docker ps ¶

docker exec -it d205b9239366 bash ¶

Mardi 30 janvier 2018 ¶

docker-compose.yml ¶

docker volume ls ¶

docker volume inspect postgresql_volume_intranet ¶

docker exec -it 47501acda106 bash ¶

psql -U postgres ¶

l (liste des bases de données)¶

CREATE USER id3admin WITH PASSWORD ‘id338’;¶

CREATE DATABASE db_id3_intranet WITH OWNER = id3admin ENCODING = ‘UTF8’ CONNECTION LIMIT = -1;¶

l ¶

docker-compose run db env ¶

docker-compose config ¶

Import de la base de données ¶

Mercredi 31 janvier 2018 : export/import d’une base de données PostgreSQL (tutoriel PostgreSQL)¶

pg_dump -U postgres –clean –create -f db.dump.sql db_id3_intranet ¶

Entête de db.dump ¶

Expérience substitution de db_id3_save à db_id3_intranet ¶

psql -U postgres -f .db.dump.sql ¶

docker-compose stop ¶

docker-compose build ¶

CREATE DATABASE db_id3_save WITH TEMPLATE = template0 ENCODING = ‘UTF8’ LC_COLLATE = ‘fr_FR.UTF-8’ LC_CTYPE = ‘fr_FR.UTF-8’;¶