This post shows how to access Pega 7 PostgreSQL DB with pgAdmin –assuming Pega 7 is installed to use PostgreSQL– and to view all Pega 7 DB tables. This example applies to the following Pega 7 installation method:
- Pega 7 is installed as a Personal Virtual Server (PVS) utilizing the VMWare Player or Oracle VM VirtualBox as described in the Downloading the Exercise System section of the System Architect Essentials I (7.1) and Senior System Architect (7.1) courses provided by Pega Academy.
- Here, Windows 7 is used as the host operating system. The steps should be similar for other OS.
Related Posts
- How to Read Pega 7 BLOB (Storage Stream)
- Install Pega 7 on Mac OS with Oracle VirtualBox
- Install Pega 7 on Tomcat and PostgreSQL
- Setup FTP Connection to Pega 7 Exercise System
- Configure Pega 7 Activity to Delete Case Type Instances
Summary
- Installation of the PostgreSQL pgAdmin Console
- Configuration of PostgreSQL DB Server to Accept Remote Connections
- Configuration of Connection Settings in postgresql.conf File
- Setup of PostgreSQL DB User for pgAdmin Connections
- Setup Connection in pgAdmin to Access Pega 7 PostgreSQL DB
1 Installation of the PostgreSQL pgAdmin Console
Download the pgAdmin tool for Windows from https://www.pgadmin.org/download/windows.php or for the Mac from https://www.pgadmin.org/download/macosx.php. In this example, pgAdmin version 1.14.3 for Windows was used. Follow the pgAdmin installation instructions to install the application.
2 Configuration of PostgreSQL DB Server to Accept Remote Connections
- The exercise systems on Pega Academy use the PostgreSQL DB server as part of the PRPC personal virtual server installation.
- Open the VMWare Player and click on the virtual machine and then click on the link Edit virtual machine settings.
- Clicking on Network Adapter opens a view showing the current network connection settings.
- In this example, the Network Adapter under Virtual Machine Settings of the VMWare Player is configured to use NAT (network address translation), so that the host’s IP address is shared (in this case the Windows OS on which the VMWare Player is running).
- This will result in the virtual machine using an IP address in the
192.168
private network that is shared with the host operating system.
- Next, use the VMWare Player to start the virtual machine by clicking on the link Play virtual machine and access it through the command line using the default user
architect
and the default passwordpassword
. - For newer versions of the Pega Exercise System, use the default user
root
and the passwordinstall
.
- The PostgreSQL server instance needs to be configured so that it allows connections from the
192.168
private network since pgAdmin has been installed on the host operating system. - Navigate to the folder
/etc/postgresql/9.1/main
on the virtual machine and locatepg_hba.conf
. - On newer Pega 7 versions, the file is located at
/var/lib/pgsql/9.4/data
. - If you can’t locate the configuration file use the below command to search for it:
find / -name pg_hba.conf
- Open the file in the vi editor using the command:
sudo vi pg_hba.conf
- In the section
# IPv4 local connections
, add:host all all 192.168.0.0/16 md5
to allow incoming connections from pgAdmin running on the Windows host operating system.
3 Configuration of Connection Settings in postgresql.conf File
- Navigate to the folder
/etc/postgresql/9.1/main
and locate the filepostgresql.conf
and open it in vi using the command:
sudo vi postgresql.conf
- Locate the section
# CONNECTIONS AND AUTHENTICATION
and uncomment the parameterlisten_addresses
and set its value to'*'
.
- In order for the changes to take effect, the virtual machine can either be restarted or the command:
/etc/init.d/postgresql restart
- …can be used to only restart PostgreSQL so that it applies the updated configuration files.
- The PostgreSQL DB server running on the virtual machine will now accept remote connections from any IP address.
4 Setup of PostgreSQL DB User for pgAdmin Connections
- It is recommended to create a dedicated PostgreSQL database user that will be used in the pgAdmin console. Execute the following command to create a new super user with access to all databases:
sudo -u postgres createuser --superuser <username>
- Then open the PostgreSQL command line with by executing the command:
sudo -u postgres psql
- Set the password of the new super user with the psql command:
\password <username>
- The PostgreSQL server port should be 5432. It can be checked by executing the command:
sudo -u postgres psql -c "SHOW port;"
- Obtain the IP address of the virtual server by executing the command:
ifconfig -a
- In this case, the IP address of the virtual machine is
192.168.1.128
. - Open the pgAdmin application on the Windows host system.
5 Setup Connection in pgAdmin to Access Pega 7 PostgreSQL DB
- In pgAdmin, click on File in the top level menu and select Add Server.
- In the New Server Registration dialog, enter the name for the connection, the host name, the port and the username and the password of the super user account created earlier.
- Then click on Ok to open the connection.
- Double clicking on the new server connection opens a tree menu showing the Pega PRPC database named
pega
. - pgAdmin can be used to understand how PRPC operates and how it maintains data and rules by looking at the tables in the associated schemas.
Caution: Modifying or deleting data or PRPC system tables can compromise a Pega 7 instance and render it useless.
Thanks a lot for the detailed explanation.