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
- 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 .
- It will show you sql statements for all activity you are doing graphically, like createdb, create tables or any ddl and dml scripts .
- You can directly make changes to various configuration files of PostgreSQL like editing postgresql.conf and pg_hba.conf
- 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
- You can provide all kinds of grants and revoke at object level using this tool.
- 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
- 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
504 total views, 1 views today