§2023-04-05

• How To Install And Configure pgAdmin 4 in Server Mode on Ubuntu 22.04@digitalocean

---

試作機器： h2Jammy.yushei.net

1. Compile python from source

$ wget https://www.python.org/ftp/python/3.11.2/Python-3.11.2.tar.xz
$ cd ..
$ tar xvf src/Python-3.11.2.tar.xz 
$ cd Python-3.11.2
$ ./configure --with-system-ffi --with-computed-gotos --enable-loadable-sqlite-extensions
$ ./configure --enable-optimizations
$ time make -j 6 // ${nproc} + 2
real    7m24.380s
user    17m37.166s
sys     0m59.415s

2. setup environment control

$ ./python --version
Python 3.11.2
./python -m venv ~/PYTHON-3.11.2
$ cd
$ source PYTHON-3.11.2/bin/activate
(PYTHON-3.11.2) alexlai@h2Jammy:~$ $ echo $PATH
/home/alexlai/PYTHON-3.11.2/bin:/usr/local/pgsql/bin:/home/alexlai/.cargo/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin

以下、　under env control

3. Install dependencies

$ sudo apt update
$ sudo apt install libgmp3-dev libpq-dev
$ sudo apt auto-remove

4. create a few directories where pgAdmin will store its sessions data, storage data, and logs:

sudo mkdir -p /var/lib/pgadmin4/sessions
sudo mkdir /var/lib/pgadmin4/storage
sudo mkdir /var/log/pgadmin4
sudo mkdir /var/lib/pgadmin4/azurecredentialcache


sudo chown -R alexlai:alexlai /var/lib/pgadmin4
sudo chown -R alexlai:alexlai /var/log/pgadmin4
sudo chown -R alexlai:alexlai /var/lib/pgadmin4/azurecredentialcache

5. Using pip to install software

python -m pip install -U pip
Successfully installed pip-23.0.1

$ pip --version
pip 23.0.1 from /home/alexlai/PYTHON-3.11.2/lib/python3.11/site-packages/pip (python 3.11)

$ pip install pgadmin4
$ pip install gunicorn

4. Create ~/PYTHON-3.11.2/lib/python3.11/site-packages/pgadmin4/config_local.py as, <-- new

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
SERVER_MODE = True
AZURE_CREDENTIAL_CACHE_DIR = '/var/lib/pgadmin4/azurecredentialcache' 

• Here are what these five directives do:
  • LOG_FILE: this defines the file in which pgAdmin’s logs will be stored.
  • SQLITE_PATH: pgAdmin stores user-related data in an SQLite database, and this directive points the pgAdmin software to this configuration database. Because this file is located under the persistent directory /var/lib/pgadmin4/, your user data will not be lost after you upgrade.
  • SESSION_DB_PATH: specifies which directory will be used to store session data.
  • STORAGE_DIR: defines where pgAdmin will store other data, like backups and security certificates.
  • SERVER_MODE: setting this directive to True tells pgAdmin to run in Server mode, as opposed to Desktop mode.

5. With those configurations in place, run the pgAdmin setup script to set your login credentials:

$ python ~/PYTHON-3.11.2/lib/python3.11/site-packages/pgadmin4/setup.py
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: alexlai@munetaka.me
Password: TxxxxYxxLxx#1nnn 
Retype password:
pgAdmin 4 - Application Initialisation
======================================

6. Modify

$ diff  ~/PYTHON-3.11.2/lib/python3.11/site-packages/pgadmin4/config.py ~/PYTHON-3.11.2/lib/python3.11/site-packages/pgadmin4/config.py.ori 
138c138
< DEFAULT_SERVER = '0.0.0.0'
---
> DEFAULT_SERVER = '127.0.0.1'
(PYTHON-3.11.2) alexlai@h2Jammy:~

