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

docker-compose.yml:

version: '3.7'

services:

  pg_db:
    image: postgres
    container_name: my-postgres
    volumes:
      - ./database_data:/var/lib/postgresql/data
    ports:
      - 5432:5432
    environment:
      POSTGRES_DB: $PGDATABASE
      POSTGRES_USER: $PGUSER
      POSTGRES_PASSWORD: $PGPASSWORD
    stdin_open: true
    tty: true

  pg_admin:
    image: dpage/pgadmin4
    container_name: my-pgadmin
    depends_on:
      - pg_db
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: test@example.com
      PGADMIN_DEFAULT_PASSWORD: postgres
      PGADMIN_LISTEN_PORT: 5052
    volumes:
      - ./pgadmin:/var/lib/pgadmin
    ports:
    - 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 config_local.py file
my-pgadmin |          and override the SESSION_DB_PATH setting per
my-pgadmin |          https://www.pgadmin.org/docs/pgadmin4/4.26/config_py.html
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/os.py:1023: RuntimeWarning: line buffering (buffering=1) isn't supported in binary mode, the default buffer size will be used
my-pgadmin |   return io.open(fd, *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/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 


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

pgadmin:
image: dpage/pgadmin4
ports:
- "8080:80"
volumes:
# 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