The vFabric Postgres 9.2 release seriously upped the user interface (UI) experience. In our post last week, we talked about the built in, VM-based GUI to help manage the system, network, and updates. This week, we’d like to take you through the changes your DBAs and developers will see when using the updated database server—listing the databases on the server, seeing database global data, and drilling into processes and locks. All of this comes out of the box with the vFabric Postgres appliance.
Connecting to the Database Management Interface
Once your vFabric Postgres server is up and running, you can connect to its database management interface using this URL in a web browser. The connection is made with https, port 8443 at the IP address or domain for the server:
This URL is the same one you can find on the login screen of the vFabric Postgres server.
Once connected, you will arrive on the screen above, requesting information necessary in order to connect too the database instance.
- Host IP—this should remain as default value, the default being localhost, as the user interface and the database server are running inside the same virtual machine.
- Port number—This should also remain as default, 5432 being the default port of the PostgreSQL.
- Database name—The name of the database you need to connect to.
- Database user name—The name of the user connecting to the database. After initialization of the virtual machine, default is “postgres.”
- Password—When connecting for the first time, this password is the value you set for the initialization of the virtual machine.
- SSL connection setting—As SSL is already embedded in the virtual machine, you can require it for a secured encrypted connection.
See the List of Available Databases
Once logged into the interface, you will find the list of available databases in the server. Like other RDBMS, some users might not have all permissions on all databases. After initialization (like in the example of this post), the only user available is called “postgres” and is a superuser. So, it has an access to all the databases and can perform all types of operations.
For each database in the server, the connected user can see the name, owner, space consumed by the database, and if there are any connection restrictions.
By right clicking on the database name, you can find more information, divided in several sections. Those sections can be customized at will.
vPostgres Database Overview
The first section is called Overview.
Simply, this screen gives user the possibility to have a look at some global database data, like:
- Version of the database server—here vFabric Postgres 184.108.40.206 beta
- The space consumed by database
- The number of tables
- The number of schemas
- The number of users who are allowed to log in this database
- Uptime of the database instance
vPostgres Database Processes
As well, you can find the processes currently running on the database. As an example, I ran the following queries from a psql client.
$ psql -h 192.168.11.3 -U postgres postgres
Password for user postgres:
psql (9.2.2, server 9.2.2)
Type "help" for help.
postgres=# create table aa (a int);
What you can see is that the database server is able to scan its activity in live and report the activity to the connected user.
vPostgres Database Locks
The last section allows having a look at the locks currently taken inside the connected database.
This is particularly useful when analyzing deadlock situations. These situations can be solved thanks to the last feature I will talk about in this post—processes.
Now, you can look at the details of a lock and the associated process currently running in your database, and you can cancel the processes manually from the user interface. This is particularly useful when analyzing stuck queries. In order to cancel a process, you can right-click on the process, then click “Cancel,” and you are done.
With the psql client I was using previously to create a table, we can see the resulting behavior when cancelling a process.
The best part is that you do not need to set up anything! All functionality is included in the vFabric Postgres appliance. If you’re interested in trying it out, download the latest version of vPostgres here.
|About the Author: Michael Paquier is a member of PostgreSQL technical staff at VMware. He is involved for many years with community development of PostgreSQL and Postgres-XC, and has worked on multi-master database technologies. He has also interest in parallel query processing and concurrent SQL processing.|