pgAdmin Installation for RHEL 7 and use cases for PostgreSQL server

pgAdmin is the leading Open Source management tool for Postgres, the world’s most advanced Open Source database. It provides a powerful graphical interface that simplifies the creation, maintenance and use of database objects.

If we are coming from Oracle background , it almost do the work of toad/sql developer , additionally monitoring and displaying various metrics like i/o , transaction per seconds , session details ,currently active and blocking sessions etc.

Best few features if pgAdmin

  1. It can generate graphical EXPLAIN plan for your queries. This unique feature since it shows pictorial insight into what the query planner is thinking. also it has better indentation as compared to using explain via command line .
  2. It will show you sql statements for all activity you are doing graphically, like createdb, create tables or any ddl and dml scripts .
  3. You can directly make changes to various configuration files of PostgreSQL like editing postgresql.conf and pg_hba.conf
  4. One of the best features is Backup/Restore via pgAdmin GUI,using pgAdmin selectively back up and restore databases, schemas, single tables, and globals, and the message tab shows you the command line pg_dump or pg_restore it used to do it
  5. You can provide all kinds of grants and revoke at object level using this tool.
  6. Monitor all you currently active session , Kill any session you want , check blocking sessions , timings and all details of sessions

In this section , I will install the pgAdmin for my test postgres server and will some activity which we can perform using this great tool call pgAdmin.

dowload and Install the pgAdmin

[root@MyPosgreHost ~]# yum install pgadmin4


 Loaded plugins: langpacks, ulninfo
 mysql-connectors-community                                                                                                            | 2.6 kB  00:00:00
 pgAdmin4                                                                                                                              | 2.9 kB  00:00:00
 pgdg-common                                                                                                                           | 2.9 kB  00:00:00
 pgdg10                                                                                                                                | 3.6 kB  00:00:00
 pgdg11                                                                                                                                | 3.6 kB  00:00:00
 pgdg12                                                                                                                                | 3.6 kB  00:00:00
 pgdg13                                                                                                                                | 3.6 kB  00:00:00
 pgdg95                                                                                                                                | 3.6 kB  00:00:00
 pgdg96                                                                                                                                | 3.6 kB  00:00:00
 prel-release-noarch                                                                                                                   | 2.9 kB  00:00:00
 tools-release-x86_64                                                                                                                  | 2.9 kB  00:00:00
 pgAdmin4/7Server/x86_64/primary_db                                                                                                    |  34 kB  00:00:00
 Total download size: 174 M
 Installed size: 554 M
 Is this ok [y/d/N]: y
 Downloading packages:
 (10/10): pgadmin4-desktop-5.1-1.el7.x86_64.rpm                                                                                        |  84 MB  00:00:38
 Package    : pgadmin4-redhat-repo-1-1.noarch (installed)
  From       : /etc/pki/rpm-gpg/PGADMIN_PKG_KEY
 Is this ok [y/N]: y


Installed:
pgadmin4.noarch 0:5.1-1.el7
Dependency Installed:
libatomic.x86_64 0:4.8.5-44.0.3.el7   pgadmin4-desktop.x86_64 0:5.1-1.el7      pgadmin4-python3-mod_wsgi.x86_64 0:4.7.1-2.el7
pgadmin4-server.x86_64 0:5.1-1.el7    pgadmin4-web.noarch 0:5.1-1.el7                python3.x86_64 0:3.6.8-18.0.3.el7
python3-libs.x86_64 0:3.6.8-18.0.3.el7  python3-pip.noarch 0:9.0.3-8.el7               python3-setuptools.noarch 0:39.2.0-10.el7
Complete!

Go to the following directory , which will be created during installation and check for setup-web.sh scripts

 [root@MyPosgreHost ~]# cd /usr/pgadmin4/bin/
 [root@MyPosgreHost bin]#
 [root@MyPosgreHost bin]#
 [root@MyPosgreHost bin]# ls -ltr
 total 46528

 -rw-r--r--. 1 root root   164752 Mar 23 14:10 v8_context_snapshot.bin
 -rw-r--r--. 1 root root  3825238 Mar 23 14:10 resources.pak
 -rw-rw-r--. 1 root root      912 Mar 23 14:10 package.json
 -rw-rw-r--. 1 root root    65320 Mar 23 14:11 yarn.lock
 -rwxrwxr-x. 1 root root     4613 Mar 23 14:17 setup-web.sh

Run the setup-web.sh and enter the details asked , the password and username will be used to login to pgAdmin GUI for login (master password)

[root@MyPosgreHost bin]# ./setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based platform…
Creating configuration database…
NOTE: Configuring authentication for SERVER mode.
Enter the email address and password to use for the initial pgAdmin user account:
Email address: parwez.alam@xyz.com
Password:
Retype password:
pgAdmin 4 - Application Initialisation
Creating storage and log directories…
Configuring SELinux…
The Apache web server is running and must be restarted for the pgAdmin 4  installation to complete. Continue (y/n)? y
Apache successfully restarted. You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4

Disable the firewall and if not allowed already allow port 80

 [root@MyPosgreHost bin]# firewall-cmd --add-port=80/tcp --permanent
 success
 [root@MyPosgreHost bin]#
 [root@MyPosgreHost bin]#
 [root@MyPosgreHost bin]# firewall-cmd --reload
 success
 [root@MyPosgreHost bin]# setsebool -P httpd_can_network_connect 1

Check apache is running ?

[root@MyPosgreHost bin]# service httpd status
Redirecting to /bin/systemctl status httpd.service
● httpd.service - The Apache HTTP Server
Loaded: loaded (/usr/lib/systemd/system/httpd.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2021-04-17 14:03:18 GMT; 3min 49s ago
Docs: man:httpd(8)man:apachectl(8)
Process: 17845 ExecStop=/bin/kill -WINCH ${MAINPID} (code=exited, status=0/SUCCESS)
Main PID: 17868 (httpd)
Status: "Total requests: 23; Current requests/sec: 0.2; Current traffic: 307        B/sec"CGroup: /system.slice/httpd.service
                       ├─17868 /usr/sbin/httpd -DFOREGROUND
Apr 17 14:03:18 MyPosgreHost systemd[1]: Started The Apache HTTP Server.

Now go any browser chrome,IE, Firefox and enter the below ip details Instead of localhost , put the public ip of your host where pgAdmin installed http://150.136.108.14/pgadmin4

This main page will be displayed once you enter the ip

Add your Postgres server connection details once the you login

Once your server is added all hosted dbs, server metrics and connection details will start showing on the main dashboard

You can walk around and see all your database and objects like tables,view, tablespace by browsing the panel

  1. Load you database and check if the session details are visible

2. Backup and restore database

Right click on the selected database tab and click backp and enter the details if you want full(base) or only schema table (dumps) backups

Check backup logs

Go to the location and verify if the compressed tar backup exits

3. Run explain plan for any sql statement

 103 total views,  3 views today

Leave a Reply

Your email address will not be published. Required fields are marked *