Create a pgAdmin Server Connection
- Samuel Valdez
Some EAS activities require that a staff-member access an EAS database with pgAdmin to perform certain tasks, for example, to administer the EAS database, or to perform database queries. This page describes how to create a pgAdmin server connection to achieve those ends.
Prerequisites
An SSH tunnel session must already have been created and configured with PuTTY before a pgAdmin server connection can be created. If this is not the case, then follow the instructions that are on the PuTTY and SSH System Administration page.
Install pgAdmin
The pgAdmin installer can be downloaded from the pgAdmin home page. To install pgAdmin, download, and then run, the installer.
All of the screenshots that follow are from pgAdmin 4 3.1.
Create a Server Group
Recommendation
This step is optional but recommended.
1 Run pgAdmin.
2 Under pgAdmin's Browser, use the Server Group Dialog to create a server group, or rename the pgAdmin's default server group. Name the server group "EAS", for obvious reasons.
Create a Server Connection
Use pgAdmin's Server Dialog to create a server connection. The server connection in this example will be for the SF DEV DB VM (San Francisco data center, DEVelopment environment, eas DataBase server, Virtual Machine). In the screenshots that follow, sensitive information has been redacted, and the screenshots themselves illustrate the key values that should be entered in the Server Dialog fields.
EAS Database Credentials
The appropriate EAS database credentials will be provided by an EAS administrator for any server connections that need to be created.
The following server connection names should be used to maintain consistency with standard EAS nomenclature. The server connection name options for this example (SF DEV DB*) are highlighted in bold font in the table below.
| Environment | |||
|---|---|---|---|
| DEV | QA | PROD | |
| San Francisco Data Center | SF DEV DB (<ip-address>) <database-user> | SF QA DB (<ip-address>) <database-user> | SF PROD DB (<ip-address>) <database-user> |
| Disaster Recovery Data Center | DR DEV DB (<ip-address>) <database-user> | ( DR QA DB (<ip-address>) <database-user>) | DR PROD DB (<ip-address>) <database-user> |
1 Under pgAdmin's Browser, and from the EAS server group, invoke the Server Dialog.
2 In the Server Dialog's General tab, enter the name of the server connection, "SF DEV DB", or "SF DEV DB (<ip-address>)", where "<ip-address>" would be replaced with the IP address of this particular VM, in the Name field. Uncheck the Connect now? checkbox.
3 In the Server Dialog's Connection tab, enter "localhost" in the Host name/address field, and enter the listener port number ("5433" in this example) of the database VM in the Port field.
For the listener port numbers of the other EAS databases, refer to the table in the page Create an SSH Tunnel Session.
SSH Tunnel Session and Port Forwarding
The connection information in the dialog above is effectively saying "as the database user postgres, connect to the database postgres, through listener port number 5433 on the local machine", for example an EAS developer's Windows machine. This will work because of the way that the corresponding SSH tunnel session was configured with port forwarding. Refer to the page Create an SSH Tunnel Session for more information on this topic.
4 Save the server connection by pressing the Save button which is at the bottom of the server dialog.
Test the Server Connection
Before the server connection can be tested, an SSH tunnel session must be opened.
1 Run PuTTY.
2 Select the eas-sf-dev-db-tunnel tunnel session from the Saved Sessions list, press the Load button, and then press the Open button.
3 You will be prompted to log in to the target VM. Use the username that was given to you by the EAS administrator, and when prompted a second time, enter the passphrase that was used to create your private key in an earlier step.
4 Now that the SSH tunnel session for this database has been opened, in pgAdmin, invoke the context menu (right-click) on the server connection, and select the Connect Server menu command.
5 Enter the password in the Password field, optionally check the Save Password checkbox, and then press the OK button.
6 Congratulations, you should now have an open EAS database!
