Graphical UI for PostgreSQL?

#1

Is there any way to connect a graphical database admin tool such as pgAdmin to the PostgreSQL database in a standard Corteza installation (i.e. with the database in a Docker container)?

#2

You can add a pgAdmin to your corteza docker compose file :

  pgadmin:
    image: dpage/pgadmin4
    restart: always
    networks: [ proxy, internal ]
    environment:
      PGADMIN_DEFAULT_EMAIL: xxxx
      PGADMIN_DEFAULT_PASSWORD: yyyy
    ports:
      - "15051:80"

pgAdmin is going to be accessible on the port 15051 of your server.

internal is your docker corteza internal network
proxy is external network

if not already defined :

networks:
  internal: {}
  proxy: { external: true }
#3

OK, I may try that. Is there any way to connect directly to the database from a desktop pgAdmin4 client (vice a web client), such as via SSH?

#4

You just to need to access the PostgreSQL server from the computer on which you have pgAdmin installed.
You have many possibilities :

  • expose your PostgreSQL server to internet (risky)
  • create a tunnel (VPN) to access your docker local network

I think with the two last options it’s better to define strict network firewall rules and also fail2ban on the server.

#5

Assuming your .env contains:
DB_DSN=postgres://corteza:corteza@db:5432/corteza?sslmode=disable

and your docker-compose.yaml has the following:
db:
# PostgreSQL Database
# See https://hub.docker.com/_/postgres for details
# Support for postgres 13, 14 and 15 is available in the latest version of Corteza
image: postgres:15
# networks: [ internal ]
restart: always
healthcheck: { test: [“CMD-SHELL”, “pg_isready -U corteza”], interval: 10s, timeout: 5s, retries: 5 }
volumes:
- “dbdata1:/var/lib/postgresql/data”
environment:
# Warning: these are values that are only used on 1st start
# if you want to change it later, you need to do that
# manually inside db container
POSTGRES_USER: corteza
POSTGRES_PASSWORD: corteza
ports:
- “5432:5432”

having this in pgAdmin should get you goining.

image
image699×549 21.5 KB

#6

This:
volumes: - “dbdata1:/var/lib/postgresql/data”

wasn’t in my original docker-compose.yaml file. Do I need it?

Also, I’m hosting Corteza on a cloud server, but using a desktop pgAdmin client. Will that work?

#7

I’m very new to most of the technologies used, but I will try and explain it as much as I can:
Volumes statement allows you to maintain the actual Postgresql DB outside the docker container. In other words, the docker engine will create a volume/folder on the hosting machine named “dbdata1” and map it to a directory in the docker container “/var/lib/postgresql/data”. Any data generated by the container and to be placed in the /var/lib/postgresql/data folder will actually be saved in the dbdata1 volume. After many experiments, I was able to use this statement to map the folder where the Corteza server container use for all uploaded attachments, icons, images …etc by the Corteza users to Azure Blob Storage.
I think you first should try to link pgAdmin to a local postgresql container DB. then use the same settings “yaml” for the cloud hosted container, if it doesn’t work, then it will be a matter of firewall settings on your cloud service that you should setup, assuming you are using the correct IP and port addresses.

#8

So, theoretically, If I set up pgAdmin to hit the public IP address for the server and port 5432, I should be able to access the Corteza database?

#9

Yes, but try it locally first. It should take a minute to do so. With the correct .env and docker-compose.yaml, Use docker-compose up -d command and see if you are able to connect pgAdmin to the local docker db container.