Home > Blogs > VMware vFabric Blog > Tag Archives: PostgreSQL

Tag Archives: PostgreSQL

How-To: Build a Geographic Database with PostGIS and vPostgres

Mobile Location Based Services are on the rise. After several false starts back in the mid 2000s, every mobile user now depends on their phones to tell them where they are, where their friends are, and to engage with social media like Facebook and Foursquare. A report by Juniper Research suggests this market is expected to breach over $12 billion next year, where it hardly existed a few years ago at all.

This is in part because mobile apps have become ubiquitous now. In order to remain relevant, businesses need to interact socially and have a web store to remain accessible to their wandering customers.

Building a geographically aware application from scratch sounds daunting and like a lot of initial data setup. It doesn’t have to be. Products like vFabric Postgres (vPostgres) can be used along with the PostGIS extensions to perform geographic-style queries. Then, public data and an open source visualizer can be used to transform the query into a meaningful result for your application or end user.

Continue reading →

How to Perform Security Updates on vFabric Postgres

19 Replies

The PostgreSQL community announced last week that an important security update will be released on April 4, 2013. This release will include a fix for a high-exposure security vulnerability and all users are strongly urged to apply the update as soon as it is available. Knowing how disruptive urgent security updates can be to IT and developers, the PostgreSQL community issued advanced warning in the hopes that it would ease the impact to day-to-day operations while helping as many companies as possible to adopt the update quickly.

As such, we would like to take the time to remind us all how important these security updates are to your business, and how to apply them most efficiently for vFabric Postgres.

The Cost of Missing Security Updates

Maintenance and security software updates are essential in extending application longevity as well as in keeping the confidence of customers who use services based on the application.

When big data disasters hit, the impacts quickly move beyond financial and affect reputation and trust. Databases are a particular area of concern. A recent article titled, “Making Database Security Your No. 1 2013 Resolution,” cited a Verizon study that showed only 10 percent of total security spend goes into database protection, while 92 percent of stolen data comes out of databases.

According to the seventh annual U.S. Cost of a Data Breach report from Ponemon Institute, the cost of an average data breach was $5.5M in 2011 or $194 per record. While $5.5M may not sound like a lot to some companies, losing one million records at a cost of $194 per record adds up. Continue reading →

Tips and Tricks for Internal Use of Your vFabric Postgres VM

103 Replies

Ever since VMware released our new version of the popular PostgreSQL, we have been working hard to publish a series of informative articles on what’s new in the vFabric Postgres (vPostgres) 9.2 release. There are a number of cool things like major performance and scale improvements, elastic memory for vPostgres, contributions to open source, master-slave clusters, and new GUI capabilities. In this post, we are going to dig into some tips and tricks for internal use of a vFabric Postgres virtual machine, talk about scripts, and explain the management of the network interface.

First, to see what we are talking about, it would be helpful to login to your vFabric Postgres VM. vPostgres supports SSL by default (see last weeks post on securing your vPostgres deployment for more security tips). Therefore, it is pretty easy to connect to a server and set things up inside for those who really want to personalize things at a very low level (like pg_bha.conf for connection restrictions). After the first initialization, you can connect either as user postgres or root with the same password you used at first boot.

As with every other system, it is never really recommended to connect with the user root for security reasons. By connecting with user postgres you will find the following things once connected. Continue reading →

Securing your vFabric PostgreSQL VM

23 Replies

Especially in today’s world, security is top of mind for app developers, DBAs, and CIOs alike. One of the benefits that VMware strives to include in every product is a system of reasonable defaults for security. This generally means that users should expect a reasonably secure middleware application when they deploy a VMware app by default.

vFabric Postgres (vPostgres) is no different. There are not that many security settings in vFabric Postgres. However, there are a few things you can look at as options to further harden your deployment, and of course, the virtual machine that you are deploying them on, particularly if it is exposed to an external environment.

SSL Connection Restrictions

vFabric Postgres has as default users postgres and root, and both can connect to the virtual machine with SSL. If you want to restrict access to the virtual machine for certain users or a group of users, here is some advice to follow:

1. In order to restrict SSL connection only to the members of the group vFabric (the user postgres is a member of this group by default), add this line at in /etc/ssh/sshd_config.

AllowGroups vfabric

Continue reading →

Exploring the New Database Server GUI Features in vFabric Postgres 9.2

26 Replies

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: Continue reading →

How to Use the New GUI Features in vFabric Postgres 9.2

18 Replies

