HomeCentOSCentOS 7Install PostgreSQL and pgAdmin using uWSGI on Centminmod CentOS 7

Install PostgreSQL and pgAdmin using uWSGI on Centminmod CentOS 7

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



Upgrade php using centmin.sh option 5


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


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'

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


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


Description=uWSGI Emperor 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


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


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


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


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


Now lets configure PostgreSQL so it can be added as server in pgAdmin

vi /var/lib/pgsql/11/data/pg_hba.conf


local   all             all                                     peer
# IPv4 local connections:
host    all             all               ident

Change peer and ident to trust. So it will look like as below

local   all             all                                     trust
# IPv4 local connections:
host    all             all               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 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.

Must Read