Automatically connect pgAdmin to a PostgreSQL volume on which there is a database and automatically load a schema present on a .sql file with Docker Compose

federicogalimberti · May 17, 2022, 6:26pm

I make a premise. I have installed Docker inside Ubuntu 22.04 and I have installed Ubuntu 22.04 inside a vmware VM. My goal is to build a well-equipped local server with Docker.

I have the following Docker project which works perfectly but has a very small problem. After starting the 3 containers I have to connect to pgAdmin and connect the software to the volume using this configuration:

I’ve read this page but haven’t found any environment variables that can help me skip this awkward step:

Besides, I also have another problem. After I have connected to the database I have to load the database schema.
Is it possible to register the server on pgAdmin as shown above and load a schema saved in a life schema.sql with Docker too? I would like to spare this second step again if possible.

I would like to get pgAdmin configured automatically like this:

(gis database with 3 tables)

All my docker project:

In the .war file:

{
	"url": "jdbc:postgresql://localhost:5432/gis",
	"user": "eb",
	"password": "password"
}

/home/gi/Dropbox/SERVER/DOCKER-LINUX/Tomcat-PosgreSQL-PostGIS-PgAdmin/postgresql-postgis/Dockerfile

FROM kartoza/postgis:9.6-2.4
LABEL Author="Nome Cognome"
EXPOSE 5432
VOLUME create pg_data

/home/gi/Dropbox/SERVER/DOCKER-LINUX/Tomcat-PosgreSQL-PostGIS-PgAdmin/tomcat/Dockerfile

