PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.
Lets install PostgreSQL 11
vi /etc/centminmod/custom_config.inc
Enter
POSTGRESQL=y
LETSENCRYPT_DETECT='y'
Upgrade php using centmin.sh option 5
cmdir
./centmin.sh
This will install PostgreSQL 11
systemctl status postgresql-11
psql --version
psql (PostgreSQL) 11.7
Lets install pgAdmin4
pip3 install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.20/pip/pgadmin4-4.20-py2.py3-none-any.whl
Setup config file with relative paths
vi /usr/local/lib/python3.6/site-packages/pgadmin4/config_local.py
Add
LOG_FILE = '/var/log/pgadmin/pgadmin.log'
SQLITE_PATH = '/var/lib/pgadmin/pgadmin.db'
SESSION_DB_PATH = '/var/lib/pgadmin/sessions'
STORAGE_DIR = '/var/lib/pgadmin/storage'
SERVER_MODE = True
ALLOW_SAVE_PASSWORD = True
Execute setup.py to configure login for pgAdmin
python3 /usr/local/lib/python3.6/site-packages/pgadmin4/setup.py
Set correct permission to the files
chown nginx:nginx /var/lib/pgadmin/pgadmin.db
chown -R nginx:nginx /var/log/pgadmin/
chown -R nginx:nginx /var/lib/pgadmin/
Create pgadmin.ini file
mkdir -p /home/nginx/domains/uwsgi
vi /home/nginx/domains/uwsgi/pgadmin.ini
Add
[uwsgi]
uid = nginx
gid = nginx
chdir = /usr/local/lib/python3.6/site-packages/pgadmin4/
wsgi-file = /usr/local/lib/python3.6/site-packages/pgadmin4/pgAdmin4.wsgi
plugins = python3
master = true
processes = 1
socket = /run/uwsgi/pgadmin.sock
chown-socket = nginx:nginx
chmod-socket = 660
vacuum = true
Create uwsgi systemd service
vi /etc/systemd/system/uwsgi.service
Add
[Unit]
Description=uWSGI Emperor service
[Service]
ExecStartPre=/usr/bin/bash -c 'mkdir -p /run/uwsgi; chown nginx:nginx /run/uwsgi'
ExecStart=/usr/local/bin/uwsgi --emperor /home/nginx/domains/uwsgi
Restart=always
KillSignal=SIGQUIT
Type=notify
NotifyAccess=all
[Install]
WantedBy=multi-user.target
Start and enable uwsgi services
systemctl start uwsgi
systemctl enable uwsgi
Lets configure Nginx as reverse proxy for pgadmin4
First create domain name with centmin.sh using option 2
cmdir
./centmin.sh
Copy staticfiles.conf to staticfiles-pgadmin4.conf
cat /usr/local/nginx/conf/staticfiles.conf > /usr/local/nginx/conf/staticfiles-pgadmin4.conf
Open staticfiles-pgadmin4.conf
vi /usr/local/nginx/conf/staticfiles-pgadmin4.conf
Find
location ~ /.well-known { location ~ /.well-known/acme-challenge/(.*) { more_set_headers "Content-Type: text/plain"; } }
Delete everything after this line and save file
Open your vhost file for the domain name your created. In my example I created site.bullten.work
vi /usr/local/nginx/conf/conf.d/site.bullten.work.ssl.conf
on to add the line below
upstream pgadmin {
server unix:///run/uwsgi/pgadmin.sock;
}
So it will look like
Find
location / {
Under it Add
include uwsgi_params;
uwsgi_pass pgadmin;
So it will look like as shown in the image below
Also Find
include /usr/local/nginx/conf/staticfiles.conf;
Replace With
include /usr/local/nginx/conf/staticfiles-pgadmin4.conf
Save the file and restart nginx
nprestart
Now lets configure PostgreSQL so it can be added as server in pgAdmin
vi /var/lib/pgsql/11/data/pg_hba.conf
Find
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
Change peer and ident to trust. So it will look like as below
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
Restart PostgreSQL
systemctl restart postgresql-11
Now lets change postgres password
su - postgres
psql postgres
\password postgres
After this is done we will open pgAdmin interface with the domain name you created above and add out PostgreSQL server.
Now enter anything in Name and click on Connection tab after it
Under hostname/address enter 127.0.0.1. Change username to postgres and password to the password you changed above using command \password postgres. Click on save and you will see your server has been added to it.