$ python ~/PYTHON-3.11.2/lib/python3.11/site-packages/pgadmin4/pgAdmin4.py
Starting pgAdmin 4. Please navigate to http://0.0.0.0:5050 in your browser.
2023-04-05 14:27:23,207: WARNING        werkzeug:       WebSocket transport not available. Install simple-websocket for improved performance.
 * Serving Flask app 'pgadmin' (lazy loading)
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off

7. Or run with gunicorn

$ gunicorn -w 4 -b 0.0.0.0 --bind unix:/tmp/pgadmin4.sock --workers=1 --threads=25 --chdir ~/PYTHON-3.11.2/lib/python3.11/site-packages/pgadmin4 pgAdmin4:app
[2023-04-05 14:31:24 +0800] [2583] [INFO] Starting gunicorn 20.1.0
[2023-04-05 14:31:24 +0800] [2583] [INFO] Listening at: http://0.0.0.0:8000,unix:/tmp/pgadmin4.sock (2583)
[2023-04-05 14:31:24 +0800] [2583] [INFO] Using worker: gthread
[2023-04-05 14:31:24 +0800] [2585] [INFO] Booting worker with pid: 2585

8. pgAdmin4.service as,

[Unit]
Description=pgAdmin4.service
After=network.target

[Service]
Type=simple
# Another Type: forking
User=alexlai
WorkingDirectory=/home/alexlai/PYTHON-3.11.2/bin
ExecStart=/home/alexlai/PYTHON-3.11.2/bin/gunicorn -w 4 -b 0.0.0.0:5050 --bind unix:/tmp/pgadmin4.sock --workers=1 --threads=25 --chdir /home/alexlai/PYTHON-3.11.2/lib/python3.11/site-packages/pgadmin4 pgAdmin4:app
Restart=on-failure
# Other restart options: always, on-abort, etc

# The install section is needed to use
# `systemctl enable` to start on boot
# For a user service that you want to enable
# and start automatically, use `default.target`
# For system level services, use `multi-user.target`
[Install]
WantedBy=multi-user.target

---

以下: Kept for reference, on h2Jammy port 80 has been redirected to 8080. 6. Configuring Nginx

• the original setup

$ cd /etc/nginx
(PYTHON-3.11.2) alexlai@h2Jammy:/etc/nginx$ ls -l
total 48
drwxr-xr-x 2 root root    6 十一 10 14:39 conf.d
-rw-r--r-- 1 root root 1125  七  27  2022 fastcgi.conf
-rw-r--r-- 1 root root 1055  七  27  2022 fastcgi_params
-rw-r--r-- 1 root root 2837  七  27  2022 koi-utf
-rw-r--r-- 1 root root 2223  七  27  2022 koi-win
-rw-r--r-- 1 root root 3957  八   2  2022 mime.types
drwxr-xr-x 2 root root    6 十一 10 14:39 modules-available
drwxr-xr-x 2 root root 4096 十二 18 08:00 modules-enabled
-rw-r--r-- 1 root root 1447  七  27  2022 nginx.conf
-rw-r--r-- 1 root root 1447 十二 18 08:06 nginx.conf.ori
-rw-r--r-- 1 root root  180  七  27  2022 proxy_params
-rw-r--r-- 1 root root  636  七  27  2022 scgi_params
drwxr-xr-x 2 root root  121 十二 20 09:02 sites-available
drwxr-xr-x 2 root root   81 十二 20 09:03 sites-enabled
drwxr-xr-x 2 root root   51 十二 18 08:00 snippets
-rw-r--r-- 1 root root  664  七  27  2022 uwsgi_params
-rw-r--r-- 1 root root 3071  七  27  2022 win-utf

$ sudo cp -v nginx.conf nginx.conf.ori
'nginx.conf' -> 'nginx.conf.ori'

