Install PostgreSQL and pgAdmin using uWSGI on Centminmod CentOS 7

By
Brijendra Sial
-
0
2984

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.