Home CentOS CentOS 7 Install PostgreSQL and pgAdmin 4 Under Domain Name on Centminmod CentOS 7

Install PostgreSQL and pgAdmin 4 Under Domain Name 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 systemd service

vi /etc/systemd/system/pgadmin4.service


Description=Pgadmin4 service

ExecStart=/usr/bin/python3 /usr/local/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py


Start and enable Pgadmin4 services

systemctl start pgadmin4
systemctl enable pgadmin4

You can now access pgadmin4 interface at yourserverip:5050. Note you need to open port 5050 in your firewall if installed

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


location / {

Under it Add

proxy_pass http://localhost:5050;

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

Enable Logging in Haproxy

HAProxy can emit log message for processing by a syslog server. This is compatible with familiar syslog tools like Rsyslog, as well...

Getting real IP in Nginx when behind cloudflare Haproxy

Create a file named cloudflare_ips.ls and in that add all the cloudflare IP's vi /etc/haproxy/cloudflare_ips.ls 2400:cb00::/32 2606:4700::/32 2803:f800::/32 2405:b500::/32 2405:8100::/32 2a06:98c0::/29 2c0f:f248::/32

Getting Real IP in HaproxyNginx configuration

Well its a little configuration can get real to nginx when haproxy is set as reverse proxy. Follow this...

Haproxy as Single Point to Failure Node With Glusterfs and MariaDB Maxscale Cluster

HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications. It...

GlusterFS Replication on CentOS 7

GlusterFS is a scalable network filesystem suitable for data-intensive tasks such as cloud storage and media streaming. GlusterFS is free and open...