Images PostgreSQL¶

Short Description ¶

The PostgreSQL object-relational database system provides reliability and data integrity.

Description ¶

PostgreSQL est un système de gestion de base de données relationnelle et objet (SGBDRO). C’est un outil libre disponible selon les termes d’une licence de type BSD.

Ce système est concurrent d’autres systèmes de gestion de base de données, qu’ils soient libres (comme MariaDB, MySQL et Firebird), ou propriétaires (comme Oracle, Sybase, DB2, Informix et Microsoft SQL Server).

Comme les projets libres Apache et Linux, PostgreSQL n’est pas contrôlé par une seule entreprise, mais est fondé sur une communauté mondiale de développeurs et d’entreprises

What is PostgreSQL ?¶

Environment Variables ¶

The PostgreSQL image uses several environment variables which are easy to miss. While none of the variables are required, they may significantly aid you in using the image.

POSTGRES_PASSWORD ¶

This environment variable is recommended for you to use the PostgreSQL image. This environment variable sets the superuser password for PostgreSQL. The default superuser is defined by the POSTGRES_USER environment variable. In the above example, it is being set to “mysecretpassword”.

Note 1: The PostgreSQL image sets up trust authentication locally so you may notice a password is not required when connecting from localhost (inside the same container). However, a password will be required if connecting from a different host/container.

Note 2: This variable defines the superuser password in the PostgreSQL instance, as set by the initdb script during inital container startup. It has no effect on the PGPASSWORD environment variable that may be used by the psql client at runtime, as described at

https://www.postgresql.org/docs/10/static/libpq-envars.html. PGPASSWORD, if used, will be specified as a separate environment variable.

POSTGRES_USER ¶

This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used.

PGDATA ¶

This optional environment variable can be used to define another location - like a subdirectory - for the database files. The default is /var/lib/postgresql/data, but if the data volume you’re using is a fs mountpoint (like with GCE persistent disks), Postgres initdb recommends a subdirectory (for example /var/lib/postgresql/data/pgdata ) be created to contain the data.

POSTGRES_DB ¶

This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used. POSTGRES_INITDB_ARGS

This optional environment variable can be used to send arguments to postgres initdb. The value is a space separated string of arguments as postgres initdb would expect them. This is useful for adding functionality like data page checksums: -e POSTGRES_INITDB_ARGS=”–data-checksums”.

POSTGRES_INITDB_WALDIR ¶

This optional environment variable can be used to define another location for the Postgres transaction log. By default the transaction log is stored in a subdirectory of the main Postgres data folder (PGDATA). Sometimes it can be desireable to store the transaction log in a different directory which may be backed by storage with different performance or reliability characteristics.

Note: on PostgreSQL 9.x, this variable is POSTGRES_INITDB_XLOGDIR (reflecting the changed name of the –xlogdir flag to –waldir in PostgreSQL 10+).

Docker Secrets ¶

As an alternative to passing sensitive information via environment variables, _FILE may be appended to the previously listed environment variables, causing the initialization script to load the values for those variables from files present in the container. In particular, this can be used to load passwords from Docker secrets stored in /run/secrets/<secret_name> files. For example:

$ docker run --name some-postgres -e POSTGRES_PASSWORD_FILE=/run/secrets/postgres-passwd -d postgres

Currently, this is only supported for POSTGRES_INITDB_ARGS, POSTGRES_PASSWORD, POSTGRES_USER, and POSTGRES_DB

How to extend this image ¶

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, .sql.gz, or .sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary).

After the entrypoint calls initdb to create the default postgres user and database, it will run any .sql files and source any .sh scripts found in that directory to do further initialization before starting the service.

For example, to add an additional user and database, add the following to /docker-entrypoint-initdb.d/init-user-db.sh:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
        CREATE USER docker;
        CREATE DATABASE docker;
        GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

These initialization files will be executed in sorted name order as defined by the current locale, which defaults to en_US.utf8.

Any .sql files will be executed by POSTGRES_USER, which defaults to the postgres superuser.

It is recommended that any psql commands that are run inside of a .sh script be executed as POSTGRES_USER by using the –username “$POSTGRES_USER” flag. This user will be able to connect without a password due to the presence of trust authentication for Unix socket connections made inside the container.

Additionally, as of docker-library/postgres#253, these initialization scripts are run as the postgres user (or as the “semi-arbitrary user” specified with the –user flag to docker run; see the section titled “Arbitrary –user Notes” for more details).

Extends with a Dockerfile ¶

You can also extend the image with a simple Dockerfile to set a different locale. The following example will set the default locale to de_DE.utf8:

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

Since database initialization only happens on container startup, this allows us to set the language before it is created.

docker-compose up ¶

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

PS 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`.

Building db
Step 1/3 : FROM postgres:10.1
10.1: 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:3f4441460029e12905a5d447a3549ae2ac13323d045391b0cb0cf8b48ea17463
Status: Downloaded newer image for postgres:10.1
 ---> ec61d13c8566
Step 2/3 : RUN localedef -i fr_FR -c -f UTF-8 -A /usr/share/locale/locale.alias fr_FR.UTF-8
 ---> Running in 18aa6161e381
Removing intermediate container 18aa6161e381
 ---> a20322020edd
Step 3/3 : ENV LANG fr_FR.utf8
 ---> Running in 0245352c15af
Removing intermediate container 0245352c15af
 ---> b738f47d14a3
Successfully built b738f47d14a3
Successfully tagged postgres:10.1
WARNING: Image for service db was built because it did not already exist. To rebuild this image you must use `docker-compose build` or `docker-compose up --build`.
Creating container_intranet ... done
Attaching to container_intranet
container_intranet | 2018-01-31 12:09:54.628 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
container_intranet | 2018-01-31 12:09:54.628 UTC [1] LOG:  listening on IPv6 address "::", port 5432
container_intranet | 2018-01-31 12:09:54.839 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
container_intranet | 2018-01-31 12:09:55.034 UTC [20] LOG:  database system was shut down at 2018-01-31 12:03:16 UTC
container_intranet | 2018-01-31 12:09:55.135 UTC [1] LOG:  database system is ready to accept connections

PS Y:\projects_id3\P5N001\XLOGCA135_tutorial_docker\tutorial_docker\tutoriels\postgresql> docker exec -ti dda260532cd7 bash

root@dda260532cd7:/# psql -U postgres
psql (10.1)
Saisissez « help » pour l'aide.

postgres=# \l
                                                                  Liste des bases de données
        Nom    | Propriétaire | Encodage | Collationnement | Type caract. |    Droits d'accès
-----------+--------------+----------+-----------------+--------------+-----------------------
 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 lignes)

Previous topic

Next topic