Install PGAdmin

Create SuperUser for Admin:

su - postgres

createuser --interactive --pwprompt

APT:

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
apt-get update
apt-get install pgadmin4

Leave user:

postgres@localhost
password

Login and create new user delete startup user.

Apache Conf:

/etc/apache2/conf-enabled/pgadmin4.conf

Visit:

URL/pgadmin4

Docker:

This setup maps config dir to local host (/opt/pgadmin) and uses port 5050 for network traffic. I use systemd to manage containers as well.

Two Notes: I couldn't get domain/foo to work, so it must be accessed via domain.com or domain.com:5050. I also use NGinX proxy to remove the need for ":5050".

PostgreSQL Changes to Allow Remote Network Auth:

https://www.pgadmin.org/docs/pgadmin4/development/connect_error.html

cat << 'EOF' >>/etc/postgresql/*/main/pg_hba.conf
host    all         all         172.17.0.0/16          md5
EOF
cat /etc/postgresql/*/main/pg_hba.conf | grep 172
sed -i '/listen_addresses/s/^#//g' /etc/postgresql/*/main/postgresql.conf 
sed -i -e 's/localhost/*/g' /etc/postgresql/*/main/postgresql.conf
cat /etc/postgresql/*/main/postgresql.conf | grep listen
systemctl restart postgresql

https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

docker pull dpage/pgadmin4

Docker-Run:

docker run -p 5050:80 \
    -e 'PGADMIN_DEFAULT_EMAIL=user@domain.com' \
    -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret' \
    -e 'PGADMIN_CONFIG_ENHANCED_COOKIE_PROTECTION=True' \
    -e 'PGADMIN_CONFIG_LOGIN_BANNER="Authorised users only!"' \
    -e 'PGADMIN_CONFIG_CONSOLE_LOG_LEVEL=10' \
    --name "pgadmin" \
    -v /opt/pgadmin:/var/lib/pgadmin \
    -e 'GUNICORN_ACCESS_LOGFILE=/var/lib/pgadmin/pgadmin.log' \
    -d dpage/pgadmin4

Docker-Compose:

WD=/opt/pgadmin
mkdir -p $WD/{mnt,setup}
cd $WD/setup
cat << 'EOF' >docker-compose.yaml
version: '3.7'

services:
 pgadmin:
    container_name: pgadmin
    hostname: pgadmin
    image: dpage/pgadmin4
    ports:
      - '5050:80'
    volumes:
      - type: bind
        source: /opt/pgadmin/conf/
        target: /var/lib/pgadmin
      - type: bind
        source: /opt/pgadmin/mnt/
        target: /mnt

    environment:
      - 'TZ=America/Whitehorse'
      - 'PGADMIN_DEFAULT_EMAIL=user@gmail.com'
      - 'PGADMIN_DEFAULT_PASSWORD=SuperSecret'
      - 'PGADMIN_CONFIG_ENHANCED_COOKIE_PROTECTION=True'
      - 'PGADMIN_CONFIG_LOGIN_BANNER="Authorised users only!"'
      - 'PGADMIN_CONFIG_CONSOLE_LOG_LEVEL=10'
      - 'GUNICORN_ACCESS_LOGFILE=/var/lib/pgadmin/pgadmin.log'

EOF
chmod +x docker-compose.yaml

I had permission issue with volume....

chmod 777 /opt/pgadmin

NginX:

#https://www.pgadmin.org/docs/pgadmin4/latest/
#Note: I was unable to get /foo/ to work, so I use subdomain.domain.tld
#https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html#reverse-proxying

##GLOBAL SSL
ssl_certificate /etc/ssl/foo.crt;
ssl_certificate_key /etc/ssl/foo.key;

##PGAdmin
server {
       listen         80;
       server_name    domain.tld;

      return         301 https://$server_name/;
}

server {
        listen 443 ssl;
        server_name domain.tld;

    location / {
        proxy_pass http://localhost:5050/;
        proxy_set_header X-Scheme $scheme;
        proxy_set_header Host $host;
        proxy_redirect off;
    }
}

SystemD:

Docker-Run:

cat << 'EOL' >/lib/systemd/system/pgadmin.service
[Unit]
Description=Docker_Description
Requires=docker.service network-online.target

[Service]
Restart=on-abnormal
ExecStart=/usr/bin/docker start -a pgadmin
ExecStop=/usr/bin/docker stop -t 2 pgadmin

[Install]
WantedBy=multi-user.target
EOL
systemctl enable pgadmin
systemctl start pgadmin
systemctl status pgadmin

Docker-Compose:

WD=/opt/pgadmin/setup
cat << EOF >$WD/pgadmin.service.setup.sh
cat << EOL >/lib/systemd/system/pgadmin.service
[Unit]
Description=pgadmin_Docker
Requires=docker.service network-online.target

[Service]

Restart=on-abnormal
ExecStart=/usr/bin/docker-compose --project-name pgadmin --project-directory $WD -f $WD/docker-compose.yaml up
ExecStop=/usr/bin/docker-compose --project-name pgadmin --project-directory $WD -f $WD/docker-compose.yaml stop

[Install]
WantedBy=multi-user.target
EOL
systemctl enable pgadmin
systemctl start pgadmin
EOF
chmod +x $WD/pgadmin.service.setup.sh
$WD/pgadmin.service.setup.sh

Page tree

Install PGAdmin