alexlai@h2Jammy:/etc/nginx/modules-enabled$ ls -l
total 0
lrwxrwxrwx 1 root root 55 十二 18 08:00 50-mod-http-geoip2.conf -> /usr/share/nginx/modules-available/mod-http-geoip2.conf
lrwxrwxrwx 1 root root 61 十二 18 08:00 50-mod-http-image-filter.conf -> /usr/share/nginx/modules-available/mod-http-image-filter.conf
lrwxrwxrwx 1 root root 60 十二 18 08:00 50-mod-http-xslt-filter.conf -> /usr/share/nginx/modules-available/mod-http-xslt-filter.conf
lrwxrwxrwx 1 root root 48 十二 18 08:00 50-mod-mail.conf -> /usr/share/nginx/modules-available/mod-mail.conf
lrwxrwxrwx 1 root root 50 十二 18 08:00 50-mod-stream.conf -> /usr/share/nginx/modules-available/mod-stream.conf
lrwxrwxrwx 1 root root 57 十二 18 08:00 70-mod-stream-geoip2.conf -> /usr/share/nginx/modules-available/mod-stream-geoip2.conf

§2022-12-17

• Running DSM 7.x

---

1. The initial nginx.conf is

alexlai@munetakaNas:/etc/nginx$ nginx -v
nginx version: nginx/1.20.1 <-- current release version 1.29.0

alexlai@munetakaNas:/etc/nginx$ ls -l
total 56
lrwxrwxrwx 1 root root   27 Dec 12 05:00 conf.d -> /usr/local/etc/nginx/conf.d
-rw-r--r-- 1 root root 1077 Oct  7  2021 fastcgi.conf
-rw-r--r-- 1 root root 1007 Oct  7  2021 fastcgi_params
-rw-r--r-- 1 root root 2837 Oct  7  2021 koi-utf
-rw-r--r-- 1 root root 2223 Oct  7  2021 koi-win
-rw-r--r-- 1 root root 5318 Oct  7  2021 mime.types
-rw-r--r-- 1 root root 9422 Dec 12 05:42 nginx.conf
-rw-r--r-- 1 root root 3639 Jun 14  2022 nginx.conf.default
lrwxrwxrwx 1 root root   21 Dec 12 05:42 nginx.conf.run -> /etc/nginx/nginx.conf
-rw-r--r-- 1 root root  277 Jun 14  2022 proxy.conf
-rw-r--r-- 1 root root 1204 Oct  7  2021 scgi_params
lrwxrwxrwx 1 root root   34 Dec 12 05:00 sites-enabled -> /usr/local/etc/nginx/sites-enabled
-rw-r--r-- 1 root root  664 Oct  7  2021 uwsgi_params
-rw-r--r-- 1 root root 3610 Oct  7  2021 win-utf

alexlai@munetakaNas:/etc/nginx$ cat nginx.conf
# Copyright (c) 2000-2017 Synology Inc. All rights reserved.

worker_processes        auto;
#worker_cpu_affinity    auto;
worker_rlimit_nofile    65535;

include conf.d/main.*.conf;

events {
    use             epoll;
    multi_accept    on;
    accept_mutex    off;
    worker_connections 1024;

    include conf.d/events.*.conf;
}

