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 "" \
--rm \
--name pgadmin \
--network my_network_default \

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 "" \
--rm \
--name pgadmin \
--network my_network_default \
-v "$(pwd)/pgadmin_data/servers.json":/pgadmin4/servers.json \
-v "$(pwd)/pgadmin_data/pgadmin":/var/lib/pgadmin \

It is not necessary to manually create local directory ./pgadmin_data/, it will be created by Docker but it will set both the owner and the group to root:

$ ls -la 
drwxr-xr-x  4 root  root  4096 Oct  7 16:39 pgadmin_data

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. 

As pgAdmin can't access directory owned by root, we need to change the ownership manually:

$ sudo chown -R 5050:5050 <host_directory>

or, in our case:

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

After this, reload http://localhost:5051 and the login page should load with no issues.

I came across this pgadmin user issue when running pgAdmin from docker-compose. 


version: '3.7'


    image: postgres
    container_name: my-postgres
      - ./database_data:/var/lib/postgresql/data
      - 5432:5432
    stdin_open: true
    tty: true

    image: dpage/pgadmin4
    container_name: my-pgadmin
      - pg_db
    restart: always
      - ./pgadmin:/var/lib/pgadmin
    - 5052:5052

docker-compose up output:

docker-compose up
my-pgadmin | WARNING: Failed to set ACL on the directory containing the configuration database:
my-pgadmin |            [Errno 1] Operation not permitted: '/var/lib/pgadmin'
my-pgadmin | HINT   : You may need to manually set the permissions on
my-pgadmin |          /var/lib/pgadmin to allow pgadmin to write to it.
my-pgadmin | ERROR  : Failed to create the directory /var/lib/pgadmin/sessions:
my-pgadmin |            [Errno 13] Permission denied: '/var/lib/pgadmin/sessions'
my-pgadmin | HINT   : Create the directory /var/lib/pgadmin/sessions, ensure it is writeable by
my-pgadmin |          'pgadmin', and try again, or, create a file
my-pgadmin |          and override the SESSION_DB_PATH setting per
my-pgadmin |
my-pgadmin | sudo: setrlimit(RLIMIT_CORE): Operation not permitted
my-pgadmin | [2021-04-19 15:26:59 +0000] [1] [INFO] Starting gunicorn 19.9.0
my-pgadmin | [2021-04-19 15:26:59 +0000] [1] [INFO] Listening at: http://[::]:5052 (1)
my-pgadmin | [2021-04-19 15:26:59 +0000] [1] [INFO] Using worker: threads
my-pgadmin | /usr/local/lib/python3.8/ RuntimeWarning: line buffering (buffering=1) isn't supported in binary mode, the default buffer size will be used
my-pgadmin |   return, *args, **kwargs)
my-pgadmin | [2021-04-19 15:26:59 +0000] [89] [INFO] Booting worker with pid: 89

$ sudo chown -R 5050:5050 ./pgadmin/
$ ls -la  ./pgadmin/
total 8
drwxr-xr-x 2  5050  5050 4096 Apr 19 16:26 .
drwxrwxr-x 7 bojan bojan 4096 Apr 19 16:26 ..

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/

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/ # ls
/var/lib/pgadmin/storage/ # cat my_table.csv 

How to check pgAdmin4 log file?

Path to pgAdmin log file on Linux is: ~/.pgadmin/pgadmin4.log [FAQ]

1 comment:

Unknown said...

to share the data between the host and the pgadmin container, it is necessary to create on the host a user uid = 5050, gid = 5050

here is an excerpt from my docker-compose.yml

image: dpage/pgadmin4
- "8080:80"
# adduser --uid 5050 pgadmin
# cp kartozageoserver_pgadmin_1:/var/lib/pgadmin /docker/geoserver/.
# chmod 600 /docker/geoserver/pgadmin
#- ./pgadmin:/var/lib/pgadmin
- /docker/geoserver/pgadmin:/var/lib/pgadmin

hope it helps