We want PostgreSQL in a container and pgadmin4 in another container. And we want to connect the pgadmin4 container to the PostgreSQL container.

We do

$ sudo docker run -p 80:80 \
  --link postgres11:postgres11 \
  --name pgadmin_4pgdocker \
  -e "PGADMIN_DEFAULT_EMAIL=sql.design.company@gmail.com" \
  -e "PGADMIN_DEFAULT_PASSWORD=secret" \
  -d dpage/pgadmin4

80:80 means that the port 80 of the container is tunneled to the port 80 of the host.

–link postgres11:postgres11 means that we link the pgadmin4 container to the postgres11 container. Remember that we wrote in the definition of the postgres11 container the name of the host :

...
--name postgres11
...

and then we open our browser on 127.0.0.1:80, we give the username/password as defined above and when create a server, we give as host name postgres11

For the host name, we give postgres11 instead of 127.0.0.1 !

and then…

We can set about tackling the work !

And the next time we reboot, we have a look on our containers

$ sudo docker ps -a

that gives

CONTAINER ID   IMAGE           COMMAND                CREATED       STATUS                        
PORTS                        NAMES
c8f82e1fd7f6   dpage/pgadmin4  "/entrypoint.sh"       27 hours ago  Exited (255) 10 minutes ago   
0.0.0.0:80->80/tcp, 443/tcp  jolly_mendel
c9159dae8aae   postgres        "docker-entrypoint.s…" 27 hours ago  Up About a minute             
0.0.0.0:5432->5432/tcp       postgres11

They are of course there and their CONTAINER ID are the same. By the way, the creators of containers are used to give them poetical names like jolly_mendel.

And we restart them

$ sudo docker start c9159dae8aae
$ sudo docker start c8f82e1fd7f6

So nice !

And if we want that pgadmin4 reaches the cluster located on the host, we do

$ sudo docker run \
  -d \
  --rm \
  --net=host \
  -e "PGADMIN_DEFAULT_EMAIL=sql.design.company@gmail.com" \
  -e "PGADMIN_DEFAULT_PASSWORD=secret" \
  --name pgadmin_4_local dpage/pgadmin4                                      

We have to stop pgadmin_4pgdocker before starting the previous command.

At this very moment, I don’t know how to manage two pgadmin4 containers simultaneously.

Or to have two clusters in pgadmin4 : one for the PostgreSQL cluster in the container and one for the PostgreSQL on the host.

A bit patience…

We create a volume with

$ sudo docker volume create pgdata

and we call docker run

$ sudo docker run -p 5432:5432 -d \
    -e POSTGRES_PASSWORD=secret \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_DB=postgres \
    --name postgres11 \
    -v pgdata:/var/lib/postgresql/data \
    postgres

5432 is the usual port number for PostgreSQL and we tunnel the port of the host into the same port within the container.

The parameter -v maps this volume with the standard location of data in PostgreSQL cluster

/var/lib/postgresql/data is the usual location of PostgreSQL data files. Those files are located in the container. To know the location of the files on the host, we inspect the volume

$ sudo docker volume inspect pgdata pgdata

that gives

...
"Mountpoint": "/var/lib/docker/volumes/pgdata/_data"
... 

We do a ls /var/lib/docker/volumes/pgdata/_data on the host and we see the same files !

We have a look on the running container

$ sudo docker ps

that gives

CONTAINER ID    IMAGE       COMMAND                  CREATED             STATUS              PORTS                           NAMES
c9159dae8aae    postgres    "docker-entrypoint.s…"   14 minutes ago      Up 14 minutes       0.0.0.0:5432->5432/tcp          postgres11

and we open a psql session on it

$ sudo docker exec -it c9159dae8aae psql -U postgres postgres

and we create a database, a table and a record in it

create database window-functions;

create table test (a text);

insert into test(a) values('hello');

select * from a;

and psql answers

  a   
-------
 hello
(1 row)

and we quit psql

\q

We stop the container (or we do a shutdown our computer)

$ sudo docker stop c9159dae8aae