FROM tomcat:9.0.22-jdk13-openjdk-oracle
LABEL Author="Nome Cognome"
EXPOSE 8080
COPY ./*.war /usr/local/tomcat/webapps/
CMD ["catalina.sh", "run"]

/home/gi/Dropbox/SERVER/DOCKER-LINUX/Tomcat-PosgreSQL-PostGIS-PgAdmin/pgadmin

FROM dpage/pgadmin4:latest
LABEL Author="Nome Cognome"
EXPOSE 80

/home/gi/Dropbox/SERVER/DOCKER-LINUX/Tomcat-PosgreSQL-PostGIS-PgAdmin/docker-compose.yaml

version: '3.8'

services:
  postgresql-postgis:
    build: ./postgresql-postgis
    image: image-postgresql-postgis-eb:v.1.0
    container_name: container-postgresql-postgis-eb
    ports:
      - 5432:5432
    volumes:
      - pg_data:/var/lib/postgresql
    environment:
      POSTGRES_USER: "eb"
      POSTGRES_PASS: "password"
      POSTGRES_DBNAME: "gis"
      ALLOW_IP_RANGE: "0.0.0.0/0"
    restart: always
    networks:
      - eb

  tomcat:
    build: ./tomcat
    image: image-tomcat-eb:v.1.0
    container_name: container-tomcat-eb
    ports:
      - 8888:8080
    depends_on:
      - postgresql-postgis
    restart: on-failure
    networks:
      - eb

  pgadmin:
    build: ./pgadmin
    image: image-pgadmin-eb:1.0
    container_name: container-pgadmin-eb
    ports:
      - 5050:80
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@gmail.com
      PGADMIN_DEFAULT_PASSWORD: password
    depends_on:
      - postgresql-postgis
    restart: always
    networks:
      - eb

volumes:
  pg_data:

networks:
  eb:

rimelek · May 17, 2022, 8:13pm

You should read more than just environment variables. Yes, it takes time, but it is necessary. Right after the variables are the configuration files

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

Right after the configuration files are the examples

Run a TLS secured container using a shared config/storage directory in /private/var/lib/pgadmin on the host, and servers pre-loaded from /tmp/servers.json on the host:

docker pull dpage/pgadmin4
docker run -p 443:443 \
    -v /private/var/lib/pgadmin:/var/lib/pgadmin \
    -v /path/to/certificate.cert:/certs/server.cert \
    -v /path/to/certificate.key:/certs/server.key \
    -v /tmp/servers.json:/pgadmin4/servers.json \
    -e 'PGADMIN_DEFAULT_EMAIL=user@domain.com' \
    -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret' \
    -e 'PGADMIN_ENABLE_TLS=True' \
    -d dpage/pgadmin4

Now you have to find how that servers.json looks like and use it in compose.

Since the format of the json file is not container specific, you go to the navigation bar and try to find a title which seems relevant: Connecting to the server

Scroll down until you see what you neeed

Server definitions (and their groups) can be exported to a JSON file and re-imported to the same or a different system to enable easy pre-configuration of pgAdmin.

Import/Export Servers

Using pgAdmin 4 GUI

Using ‘setup.py’ command line script

Exporting Servers

Importing Servers

JSON format

Now you have “JSON format” with an example.

{
    "Servers": {
        "1": {
            "Name": "Minimally Defined Server",
            "Group": "Server Group 1",
            "Port": 5432,
            "Username": "postgres",
            "Host": "localhost",
            "SSLMode": "prefer",
            "MaintenanceDB": "postgres"
        }
}

Which is actually an other question again and not related to pgadmin but only to Postgresql. @meyay suggested to you that you should read the description of Pgadmin on Docker Hub. I think he actually meant you should read the description of every image that you use. So to solve your second problem, go to the description of postgres on Docker Hub.

and read the “Initialization scripts” section.

an example compose file from me

services:
  postgres:
    container_name: postgres
    image: postgres:latest
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PW}
      - POSTGRES_DB=${POSTGRES_DB} #optional (specify default database instead of $POSTGRES_USER)
    ports:
      - "5432:5432"
    volumes:
      - ./schema.sql:/docker-entrypoint-initdb.d/schema.sql


  pgadmin:
    container_name: pgadmin
    image: dpage/pgadmin4:latest
    environment:
      - PGADMIN_DEFAULT_EMAIL=${PGADMIN_MAIL}
      - PGADMIN_DEFAULT_PASSWORD=${PGADMIN_PW}
    ports:
      - "5050:80"
    volumes:
      - ./servers.json:/pgadmin4/servers.json

While I was typing this message, I realized that you use kartoza/postgis but that has a description too, which is very similar to the official one. I let you try the configuration with that

federicogalimberti · May 19, 2022, 4:50pm

Thank you so much. I tried your script and it worked the first time. With the .json file you can also set “Name” and “Group”. This is more than what I expected to do. Docker is great but you are great too!
Unfortunately, I have a hard time writing Docker code because I’m a beginner.

federicogalimberti · May 19, 2022, 9:20pm

Sorry if I break the boxes again but I can’t understand some things:

“./servers.json” is the location of the servers.json file on the host (Ubuntu 22.04) while “/pgadmin4/servers.json” is the location of the .json file in the image (image-pgadmin-eb: 1.0), right?
“./schema.sql:” is the location of the schema.sql file on the host (Ubuntu 22.04) while “/docker-entrypoint-initdb.d/schema.sql” is the location of the .sql file in the image (postgres: latest), right?
Before I wrote “- pg_data: / var / lib / postgresql” and in the Dockerfile “VOLUME create pg_data”. By not specifying a host address, Docker autonomously chooses a host address (var / lib / docker / volumes) where to copy the content of the data contained in the image “image-postgresql-postgis-eb: v.1.0” and at the address "/ var / lib / postgresql ", right?
What exactly does this code in the .json file do?
“MaintenanceDB”: “postgres”
I am a beginner and I cannot understand the usefulness of this line. Could you explain to me in a simple way? I read something online but I just can’t understand. I interact with the DB with simple queries, I write simple codes. If I change “postgres” I can no longer connect to “Server 1”, the software constantly asks me for the password.

Thanks

rimelek · May 20, 2022, 11:29am

federicogalimberti:

/pgadmin4/servers.json” is the location of the .json file in the image

In the container, but yes. It is important to know the difference. ou can have data in the container which was not in the image (template)
federicogalimberti:

the location of the .sql file in the image

Again, container.
federicogalimberti:

Dockerfile “VOLUME create pg_data”

It doesn’t make sense. I thought I mentioned it somewhere that it should be a path, not a command, but let’s not dive into the details here. To the rest of this question, yes.
federicogalimberti:

What exactly does this code in the .json file do?

federicogalimberti:

Sorry if I break the boxes again

Well, I indeed hope we will not cover every topic not even related to Docker here, but this is database specific and I am not a postgres user to give you a precise answer so again, I used Google to search for “postgresql maintenance db”: postgresql - What is the Maintenance DB asked by pgAdmin? - Database Administrators Stack Exchange

federicogalimberti:

I interact with the DB with simple queries, I write simple codes. If I change “postgres” I can no longer connect
to “Server 1”, the software constantly asks me for the password.

Probably because you inicialized the database with the access rights set to the postgres db, but as I wrote, I don’t use postgres frequently.

We can help you with Docker related issues but you should use other forums as well.

Please, share what you have read next time so we can focus on that and not trying to search for you the same again.

federicogalimberti · May 21, 2022, 10:25am

thanks so much for everything