http {
    include         mime.types;
    default_type    application/octet-stream;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
        '$status $body_bytes_sent "$http_referer" '
        '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  off;
    #access_log syslog:server=unix:/dev/log,facility=local7,tag=nginx_access,nohostname main;
    error_log   syslog:server=unix:/dev/log,facility=local7,tag=nginx_error,nohostname error;

    tcp_nopush  on;
    tcp_nodelay on;

    sendfile        on;
    server_tokens   off;

    proxy_request_buffering     off;
    fastcgi_request_buffering   off;
    scgi_request_buffering      off;

    proxy_buffering     off;
    fastcgi_buffering   off;
    scgi_buffering      off;

    resolver_timeout              5s;
    client_header_timeout         10s;
    client_body_timeout           60s;
    send_timeout                  60s;
    keepalive_timeout             65s 20s;
    client_max_body_size          0;
    http2_max_client_body_buffer_size 8m;
    server_names_hash_max_size    8192;
    server_names_hash_bucket_size 128;

    include /usr/syno/etc/www/certificate/system_default/cert.conf*;
    include /usr/syno/etc/security-profile/tls-profile/config/dsm.conf*;
    ssl_prefer_server_ciphers on;

    ssl_session_tickets       off;
    ssl_session_cache         shared:SSL:1m;
    ssl_session_timeout       3600s;

    ssl_early_data            off;

    real_ip_header            X-Forwarded-For;
    real_ip_recursive         on;
    set_real_ip_from          127.0.0.1;

    include     /var/tmp/nginx/trusted_proxy/*.conf;

    default_listen_option ssl_http2;

    map $http_upgrade $connection_upgrade {
        default upgrade;
        ''      close;
    }

    server_tag     "nginx";

    gzip_disable    "msie6";
    gzip_min_length 1000;
    gzip_types      text/plain text/css application/javascript application/json;
    gzip_vary       on;
    gzip_static     on;

    open_file_cache          max=1000 inactive=60s;
    open_file_cache_valid    3s;
    open_file_cache_min_uses 2;
    open_file_cache_errors   on;

    upstream synoscgi {
        server unix:/run/synoscgi.sock;
    }

    upstream synoscgi.sock {
        server unix:/run/synoscgi_socket.sock;
    }

    index index.html index.htm index.php;

    server {
        listen 5000 default_server;
        listen [::]:5000 default_server;

        server_name _;

        gzip on;

        include conf.d/alias.*.conf;
        root /usr/syno/synoman;
        index index.cgi;

        ignore_invalid_headers off;

        include /usr/syno/share/nginx/conf.d/dsm.*.conf;
        include conf.d/dsm.*.conf;

        location = / {
            try_files $uri /index.cgi$is_args$query_string;
        }

        location ~ ^/volume(?:X|USB|SATA|Gluster)?\d+/ {
            internal;

            root /;

            open_file_cache off;

            include conf.d/x-accel.*.conf;
        }

        location ~ /webman/modules/(PersonalSettings|ExternalDevices|FileBrowser)/index_ds.php$ {
            alias /usr/syno/share/OAuth/index_ds.php;
            default_type text/html;
        }

        location ~ \.cgi {
            include     scgi_params;
            scgi_pass   synoscgi;

            scgi_read_timeout   3600s;
        }

        location ~ /synoscgi.sock/socket.io/ {
            proxy_read_timeout   3600s;
            include proxy.conf;
            rewrite /synoscgi.sock/(.*)$ /$1 break;
            proxy_set_header Connection $connection_upgrade;
            proxy_pass http://synoscgi.sock;

        }

        error_page 403 404 500 502 503 504 /dsm_error_page;

        location /dsm_error_page {
            internal;
            root /usr/syno/share/nginx;
            rewrite (.*) /error.html break;
            allow all;
        }

        location ~ ^/webman/modules/Indexer/ {
            deny all;
        }

        location ~ ^/webapi/lib/ {
            deny all;
        }

        location ~ ^/webapi/(:?(:?.*)\.lib|(:?.*)\.api|(:?.*)\.auth|lib.def)$ {
            deny all;
        }

        location ~ /\. { access_log off; log_not_found off; deny all; }

        location ~* \.(?:js|css|png|jpg|gif|ico)$ {
            access_log off;
            log_not_found off;
        }

        location = /favicon.ico {
            access_log off;
            log_not_found off;
        }

        location = /robots.txt {
            allow all;
            access_log off;
            log_not_found off;
        }

    }

    server {
        listen 5001 default_server ssl;
        listen [::]:5001 default_server ssl;

        server_name _;

        include conf.d/ssl.*.conf;

        include conf.d/alias.*.conf;
        root /usr/syno/synoman;
        index index.cgi;

        ignore_invalid_headers off;

        include /usr/syno/share/nginx/conf.d/dsm.*.conf;
        include conf.d/dsm.*.conf;

        location = / {
            try_files $uri /index.cgi$is_args$query_string;
        }

        location ~ ^/volume(?:X|USB|SATA|Gluster)?\d+/ {
            internal;

            root /;

            open_file_cache off;

            include conf.d/x-accel.*.conf;
        }

        location ~ /webman/modules/(PersonalSettings|ExternalDevices|FileBrowser)/index_ds.php$ {
            alias /usr/syno/share/OAuth/index_ds.php;
            default_type text/html;
        }

        location ~ \.cgi {
            include     scgi_params;
            scgi_pass   synoscgi;

            scgi_read_timeout   3600s;
        }

        location ~ /synoscgi.sock/socket.io/ {
            proxy_read_timeout   3600s;
            include proxy.conf;
            rewrite /synoscgi.sock/(.*)$ /$1 break;
            proxy_set_header Connection $connection_upgrade;
            proxy_pass http://synoscgi.sock;

        }

        error_page 403 404 500 502 503 504 /dsm_error_page;

        location /dsm_error_page {
            internal;
            root /usr/syno/share/nginx;
            rewrite (.*) /error.html break;
            allow all;
        }

        location ~ ^/webman/modules/Indexer/ {
            deny all;
        }

        location ~ ^/webapi/lib/ {
            deny all;
        }

        location ~ ^/webapi/(:?(:?.*)\.lib|(:?.*)\.api|(:?.*)\.auth|lib.def)$ {
            deny all;
        }

        location ~ /\. { access_log off; log_not_found off; deny all; }

        location ~* \.(?:js|css|png|jpg|gif|ico)$ {
            access_log off;
            log_not_found off;
        }

        location = /favicon.ico {
            access_log off;
            log_not_found off;
        }

        location = /robots.txt {
            allow all;
            access_log off;
            log_not_found off;
        }

    }

    server {
        listen 80 default_server;
        listen [::]:80 default_server;

        server_name _;
        gzip on;

        location ~ ^/volume(?:X|USB|SATA|Gluster)?\d+/ {
            internal;

            root /;

            open_file_cache off;

            include conf.d/x-accel.*.conf;
        }

        include conf.d/alias.*.conf;
        include /usr/syno/share/nginx/conf.d/www.*.conf;
        include conf.d/www.*.conf;

        location = /webdefault/images/logo.jpg {
            alias /usr/syno/share/nginx/logo.jpg;
        }

        error_page 403 404 500 502 503 504 /dsm_error_page;

        location /dsm_error_page {
            internal;
            root /usr/syno/share/nginx;
            rewrite (.*) /error.html break;
            allow all;
        }

        location ^~ /.well-known/acme-challenge {
            root /var/lib/letsencrypt;
            default_type text/plain;
        }

        include conf.d/.location.webstation.conf*;

        location / {
            etag off;
            root /var/tmp/nginx/html;
            rewrite (.*) /redirect.html break;
        }

    }

    server {
        listen 443 default_server ssl;
        listen [::]:443 default_server ssl;
        server_name _;

        location ~ ^/volume(?:X|USB|SATA|Gluster)?\d+/ {
            internal;

            root /;

            open_file_cache off;

            include conf.d/x-accel.*.conf;
        }

        include conf.d/alias.*.conf;
        include /usr/syno/share/nginx/conf.d/www.*.conf;
        include conf.d/www.*.conf;

        location = /webdefault/images/logo.jpg {
            alias /usr/syno/share/nginx/logo.jpg;
        }

        error_page 403 404 500 502 503 504 /dsm_error_page;

        location /dsm_error_page {
            internal;
            root /usr/syno/share/nginx;
            rewrite (.*) /error.html break;
            allow all;
        }

        location ^~ /.well-known/acme-challenge {
            root /var/lib/letsencrypt;
            default_type text/plain;
        }

        include conf.d/.location.webstation.conf*;

        location / {
            etag off;
            root /var/tmp/nginx/html;
            rewrite (.*) /redirect.html break;
        }

    }

    include conf.d/http.*.conf;
    include sites-enabled/*;
}

Return to Top