Friday, 14 February 2020

Running pgAdmin in Docker container

pgAdmin is a browser-based DB client. It is possible to run it from a Docker container  - an image is available at DockerHub: dpage/pgadmin4.

I assume we are also running PostgresDB Docker container.

To run pgAdmin Docker container on the same network as PostgresDB container execute:

$  docker run \
-p 5051:5051 \
-d \
-e "PGADMIN_DEFAULT_EMAIL=xxxxxxx@example.com" \
-e "PGADMIN_DEFAULT_PASSWORD=xxxxxxx" \
-e "PGADMIN_LISTEN_PORT=5051" \
--rm \
--name pgadmin \
--network my_network_default \
dpage/pgadmin4

my_network_default is the name of the Docker network on which Postgres DB container is running. This allows using DB service name (as specified in docker-compose.yml) as the DB hostname when adding DB server in pgAdmin4. This is possible if DB container is run via docker-compose.

Once this container is up we can go to http://localhost:5051 in local browser and log in with credentials specified via PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD. To add a new DB server we need to know either its hostname or its IP address.

If both containers are running on the same Docker network (which is our case here as we use --network) then for DB container hostname we can simply use the name of the DB service from docker-compose.yml e.g. db.

If we want to go via DB container IP address route we can find it out we can inspect Postgres container network:

$ docker inspect my_network_default

If you're using VSCode for development, use Docker plugin feature NETWORKS, right-click network of interest and select inspect.

Resolving Issues


In case of any issues, remove -d from this command line in order to run this container undetached in which case all useful output will appear in terminal.

How to update pgAdmin Docker image? 


To update pgAdmin Docker image and then run it, use:

$ docker pull dpage/pgadmin4 && docker run (...) dpage/pgadmin4

How to Persist Data between pgAdmin sessions?


If we restart pgAdmin container, all serves we added before will be lost and we'll need to add them again. To prevent that we can use local host directory as a mounted volume for persistence:

$ docker run \
-p 5051:5051 \
-d \
-e "PGADMIN_DEFAULT_EMAIL=xxxxxxx@example.com" \
-e "PGADMIN_DEFAULT_PASSWORD=xxxxxxx" \
-e "PGADMIN_LISTEN_PORT=5051" \
--rm \
--name pgadmin \
--network my_network_default \
-v "$(pwd)/pgadmin_data/servers.json":/pgadmin4/servers.json \
-v "$(pwd)/pgadmin_data/pgadmin":/var/lib/pgadmin \
dpage/pgadmin4

It is not necessary to manually create local directory ./pgadmin_data/, it will be created by Docker.

From pgAdmin4 docs:

/var/lib/pgadmin - This is the working directory in which pgAdmin stores session data, user files, configuration files, and it’s configuration database. Mapping this directory onto the host machine gives you an easy way to maintain configuration between invocations of the container.
/pgadmin4/servers.json - If this file is mapped, server definitions found in it will be loaded at launch time. This allows connection information to be pre-loaded into the instance of pgAdmin in the container. Note that server definitions are only loaded on first launch, i.e. when the configuration database is created, and not on subsequent launches using the same configuration database.
pgAdmin runs as the pgadmin user (UID: 5050) in the pgadmin group (GID: 5050) in the container. You must ensure that all files are readable, and where necessary (e.g. the working/session directory) for this user on the host machine. For example:

$ sudo chown -R 5050:5050 ./pgadmin_data/


How to export and view exported table?


If we export some table into a csv file, we can see that the following command is executed:

This command is run when using pgAdmin web application to export DB table my_table to csv file:

"/usr/local/pgsql-11/psql" --command " "\\copy public.my_table (column1_name, column2_name...) TO '<STORAGE_DIR>/my_table.csv' CSV QUOTE '\"' ESCAPE '''';""

STORAGE_DIR path is defined in /pgadmin4/config.py.

We can view csv file it if we attach to pgadmin container's terminal:

$ docker exec -it pgadmin /bin/sh
/pgadmin4 # ls -la
...

/var/lib/pgadmin/storage/test_example.com # ls
my_table_export.csv
/var/lib/pgadmin/storage/test_example.com # cat my_table.csv 


1 comment:

D365 said...

The information which you have provided in this blog is really useful to everyone. Thanks for sharing.
Docker and Kubernetes Training
Docker and Kubernetes Online Training
Kubernetes Online Training
Docker Online Training