Wikis - Page

pgAdmin to access SQL on SMG (non-appliance)

0 Likes

pgAdmin to access SQL on SMG (non-appliance)

 

SMG will change and has changed its outfit. SMG will move from appliance based version to a rpm based version. I do not discuss why it happens but I want to bring back a utility which I used many times to access the sql database in the background.

SMG uses a PostgreSQL database to store configuration and daily data in the background. The appliance offered phpPgAdmin to access and maintain the database. There you are able to search for values, to check settings, to create automatic reports or to shrink databases in case of troubles.

 

Now I  will use a similar SQL tool called pgAdmin to access my sql database. I use a docker container because it is easy to roll out and bring applications to life. And it is easy to throw it away if there is no need.

Intro

My description here is based on Sles15SP5. However I think it is easy to adapt it for OpenSUSE too. Instead of a long docker command I use docker-compose and a yml file. It is easier to explain and to change.

How to get docker

In my yast configuration environment I add extension “Containers Module”.

The next steps are easy. I install docker, enable docker service and start it:

zypper install docker

systemctl enable docker.service

systemctl start  docker.service

 

Because preferring docker-compose I will long for it.

zypper install docker-compose

Find and get pgadmin container

Let’s use the “docker advantage”!

Someone was interested in pgAdmin, did some researches, played around with settings and created a container which contains all ingredients to get a running pgAdmin application. There are several ideas and ways for this software package.
I have selected dpage/pgadmin4 (https://hub.docker.com/r/dpage/pgadmin4/). You can download this prepared container with ‘docker pull dpage/pgadmin4:latest’.

However, I mentioned that I go for docker-compose. At https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html you will find a helpful docker-compose.yml file which can be downloaded and copied to your sles server. I have created an own directory for this.

                                                                                                                                                        

If you want to test it, change into the directory where this yml file is located and run “docker compose up’.  If your container is starting then stop it with ‘Ctrl C’ or ‘docker compose down’.

Make Container phpPgAdmin run

This docker-compose.yml file is a first approach and I have changed some settings. Let’s start with the first lines.

version: '3.4'

 

services:

  pgadmin:

    image: dpage/pgadmin4

    container_name: pgadmin

    restart: unless-stopped

    ports:

      - 8081:80

    volumes:

      - ./pgadmin/servers.json:/pgadmin4/servers.json

    env_file:

      - ./env


Adjust yellow marked values for your purposes.

You see that I use port 8081 to access pgAdmin. Adjust these values to your environment but take care that these ports are not occupied by other services.     

    networks:

      php-stack:

            ipv4_address: 172.18.0.2

 

networks:

  php-stack:

    ipam:

        config:

          - subnet: 172.18.0.0/24

    #    name: php-stack

I added some network settings which I had to do because postgresql does not allow general access from all ip addresses. If I use a defined ip address for my docker environment then it is easier to create exceptions in postgresql.

In this case developers offer a very comfortable way to configure a personal environment. Two files – env and servers.json – will help to configure a container from outside.

Here is my env file:

POSTGRES_HOST=10.1.1.184

POSTGRES_PORT=5432

POSTGRES_DATABASE=postgres

POSTGRES_USER=postgres

POSTGRES_PASSWORD=postgres

ALLOW_EMPTY_PASSWORD=yes

PGADMIN_DEFAULT_EMAIL=admin@demo.com

PGADMIN_DEFAULT_PASSWORD=oh

You can set how to connect to your host and which credentials will be used. There are some more settings how to login to pgAdmin. In my case ‘PGADMIN..’ values.

The second configuration file is servers.json which is stored in subdirectory pgadmin.

{

  "Servers": {

      "1": {

          "Name": "SMG",

          "Group": "Servers",

          "Host": "10.1.1.184",

          "Port": 5432,

          "MaintenanceDB": "postgres",

          "Username": "postgres",

          "SSLMode": "prefer"

      }

  }

}

You see that you can configure a lot more sql servers to access.

PostgreSQL Configuration

My work is almost done. Now I have to change some postgresql settings to make access possible.

These files are stored in /var/lib/pgsql/data/

I start with pg_hba.conf. You will find some lines similar to this:

The yellow marked parts are important. I allow postgres access to an ip range 172.18.0.0. Do you remember that I used this range for my container in docker-compose.yml?

The second file I had to change is postgres.conf. Postgresql will listen on which ip address – I keep it open.

Done! Please restart you postgresql!

Start your pgAdmin!

Change to your directory where docker-compose.yml is stored. Try to start the new container.

docker compose up

Hopefully your container will start in interactive mode. If you want to run it in the background then use:

docker compose up –detach

Access pgAdmin with server-ip-address:8081 and you should end up here:

Summary

Because of docker you can bring back SQL access in an easy way. Although it seems to be a long paper only a few settings have to be adjusted from case to case. Go for it!

My approach can be used for other postgreSQL environments too.

Labels:

How To-Best Practice
Comment List
Related
Recommended