People have said code is poetry and information is beautiful, but let’s talk about graphical user interfaces (GUI) and ease of use.

The PostgreSQL community has a multitude of choices when it comes to GUIs versus CLI. For vFabric Postgres, we’ve added some cool, new GUI tools.

In part one of this two-part series on vFabric Postgres user interfaces, we look at the built-in GUI capabilities within the VM of the latest release—vFabric Postgres 9.2. Based on VMware Studio, vFabric Postgres 9.2 uses an instinctive user interface to fully control the behavior of the database virtual machine and allows you to perform basic tasks that are normally associated with an OS. In part 2, we will cover the key features of the database server’s new GUI and explain how to get overview-level information and see both processes and locks. Continue reading →

Scaling for the Information Explosion: Master-Slave Cluster with vFabric Postgres 9.2 on vSphere

19 Replies

We are living in the age of the Information Explosion. Data growth is accelerating at 50% year over year. In fact, more data will be generated in the next 4 years than in the history of the world. And people are addicted to information, staying connected for up to 12 hours a day and seeing more than 34 billion bits of information every day—the equivalent of reading two books.

Earlier this year, VMware CTO Richard McDougall stated in his 2013 predictions on big data that this year will be the year that ‘delete’ becomes forbidden. As a result, IT is challenged to scale a volume of data that is a moving target, with the only promise that it will continue to grow exponentially.

While vFabric Postgres 9.2 is already known to have a significant performance gains, the ability to create master-slave clusters adds more power to the architecture mix. Master-slave clusters provide extra assurances for data backups and failover, but also they can be used to distribute the workload for queries and to help applications scale.

Below, we will explain how set up and test a master-slave cluster in Postgres 9.2.

Continue reading →

Behind the Scenes: Patching PostgreSQL for Performance–vFabric Team Contributes to Open Source

22 Replies

After PostgreSQL 9.2 was released, users that relied on PostgreSQL for scale, may have noticed a performance hit. In fact, the PostgreSQL community alongside the VMware vFabric Postgres team, was able to prove that the new version demonstrated a 10% performance hit over version 9.1. As part of the VMware Postgres team, we wanted to fix this problem for our own distribution, but as mentioned in previous posts, we also wanted to contribute our fixes back to the common core. This post provides additional detail on how this problem was identified and how we worked with the open source PostgreSQL community to restore performance.

Background on the Performance Issue in PostgreSQL 9.2

Last year, during routine regression testing of vFabric Postgres, we found that PostgreSQL 9.2, the latest major release of PostgreSQL, demonstrated a significant performance regression from version 9.1. Using DBT-2, an open-source and fair-use implementation of TPC-C benchmark [1], we noticed a 10% performance degradation, which we then reported to the community [2].

To troubleshoot the problem we used git bisect to find the type of commit that caused the performance problem and cross-examined the statistical profiles using oprofile. As it turns out, the regression was caused by a commit that changed the way memory was allocated when SPI queries were executed. The commit was intended to reduce the number of allocations for queries using a cached plan at the cost of more logistics work. However, according to the DBT-2 test, we could see that this tradeoff was unfavorable for dynamic queries. So to fix it, we would need reintroduce the original tradeoff on its intended queries using conditions [3].

We proposed the fix to the wider PostgreSQL community and the ensuing discussion led to a refined resolution which was implemented in a patch [4]. This patch has been back-ported to the latest PostgreSQL 9.2.3 release and is included in the latest vFabric Postgres release [5]. Continue reading →

Dynamic Memory Management of vFabric Postgres

53 Replies

In a nutshell, dynamic memory management in vFabric Postgres is conceptually like Elastic Memory for Java (EM4J), but for a virtualized, enterprise-class, open source database instead of an application server.

Compared to a normal PostgreSQL server, vFabric Postgres brings two additions necessary for flexible virtualization of the database server. These two features can help companies realize the benefits of virtualizing the database and the associated cost savings from running an open source database on an extremely cost-effective infrastructure.

Elastic shared memory management
Automatic memory configuration

Elastic Shared Memory Management

Directly embedded with PostgreSQL core, the elastic shared memory management is a new feature of vFabric Postgres. This capability allows memory to be released or obtained according to the other virtual machine needs on the same server. Continue reading →

VMware vFabric Cloud Application Platform — Build, Scale and Run Data-Intensive Applications On-Premise and in the Cloud

The Cost of Missing Security Updates

SSL Connection Restrictions

Connecting to the Database Management Interface

Background on the Performance Issue in PostgreSQL 9.2

Elastic Shared Memory Management