We start it again (to list the containers, do a sudo docker ps -a after a shutdown because sudo docker ps gives running containers only)

$ sudo docker start c9159dae8aae

We come back in psql

$ sudo docker exec -it c9159dae8aae psql -U postgres postgres

and we connect to our database window-functions

postgres=# \c window-functions

psql answers

You are now connected to database "window-functions" as user "postgres".

and we type

window-functions=# select * from test;

and we obtain

   a   
-------
 hello
(1 row)

The deal is in the bag !

The CONTAINER IDs are persistent. Then we can encapsulate the docker commands in a script.
And this containerized version of PostgreSQL coexists with the local one as we can see in the list of running processes.

$ ps -ef | grep postgres

gives

UID        PID  PPID  C STIME TTY          TIME CMD

...
postgres  1661     1  0 16:41 ?        00:00:00 /usr/lib/postgresql/11/bin/postgres 
       -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
postgres  1736  1661  0 16:41 ?        00:00:00 postgres: 11/main: checkpointer   
postgres  1737  1661  0 16:41 ?        00:00:00 postgres: 11/main: background writer   
postgres  1738  1661  0 16:41 ?        00:00:00 postgres: 11/main: walwriter   
postgres  1740  1661  0 16:41 ?        00:00:00 postgres: 11/main: autovacuum launcher   
postgres  1742  1661  0 16:41 ?        00:00:00 postgres: 11/main: stats collector   
postgres  1743  1661  0 16:41 ?        00:00:00 postgres: 11/main: logical replication launcher   
999       8637  8615  0 17:47 ?        00:00:00 postgres
999       8719  8637  0 17:47 ?        00:00:00 postgres: checkpointer  
999       8720  8637  0 17:47 ?        00:00:00 postgres: background writer  
999       8721  8637  0 17:47 ?        00:00:00 postgres: walwriter  
999       8722  8637  0 17:47 ?        00:00:00 postgres: autovacuum launcher  
999       8723  8637  0 17:47 ?        00:00:02 postgres: stats collector  
999       8724  8637  0 17:47 ?        00:00:00 postgres: logical replication launcher  
999       8973  8637  0 17:49 ?        00:00:28 postgres: mchl postgres 172.17.0.3(45606) idle
...

The user 999 is the owner of the postgres processes launched by docker and the user postgres is the user of the local PostgreSQL cluster.

The PPID (Parent Process ID of the postgres process owned by 999 is 8615 : the docker process.

The PPID of the postgres process owned by postgres is 1 : /sbin/init, the father of all processes.

To run a shell inside the container, we do

$ sudo docker exec -it c9159dae8aae /bin/bash

and to run psql inside the container, we do

$ sudo docker exec -it c9159dae8aae psql -U postgres -d postgres

We pull the latest version of PostgreSQL :

$ sudo docker pull postgres

This image comes from the hub of docker for PostgreSQL. Without additional parameter, pull takes the last version.

We run PostgreSQL :

$ sudo docker run --name pg-docker -e POSTGRES_PASSWORD=secret -d postgres

And we list the containers

$ sudo docker ps   

gives

CONTAINER ID   IMAGE     COMMAND                  CREATED             STATUS             PORTS     NAMES
00ab12a6f24d   postgres  "docker-entrypoint.s…"   About an hour ago   Up About an hour   5432/tcp  pg-docker

and we list the images

$ sudo docker images

gives

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres            latest              f9b577fb1ed6        3 weeks ago         311MB
hello-world         latest              4ab4c602aa5e        3 months ago        1.84kB

and now we connect to PostgreSQL with psql :

$ sudo docker run -it --rm --link pg-docker:postgres postgres psql -h postgres -U postgres

and we obtain :

Password for user postgres: 
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

postgres=# 

It works !

It’s nice but… If we insert data in the database, they will be lost when the container will be stopped. To implement persistence, we will use volumes. Have a look on the next post.

We can now remove the image of hello-world

 $ sudo docker rmi hello-world -f

and then

$ sudo docker images

gives

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres            latest              f9b577fb1ed6        3 weeks ago         311MB